Is there better way to retrieve multiple CURSOR fr

2019-06-06 04:45发布

问题:

I have a solution to retrieve DB2 cursors from a stored procedure but I am looking for a better approach. The current solution looks like described below:-

Stored Proc:-

CREATE OR REPLACE PROCEDURE Proc(c1 OUT SYS_REFCURSOR, c2 OUT SYS_REFCURSOR,
         c3 OUT SYS_REFCURSOR, ....)
is
BEGIN
  open c1 FOR select feild1,feild2,.... from RSPNSE_TABLE1;
  open c2 FOR select feild1,feild2,.... from RSPNSE_TABLE2;      
  open c3 FOR select feild1,feild2,.... from RSPNSE_TABLE3;
  open c4 FOR select feild1,feild2,.... from RSPNSE_TABLE4;
  open c5 FOR select feild1,feild2,.... from RSPNSE_TABLE5;
  // continue this i got least 30 tables
END;

The java code to retrieve these cursors which looks like this:-

public boolean callProc() throws Exception {
       Connection conn = openConnection();
       CallableStatement callableStatement = null;
       ResultSet rs = null;

       int rowCount = 0;
       try {

            String proc = "call Proc(?,?,?,?,.....)";
            callableStatement = conn.prepareCall(proc);

            callableStatement.registerOutParameter(1, DB2Types.CURSOR);
            callableStatement.registerOutParameter(2, DB2Types.CURSOR);
            callableStatement.registerOutParameter(3, DB2Types.CURSOR);
            .....
            .....

            boolean results = callableStatement.execute();

            rs = (ResultSet) callableStatement.getObject(1);
            // retrieve data from resultset                             
            rs = (ResultSet) callableStatement.getObject(2);
            // retrieve data from resultset
            ......
            ......      

        } catch (Exception e) {
             throw e;
        }
        return true;
}

I have following questions about this,

  1. Is there batter way to return multiple cursors from DB2 Proc ?
  2. If so then what is java code to retrieve it from callable statement ?

Note: Individual answer for both is excepted. Even pointing out to right resources where I can read and achieve is also excepted.