Java调用Oracle动态游标
来源:互联网 发布:听评书软件 编辑:程序博客网 时间:2024/05/16 06:00
- Create or REPLACE PROCEDURE TEST_PROCEDURRE(
- P_USER_ID_I IN VARCHAR2 --接收输入参数
- )
- RETURN SYS_REFCURSOR
- AS
- P_RESULT_SET_O SYS_REFCURSOR; --返回游标
- X_SQL VARCHAR2(200);
- BEGIN
- X_SQL :='select user_id,user_name,email from user_mst where user_id like ''%' || P_USER_ID_I ||'%'''; --生成SQL语句
- OPEN P_RESULT_SET_O FOR X_SQL;
- RETURN P_RESULT_SET_O; --返回游标
- END TEST_PROCEDURRE;
- CallableStatement cstmt = null;
- ResultSet rs = null;
- try {
- String callSql = "{? = call TEST_PROCEDURRE(?)}";
- cstmt = conn.prepareCall(callSql);
-
- cstmt.setString(2, "userName");
- cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
- cstmt.execute();
- rs = (ResultSet) cstmt.getObject(1);
- if (rs != null) {
- System.out.print("usercd");
- System.out.print("userName");
- System.out.println("EMAIL");
- while (rs.next()) {
- System.out.print(rs.getString(1)+" ");
- System.out.print(rs.getString(2)+" ");
- System.out.println(rs.getString(3));
- }
- }