怎样在PL/SQL存储过程中使用REF CURSOR返回动态结果集-SQL-PHP中文网

错误说法：ref cursor 必须显式声明类型，不能直接用 sys_refcursor 作参数类型。正确答案是：sys_refcursor 可直接作为存储过程的 out 参数类型，oracle 官方支持且广泛使用；pls-00382 错误通常源于赋值或调用不匹配，而非参数声明本身。

怎样在pl/sql存储过程中使用ref cursor返回动态结果集

REF CURSOR 必须显式声明类型，不能直接用 `SYS_REFCURSOR` 作参数类型

Oracle 中 SYS_REFCURSOR 是预定义的弱类型游标，只能用于变量声明或函数返回值，**不能作为存储过程的 OUT 参数类型**。否则会报错 PLS-00382: expression is of wrong type。

正确做法是先在包中定义自定义 REF CURSOR 类型：

CREATE OR REPLACE PACKAGE my_pkg AS
  TYPE emp_cursor IS REF CURSOR RETURN emp%ROWTYPE;  -- 强类型
  TYPE generic_cursor IS REF CURSOR;                 -- 弱类型（等价于 SYS_REFCURSOR）
  PROCEDURE get_emp_data(p_deptno IN NUMBER, p_cur OUT emp_cursor);
END my_pkg;
/

强类型（如 emp_cursor）要求 OPEN ... FOR 后的查询字段、顺序、类型必须严格匹配 RETURN 子句，编译期检查更严，适合固定结构结果集
弱类型（如 generic_cursor 或 SYS_REFCURSOR）不限制查询结构，但调用方需自行处理列名和类型，适合动态 SQL 场景
如果过程只在包内使用，类型可定义在包体中；若需外部调用（如 JDBC），必须放在包规范（PACKAGE）里

动态 SQL 要用 `OPEN ... FOR EXECUTE IMMEDIATE`，不能直接拼接字符串

想根据输入参数决定查哪张表或加什么条件，不能写 EXECUTE 'OPEN p_cur FOR SELECT ... WHERE deptno = ' || p_deptno —— 这语法非法，OPEN 不接受字符串表达式。

必须用 OPEN ... FOR EXECUTE IMMEDIATE 配合绑定变量：

悟道

悟道是一款AI大模型工具，智源“悟道”大模型，中国首个+世界最大人工智能大模型。

下载

CREATE OR REPLACE PACKAGE BODY my_pkg AS
  PROCEDURE get_emp_data(p_deptno IN NUMBER, p_cur OUT emp_cursor) IS
    v_sql VARCHAR2(500);
  BEGIN
    v_sql := 'SELECT * FROM emp WHERE deptno = :1';
    OPEN p_cur FOR v_sql USING p_deptno;  -- 绑定变量防注入，且支持日期/数字等类型自动转换
  END;
END my_pkg;
/

USING 子句传递参数，比字符串拼接安全，也避免类型隐式转换错误（比如把数字当字符串拼进 SQL）
如果查询字段不固定（例如不同表字段数不同），必须用弱类型游标 + DBMS_SQL 解析列元数据，REF CURSOR 本身不提供列信息
动态 SQL 中不能用 PL/SQL 变量名直接替换表名或列名，需用 EXECUTE IMMEDIATE 拼接，但要严格校验输入（如白名单检查表名）

JDBC 调用时 registerOutParameter 必须设为 `Types.OTHER`

Java 端用 CallableStatement 调用带 OUT SYS_REFCURSOR 的过程，漏掉类型注册会抛 SQLException: Invalid column type。

标准写法：

String sql = "{call my_pkg.get_emp_data(?, ?)}";
CallableStatement cs = conn.prepareCall(sql);
cs.setInt(1, 10);
cs.registerOutParameter(2, Types.OTHER);  // 关键：必须是 Types.OTHER
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);  // 不能用 getResultSet()
while (rs.next()) {
  System.out.println(rs.getString("ename"));
}

getResultSet() 在 Oracle JDBC 中仅适用于函数返回值，对存储过程的 OUT 参数无效
如果过程定义的是自定义类型（如 my_pkg.emp_cursor），JDBC 无法识别，必须用 SYS_REFCURSOR 或包规范中声明的弱类型
事务控制由应用层负责：游标在语句执行后即打开，但 ResultSet 读取必须在连接未关闭前完成，否则报 ORA-01001: invalid cursor