java 调用存储过程返回多个结果集

本文链接：https://blog.csdn.net/stevendbaguo/article/details/70256469

有时候有需求要通过存储过程返回多个结果集，样例如下：

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