概要描述

在开发中我需要在Procedure A中打开一个动态SQL的游标，并返回此cursor；然后用Procedure B去call A存储过程，再对返回的cursor进行操作，这个时候需要通过sys_refcursor游标在过程中返回结果集。

下面的例子简单介绍了sys_refcursor的使用方法。

详细说明

创建样例表并插入数据

CREATE  TABLE emp_torc(
  empno decimal(38,0) DEFAULT NULL, 
  ename string DEFAULT NULL, 
  job string DEFAULT NULL, 
  mgr decimal(38,0) DEFAULT NULL, 
  hiredate timestamp DEFAULT NULL, 
  sal decimal(38,0) DEFAULT NULL, 
  comm decimal(38,0) DEFAULT NULL, 
  deptno decimal(38,0) DEFAULT NULL
)
CLUSTERED BY ( 
  empno) 
INTO 3 BUCKETS
STORED AS ORC_TRANSACTION;

BATCHINSERT INTO emp_torc BATCHVALUES(
VALUES (7521,'WARD','SALESMAN',7698,'1981-01-22',1250,500,30),
VALUES (7566,'JONES','MANAGER',7839,'1981-01-02',2975,NULL,20),
VALUES (7698,'BLAKE','MANAGER',7839,'1981-01-01',2850,NULL,30),
VALUES (7782,'CLARK','MANAGER',7839,'1981-01-09',2450,NULL,10),
VALUES (7788,'SCOTT','ANALYST',7566,'1987-01-13',3000,NULL,20),
VALUES (7839,'KING','PRESIDENT',NULL,'1981-01-17',5000,NULL,10),
VALUES (7902,'FORD','ANALYST',7566,'1981-01-03',3000,NULL,20),
VALUES (7369,'SMITH','CLERK',7902,'1980-01-17',800,NULL,20),
VALUES (7654,'MARTIN','SALESMAN',7698,'1981-01-28',1250,1400,30),
VALUES (7876,'ADAMS','CLERK',7788,'1987-01-13',1100,NULL,20),
VALUES (7900,'JAMES','CLERK',7698,'1981-01-03',950,NULL,30),
VALUES (7499,'ALLEN','SALESMAN',7698,'1981-01-20',1600,300,30),
VALUES (7844,'TURNER','SALESMAN',7698,'1981-01-08',1500,0,30),
VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)
);

以下面的语句为例：

CREATE OR REPLACE PROCEDURE pcx_dgzq_test_all_user(v_job in string,cur OUT sys_refcursor) is
begin
open cur for
SELECT * FROM emp_torc WHERE job=v_job;
end;

pl/sql调用方式

DECLARE
    V_SYS_REFCURSOR_ROWS SYS_REFCURSOR;
    V_ROWS           DEFAULT.emp_torc%ROWTYPE;
BEGIN
    pcx_dgzq_test_all_user('SALESMAN', V_SYS_REFCURSOR_ROWS);
    LOOP
        FETCH V_SYS_REFCURSOR_ROWS
            INTO V_ROWS;
        EXIT WHEN V_SYS_REFCURSOR_ROWS%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(V_ROWS.ENAME);
    END LOOP;
    CLOSE V_SYS_REFCURSOR_ROWS;
END;

存储过程调用方式

CREATE OR REPLACE PROCEDURE pcx_dgzq_test_get_name(v_job IN STRING)
AS 
    V_SYS_REFCURSOR_ROWS SYS_REFCURSOR;
    V_ROWS           DEFAULT.emp_torc%ROWTYPE;
BEGIN
    pcx_dgzq_test_all_user(v_job, V_SYS_REFCURSOR_ROWS);
    LOOP
        FETCH V_SYS_REFCURSOR_ROWS
            INTO V_ROWS;
        EXIT WHEN V_SYS_REFCURSOR_ROWS%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(V_ROWS.ENAME);
    END LOOP;
    CLOSE V_SYS_REFCURSOR_ROWS;
END

CALL pcx_dgzq_test_get_name('SALESMAN');

jdbc访问输出参数类型为sys_refcursor的简单示例

该方法同pl/sql调用方式，而非存储过程调用方式

import java.sql.*;
public class JDBCexample {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";

    public static void main(String[] args) throws SQLException {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        //Hive2 JDBC URL with LDAP
        String jdbcURL = "jdbc:hive2://172.22.23.1:10000/default";
        String user = "hive";
        String password = "123456";
        ResultSet rs = null;
        Connection conn = DriverManager.getConnection(jdbcURL, user, password);
        CallableStatement cbstmt = conn.prepareCall("call pcx_dgzq_test_all_user(?,?)");
        cbstmt.setString(1, "SALESMAN");
        cbstmt.registerOutParameter(2, -10);
        cbstmt.execute();
        rs = (ResultSet) cbstmt.getObject(2);
        System.out.println();
        while (rs.next()) {
            System.out.println(rs.getString(2));
        }
        rs.close();
        cbstmt.close();
        conn.close();
    }
}