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);
- }