Oracle存储过程怎么使用REF CURSOR返回查询结果集

在Oracle数据库开发中，存储过程能够实现复杂的业务逻辑封装，而REF CURSOR作为Oracle中用于处理动态结果集的特殊数据类型，常被用于让存储过程返回多行查询数据，满足应用层获取批量数据的需求。

REF CURSOR基本概念

REF CURSOR是Oracle中的引用游标类型，本质上是一个指向查询结果集的指针，它分为两种类型：

• 强类型REF CURSOR：在定义时指定了返回结果的字段结构，类型检查更严格，使用更安全。
• 弱类型REF CURSOR：定义时不指定返回结构，可以返回任意结构的查询结果集，灵活性更高。

存储过程中定义REF CURSOR的步骤

1. 定义REF CURSOR类型

首先需要自定义REF CURSOR类型，强类型和弱类型的定义方式如下：

-- 强类型REF CURSOR，指定返回字段为id和name，类型为NUMBER和VARCHAR2
CREATE OR REPLACE TYPE emp_ref_cur_type IS REF CURSOR RETURN emp%ROWTYPE;

-- 弱类型REF CURSOR，不指定返回结构
CREATE OR REPLACE TYPE generic_ref_cur_type IS REF CURSOR;

2. 编写使用REF CURSOR的存储过程

以下示例创建一个存储过程，通过弱类型REF CURSOR返回员工表中指定部门的员工信息：

CREATE OR REPLACE PROCEDURE get_dept_emps(
    p_dept_id IN NUMBER,               -- 输入参数：部门ID
    p_emp_cur OUT SYS_REFCURSOR        -- 输出参数：弱类型REF CURSOR
) AS
BEGIN
    -- 打开游标，关联查询语句
    OPEN p_emp_cur FOR
        SELECT empno, ename, job, sal 
        FROM emp 
        WHERE deptno = p_dept_id;
END get_dept_emps;
/

如果是使用自定义强类型REF CURSOR的存储过程，示例如下：

CREATE OR REPLACE PROCEDURE get_dept_emps_strong(
    p_dept_id IN NUMBER,
    p_emp_cur OUT emp_ref_cur_type     -- 使用自定义强类型REF CURSOR作为输出参数
) AS
BEGIN
    OPEN p_emp_cur FOR
        SELECT * FROM emp WHERE deptno = p_dept_id;
END get_dept_emps_strong;
/

存储过程的调用方式

在PL/SQL块中调用

使用PL/SQL匿名块调用弱类型REF CURSOR的存储过程，获取并输出结果：

DECLARE
    v_cur SYS_REFCURSOR;               -- 定义弱类型REF CURSOR变量
    v_empno NUMBER;
    v_ename VARCHAR2(20);
    v_job VARCHAR2(20);
    v_sal NUMBER;
BEGIN
    -- 调用存储过程，传入部门ID10，获取游标
    get_dept_emps(10, v_cur);
    -- 循环获取游标中的数据
    LOOP
        FETCH v_cur INTO v_empno, v_ename, v_job, v_sal;
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('员工编号：' || v_empno || '，姓名：' || v_ename || '，职位：' || v_job || '，薪资：' || v_sal);
    END LOOP;
    CLOSE v_cur;                       -- 关闭游标
END;
/

在Java程序中调用

Java通过JDBC调用返回REF CURSOR的Oracle存储过程，需要先注册输出参数的类型为OracleTypes.CURSOR：

import java.sql.*;
import oracle.jdbc.OracleTypes;

public class CallOracleProc {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "scott";
        String password = "tiger";
        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            // 调用存储过程，格式为{ call 存储过程名(?, ?) }
            cs = conn.prepareCall("{ call get_dept_emps(?, ?) }");
            // 设置输入参数，部门ID为10
            cs.setInt(1, 10);
            // 注册输出参数类型为OracleTypes.CURSOR
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            // 执行存储过程
            cs.execute();
            // 获取输出参数中的结果集
            rs = (ResultSet) cs.getObject(2);
            // 遍历结果集
            while (rs.next()) {
                System.out.println("员工编号：" + rs.getInt("empno") + 
                                   "，姓名：" + rs.getString("ename") + 
                                   "，职位：" + rs.getString("job") + 
                                   "，薪资：" + rs.getDouble("sal"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (cs != null) cs.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

注意事项

• REF CURSOR的输出参数在存储过程结束前需要保持打开状态，不需要在存储过程内部关闭，由调用方负责关闭。
• 弱类型REF CURSOR的查询语句可以动态拼接，适合查询条件不固定的场景，强类型REF CURSOR更适合返回结构固定的结果集。
• 在PL/SQL中使用自定义REF CURSOR类型时，对应的类型需要先创建，否则存储过程会编译报错。

Oracle存储过程REF_CURSORPL/SQL修改时间：2026-06-06 23:07:10