Java调用Oracle存储过程返回查询结果集

Java调用Oracle存储过程返回查询结果集 数据采用oracle数据库scott/tiger示范账户下的emp(员工)表和dept(部门)表: create table DEPT ( DEPTNO NUMBER(2) not null pri mary 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 (DEPT NO) ) 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 ClassNotFoundExcep tion, 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.C URSOR); 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 登陆数据库服务器,会提示密码已过期,并要求你立即输入新密码。