有时候有需求要通过存储过程返回多个结果集,样例如下:
CREATE OR REPLACE PROCEDURE p_t_return_cursor(
cursor_o_1 OUT SYS_REFCURSOR,
cursor_o_2 OUT SYS_REFCURSOR)
IS
BEGIN
OPEN cursor_o_1 FOR
SELECT object_name from user_objects WHERE object_type = 'INDEX' and rownum=1;
OPEN cursor_o_2 FOR
SELECT object_name from user_objects WHERE object_type = 'TABLE' and rownum=1;
END p_t_return_cursor;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class TestProdureCursor {
static final String driver_class = "oracle.jdbc.driver.OracleDriver";
static final String connectionURL = "jdbc:oracle:thin:@10.10.100.16:1521:ora11";
static final String userID = "test";
static final String userPassword = "test";
public void runTest() {
Connection con = null;
CallableStatement stmt = null ;
ResultSet rs = null;
try {
Class.forName (driver_class).newInstance();
con = DriverManager.getConnection(connectionURL, userID, userPassword);
stmt = con.prepareCall("{CALL p_t_return_cursor(?,?)}");
stmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
stmt.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
stmt.execute();
System.out.println("=========游标1==============");
rs = (ResultSet) stmt.getObject(1);
while(rs.next()) {
System.out.println(rs.getString(1));
}
System.out.println("=========游标2==============");
rs = (ResultSet) stmt.getObject(2);
while(rs.next()) {
System.out.println(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
if(stmt != null){
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(rs != null){
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) throws Exception{
TestProdureCursor testTestProdureCursor = new TestProdureCursor();
testTestProdureCursor.runTest();
}
}
=========游标1==============ADD_BUSINESS_ID
=========游标2==============
A