java调研oracle存储过程返回记录集

PACKAGE

sql 代码

CREATE OR REPLACE PACKAGE PKG_UIREPORT_DEPTTRANSTABLE  AS

 TYPE MY_CURSOR IS REF CURSOR;

end PKG_UIREPORT_DEPTTRANSTABLE;   

PROCEDURE
sql 代码

 

CREATE OR REPLACE PROCEDURE GETDEPTTRANSTABLE(
       o_CURSOR out PKG_UIREPORT_DEPTTRANSTABLE.MY_CURSOR,
       i_POST_DATE IN VARCHAR2,  --传入参数1
       i_SEC_TYPE IN VARCHAR2      --传入参数2
       ) IS

BEGIN
    OPEN o_CURSOR FOR select * from sec_vou T1 WHERE T1.CLEAR_DATE = i_POST_DATE AND T1.SEC_CODE IN (select T2.SEC_CODE from ea_pub.sys_sec_info T2 WHERE T2.SEC_TYPE = i_SEC_TYPE);

END GETDEPTTRANSTABLE;

JAVA代码调用存储过程并得到记录集

java 代码

.....
//要引入的包
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.OracleTypes;
......
.......
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        ComOutput comOutput = null;
        String postDate = "20070118";
        String secType = "01";

        try {
            conn = EJBUtil.getConnection();
            /**
             * 调用存储过程
             */
            cstmt = conn.prepareCall("{ call GETDEPTTRANSTABLE(?,?,?) }");
            cstmt.registerOutParameter(1,OracleTypes.CURSOR);
            cstmt.setString(2,postDate);
            cstmt.setString(3,secType);
            cstmt.execute();
            rs = (ResultSet)cstmt.getObject(1);
            while(rs.next) {
              ...........
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            CommUtil.closeResultSet(rs);
            cstmt.close();
            CommUtil.closeConnection(conn);
        }