数据采用oracle数据库scott/tiger示范账户下的emp（员工）表和dept（部门）表：
create table DEPT
(
DEPTNO NUMBER(2) not null primary key,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
create table EMP
(
EMPNO NUMBER(4) not null primary key,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
constraint FK_DEPTNO foreign key (DEPTNO)references DEPT (DEPTNO)
)
Oracle存储过程代码:
Create or replace PROCEDURE searchEmpByDept(
indeptno IN NUMBER,
empcur OUT sys_refcursor,
errorMsg OUT varchar)
IS
BEGIN
errorMsg:='';
OPEN empcur FOR
SELECT *
FROM emp
WHERE deptno = indeptno
ORDER BY empno;
EXCEPTION
WHEN OTHERS THEN
errorMsg:= sqlerrm;-- sqlcode是异常编号，sqlerrm是异常的详细信息
END searchEmpByDept;
使用 pl/sql 过程语句测试上面的存储过程：
declare
errorMsg varchar(1000);
empcur sys_refcursor;
emp scott.emp%rowtype;
begin
searchEmpByDept(10,empcur, errorMsg);
if errorMsg is not null then
dbms_output.put_line(errorMsg);
end if;
loop
fetch empcur into emp;
EXIT WHEN empcur%notfound ;
dbms_output.put_line(emp.ename);
end loop;
close empcur;
end;
Java调用代码：
public class DBHelper {
private Connection conn = null;
public Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.1.6:1521:ntcsoft",
"scott",
"tiger");
return conn;
}
}
public class CallOracleProcedure {
public static void main(String args[]) {
ResultSet rs = null;
CallableStatement st = null;
Connection con = null;
try {
con = new DBHelper().getConnection();
String sql = "call searchEmpByDept(?,?,?)";
st = con.prepareCall(sql);
st.setInt(1, 20);//设置入参部门编号20
//注册返回类型参数。CURSOR类型在java.sql.Tyes中没有定义，在驱动程序包中找到了
st.registerOutParameter(2, oracle.jdbc.driver.OracleTypes.CURSOR);
st.registerOutParameter(3, Types.VARCHAR);
boolean result = st.execute();
//获取返回参数
rs = (ResultSet) st.getObject(2);
String msg = st.getString(3);
if(msg != null)
System.out.println(msg);//异常信息部为null则打印
System.out.println("empno" + ""t" + "ename" + ""t" + "sal" + ""t"+ "deptno");
//输出查询结果
StringBuilder output = new StringBuilder();
while (rs.next()) {
output.append(rs.getInt("empno"))
.append(""t")
.append(rs.getString("ename"))
.append(""t")
.append(rs.getDouble("sal"))
.append(""t")
.append(rs.getInt("deptno"));
System.out.println(output.toString());
output.delete(0, output.length());
}
output = null;
}catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(rs!=null) rs.close();
if(st!=null) st.close();
if(con!=null) con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
注意，oracle数据库的scott示范账户默认是被锁定的，在使用之前需要解锁：
alter user scott account unlock;
然后以scott/tiger 登陆数据库服务器，会提示密码已过期，并要求你立即输入新密码。