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);
}
}
}