必须用 SYS_REFCURSOR 而非普通游标，因其是弱类型引用游标，可跨作用域传递并由调用方fetch；普通游标静态绑定、无法传出过程边界。

直接用 SYS_REFCURSOR 作 OUT 参数，就能让存储过程返回结果集——不是把数据复制出来，而是把游标句柄交出去，调用方自己 fetch。

为什么必须用 SYS_REFCURSOR 而不是普通游标变量

普通的显式游标，例如 CURSOR c IS SELECT ...，本质上属于静态绑定。

它的使用范围被限定在声明它的那个 PL/SQL 块里，只能在那里完成 open、fetch 和 close。一旦越过过程边界，就无法再继续传递。

相比之下，SYS_REFCURSOR 属于弱类型引用游标，更像一个指针。它不会预先锁定查询结果结构。

同一个变量既可以先 OPEN FOR SELECT * FROM t1，也可以随后再指向 OPEN FOR SELECT x,y FROM t2。同时，它还能跨作用域传递，客户端工具像 JDBC、SQL Developer、SQL*Plus 也都能直接识别并展示它返回的结果集。

常见错误现象：PLS-00382: expression is of wrong type —— 误把普通游标变量当 OUT 参数传；或声明成 IN OUT 却没初始化；或在声明区就写 OPEN（语法非法）。

• SYS_REFCURSOR 必须声明为 OUT（不能是 IN 或 IN OUT）
• OPEN ... FOR 必须写在 BEGIN 块里，不能放在声明区
• 不能对 SYS_REFCURSOR 直接 FETCH —— 它本身不是结果，只是句柄，fetch 操作由调用方完成

CREATE OR REPLACE PROCEDURE 中怎么声明和打开 SYS_REFCURSOR

声明格式固定：p_result OUT SYS_REFCURSOR。

OPEN p_result FOR 后面可以跟任意合法 SELECT，支持绑定变量、子查询、甚至动态 SQL。

示例：

CREATE OR REPLACE PROCEDURE get_user_data(
p_dept_id IN NUMBER,
p_resultOUT SYS_REFCURSOR
) IS
BEGIN
OPEN p_result FOR
SELECT id, name, salary 
FROM users 
WHERE dept_id = p_dept_id AND status = 'ACTIVE';
END;

注意点：

• 查询语句中用 = 而非 := —— 这是 SQL，不是赋值
• 若需动态 SQL（如拼表名），用 EXECUTE IMMEDIATE '...' INTO ... 不适用；正确方式是 OPEN p_result FOR sqlstr USING bind_var
• Oracle 11g 支持 USING 绑定变量，避免 SQL 注入，也提升硬解析复用率

调用时怎么拿到并遍历这个游标结果

不能在另一个存储过程中直接 FETCH —— 那是对本地游标的用法。

这里要分两步：先执行过程获取句柄，再从该句柄读数据。通常这个操作发生在匿名块或客户端。

PL/SQL 匿名块调用示例：

BEGIN
get_user_data(p_dept_id => 10, p_result => :cur_out);
END;

然后在 SQL Developer 或 PL/SQL Developer 的 Variables 面板里，把 cur_out 类型设为 Cursor，点 Value 栏右侧图标展开结果。

JDBC 中对应的是：

• CallableStatement.registerOutParameter(, Types.OTHER)
• cs.execute()
• ResultSet rs = cs.getResultSet()

容易踩的坑：

• SQL*Plus 默认不显示游标结果，需提前执行 SET SERVEROUTPUT ON 并配合 DBMS_OUTPUT.PUT_LINE 手动输出（但一般不用——直接查游标更直观）
• MyBatis 中若用 resultType="map" 接收，需确保 mapper XML 中 statementType="CALLABLE" 且参数 mode="OUT"
• 游标未被消费完就断开连接？Oracle 会自动 close，但长期持有大结果集仍可能撑爆 PGA 内存

关键理解：返回的不是表，而是句柄

真正棘手的，往往并不在于存储过程怎么写，而在于调用方有没有把这件事理解透。

它返回的不是“现成的一张表”，而是“一个可以遍历的句柄”。

这意味着，没有 schema 元信息，不会帮你自动做类型转换，结果顺序也没有兜底保证。最终都得靠调用方严格按约定来接。

尤其一旦涉及跨语言调用，Types.OTHER 和 getResultSet() 这组搭配只要稍微没对上，就很容易直接静默失败。