oracle 游标返回多个结果集,java 调用oracle存储过程返回多个游标结果集

1.新建一个packages,声明返回多个游标的结果集

CREATE OR REPLACE PACKAGE testxx

IS

TYPE   retcursor    IS   REF   CURSOR;

TYPE   retcursor2    IS   REF   CURSOR;

PROCEDURE pro_read

(

outcurse   OUT retcursor,

outcurse2   OUT retcursor2

);

END;

2。新建packages body

CREATE OR REPLACE PACKAGE BODY testxx IS

PROCEDURE pro_read

(

outcurse  OUT retcursor,

outcurse2  OUT retcursor2

)

IS

begin

OPEN outcurse FOR

select 1,2 from dual;

OPEN outcurse2 FOR

select 2,4 from dual;

return;

end;

END;

3.java 实现代码

得到数据库连接那段就不再重复出来了

public void testPro () throws Exception {

CallableStatement cstmt=null;

Connection conn=null;

ResultSet  rs = null;

ResultSet  rs1 = null;

try {

conn=DBConnectionManager.getInstance().getConnection(dbName);

logger.info("---conn--------"+conn);

String sqlstr = "{call  TESTXX.PRO_READ(?,?)}";

cstmt = conn.prepareCall(sqlstr);

cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); //outcurse

cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); //outcurse2

cstmt.execute();

rs = (ResultSet)cstmt.getObject(1);

rs1 = ((oracle.jdbc.OracleCallableStatement)cstmt).getCursor(2);

while(rs.next()){

logger.info(rs.getString("1")+"-----------"+rs.getString("2"));

}

while(rs1.next()){

logger.info(rs1.getString("2")+"-----------"+rs1.getString("4"));

}

cstmt.close();

} catch (SQLException se) {

logger.error (se.getMessage(),se);

} finally {

try {

if (rs != null){rs.close();}

if (rs1 != null){rs1.close();}

if (cstmt != null) cstmt.close();

conn.close();

} catch(SQLException see) {

logger.info("Exception occured closing stmt objects: " + see);

}

}

}