为何ROLLBACK后SYS_REFCURSOR会关闭？文档无说明但实际出现该情况

问题现象 #

执行ROLLBACK TO SAVEPOINT后，此前已打开并确认处于打开状态的SYS_REFCURSOR被意外关闭，Oracle官方文档未明确提及回滚至保存点会关闭已加载数据的游标，但实际业务代码中出现了该行为：

PROCEDURE p_get_employ_data(
    in_account_prefix IN ucet_jadro.predcislie%TYPE,
    in_account_number IN ucet_jadro.cislo_uctu%TYPE,
    in_out_cursor IN OUT SYS_REFCURSOR
  ) IS
  BEGIN
    OPEN in_out_cursor FOR
      SELECT 
          status_id status,
          balance balance,
          dispo_balance dispo_balance
        FROM employ j
        WHERE j.acc_pfx = in_account_prefix
          AND j.acc_num = in_account_number;
    -- 查询结果行数为1，已存入游标
  END p_get_employ_data;

DECLARE
    l_expected_account SYS_REFCURSOR;
BEGIN
    SAVEPOINT specimen_savepoint;
    p_set_employ_data_old_process(...);
    p_get_employ_data(
      in_account_prefix => g_account_prefix,
      in_account_number => g_account_number,
      in_out_cursor => l_expected_account
    );
    
    IF l_expected_account%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('01. l_expected_account is open'); -- 游标处于打开状态
    ELSE
      DBMS_OUTPUT.PUT_LINE('01. l_expected_account is closed');
    END IF;
    -- 回滚至保存点，为新流程准备相同数据
    ROLLBACK TO specimen_savepoint;
    IF l_expected_account%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('02. l_expected_account is open');
    ELSE
      DBMS_OUTPUT.PUT_LINE('02. l_expected_account is closed'); -- 游标已关闭
    END IF;

    p_set_employ_data_new_process(...);
    --...
END;

原因分析 #

Oracle的ROLLBACK TO SAVEPOINT操作会隐式清理保存点之后创建的事务相关资源，包括在该阶段打开的游标。尽管官方文档未明确说明这一细节，但这是Oracle事务管理的内部机制：保存点之后打开的游标属于当前事务分支的临时资源，回滚到保存点时会释放这些资源，从而导致游标被关闭。

解决方案 #

方案1：回滚前提取游标数据到本地变量 #

在执行回滚操作前，将游标中的数据提取到本地记录或集合中，这样即使游标被关闭，数据已被持久化到本地变量，可继续使用：

DECLARE
    l_expected_account SYS_REFCURSOR;
    -- 定义与游标结果匹配的记录类型
    TYPE t_employ_rec IS RECORD(
        status employ.status_id%TYPE,
        balance employ.balance%TYPE,
        dispo_balance employ.dispo_balance%TYPE
    );
    l_employ_data t_employ_rec;
BEGIN
    SAVEPOINT specimen_savepoint;
    p_set_employ_data_old_process(...);
    p_get_employ_data(
      in_account_prefix => g_account_prefix,
      in_account_number => g_account_number,
      in_out_cursor => l_expected_account
    );
    
    -- 提取游标数据到本地变量
    FETCH l_expected_account INTO l_employ_data;
    -- 主动关闭游标，避免资源泄漏
    IF l_expected_account%ISOPEN THEN
        CLOSE l_expected_account;
    END IF;

    ROLLBACK TO specimen_savepoint;
    -- 后续直接使用l_employ_data中的数据即可
    DBMS_OUTPUT.PUT_LINE('Status: ' || l_employ_data.status);
    
    p_set_employ_data_new_process(...);
    --...
END;

方案2：将游标打开操作移至保存点之前 #

调整代码逻辑，在创建保存点之前打开游标，这样游标属于保存点之前的事务资源，回滚到保存点不会影响其状态：

DECLARE
    l_expected_account SYS_REFCURSOR;
BEGIN
    -- 先打开游标，再设置保存点
    p_get_employ_data(
      in_account_prefix => g_account_prefix,
      in_account_number => g_account_number,
      in_out_cursor => l_expected_account
    );
    
    SAVEPOINT specimen_savepoint;
    p_set_employ_data_old_process(...);
    
    -- 确认游标状态
    IF l_expected_account%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('01. l_expected_account is open');
    END IF;

    ROLLBACK TO specimen_savepoint;
    -- 此时游标依然保持打开状态
    IF l_expected_account%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('02. l_expected_account is open');
    END IF;

    p_set_employ_data_new_process(...);
    --...
    -- 最后记得关闭游标
    IF l_expected_account%ISOPEN THEN
        CLOSE l_expected_account;
    END IF;
END;

方案3：使用自治事务打开游标 #

将打开游标的存储过程声明为自治事务，使游标所属事务与主事务分离，主事务的回滚操作不会影响自治事务中打开的游标。注意自治事务需要提交，否则可能无法正常访问游标数据：

PROCEDURE p_get_employ_data(
    in_account_prefix IN ucet_jadro.predcislie%TYPE,
    in_account_number IN ucet_jadro.cislo_uctu%TYPE,
    in_out_cursor IN OUT SYS_REFCURSOR
  ) IS
    -- 声明自治事务
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    OPEN in_out_cursor FOR
      SELECT 
          status_id status,
          balance balance,
          dispo_balance dispo_balance
        FROM employ j
        WHERE j.acc_pfx = in_account_prefix
          AND j.acc_num = in_account_number;
    -- 自治事务必须提交，否则游标可能无法正常读取
    COMMIT;
END p_get_employ_data;

注意事项 #

• 提取游标数据时，需处理NO_DATA_FOUND和TOO_MANY_ROWS异常，确保数据读取的稳定性。
• 使用自治事务时，需确认业务逻辑允许数据隔离，避免主事务修改的数据与自治事务查询的数据出现一致性问题。

内容的提问来源于stack exchange，提问作者Peticek