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

比眉伴天荒 2022-06-17 07:23 644阅读 0赞

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

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