pl/sql 编写 Oracle存储过程 调用存储过程返回集合

先写Oracle的存储过程

--返回一个集合（多行数据）--1、先建一个包,在该包中定义了一个类型 test_cursor ，是一个游标create or replace package testPackage astype test_cursor is ref cursor;end testPackage;/--2、建立存储过程create or replace procedure sp_pro13(spNo in number,p_cursor out testPackage.test_cursor) isv_sql varchar2(500);begin  v_sql:='select * from emp where deptno='||spNo;  open p_cursor for v_sql;end;/

java中调用创建的存储过程

@Testpublic void testWithReturn_manyRow() {try {Class.forName("oracle.jdbc.driver.OracleDriver");Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.202.129:1521:orcl", "scott","tiger");CallableStatement cs = conn.prepareCall("{call sp_pro13(?,?)}");cs.setInt(1, 10);cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);cs.execute();// 得到结果集ResultSet rs = (ResultSet) cs.getObject(2);while (rs.next()) {System.out.println(rs.getInt("deptno") + " --- "+ rs.getInt("empno") + " --- " + rs.getString("ename")+ " --- " + rs.getString("job") + " --- "+ rs.getInt("sal"));}cs.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}

结果：