存储过程中如何将带FOR UPDATE的显式游标转为SYS_REFCURSOR返回？

问题分析 #

SYS_REFCURSOR作为输出参数时无法附加FOR UPDATE子句——一方面因为它本质是只读游标，另一方面如果带锁返回，游标会一直持有锁直到客户端关闭，极易引发锁等待问题。结合你锁数据→更新→返回数据的业务逻辑，可采用以下两种可行方案：

方案一：先完成锁与更新，再查询已处理数据返回 #

核心思路是先执行锁表、更新的逻辑，事务提交后，重新查询已更新的数据并通过SYS_REFCURSOR返回。需确保能准确筛选出本次处理的批次数据，避免混入其他批次结果。

修改后的存储过程示例：

/* Formatted on 10/12/2022 12:13:44 (QP5 v5.388) */
CREATE OR REPLACE PACKAGE BODY sh1.test IS
    PROCEDURE sms2 (p_return_code OUT INTEGER, v_out OUT SYS_REFCURSOR) IS
        v_run_type         VARCHAR (20) := 'SMS';
        v_batch_size       INTEGER := 100; -- 需补充批量大小的赋值逻辑
        v_threshold_size   INTEGER;
        v_retry_time       INTEGER;
        v_exception_list   VARCHAR (1);
        v_q_day            VARCHAR (10);
        -- 定义集合存储本次处理的主键ID，用于后续精准查询
        TYPE t_outbound_ids IS TABLE OF sh1.publish.outbound_event_id%TYPE;
        v_outbound_ids     t_outbound_ids;
    BEGIN
        DECLARE
            CURSOR c_cur IS
                  SELECT p.outbound_event_id,
                         cl.seq_id,
                         p.bill_account_code
                    FROM sh1.publish p
                    JOIN sh1.contact_list cl ON p.outbound_event_id = cl.outbound_event_id
                    JOIN sh1.actions_cfg ac ON p.action_id = ac.action_id
                   WHERE ac.das_service = v_run_type
                     AND p.retry_count <= ac.das_retry_count
                     AND ROWNUM <= v_batch_size
                ORDER BY p.creation_date DESC
                FOR UPDATE OF p.action_status, cl.status, p.mode_date, cl.mode_date SKIP LOCKED;
        BEGIN
            -- 批量获取锁定的主键ID
            OPEN c_cur;
            FETCH c_cur BULK COLLECT INTO v_outbound_ids;
            CLOSE c_cur;

            IF v_outbound_ids.COUNT = 0 THEN
                p_return_code := 2;
                RETURN;
            END IF;

            -- 批量更新publish表
            FORALL idx IN v_outbound_ids.FIRST .. v_outbound_ids.LAST
                UPDATE sh1.publish p
                   SET p.action_status = '1', p.mode_date = SYSDATE
                 WHERE p.outbound_event_id = v_outbound_ids(idx);

            -- 批量更新contact_list表
            FORALL idx IN v_outbound_ids.FIRST .. v_outbound_ids.LAST
                UPDATE sh1.contact_list cl
                   SET cl.status = 1, cl.mode_date = SYSDATE
                 WHERE cl.outbound_event_id = v_outbound_ids(idx);

            COMMIT;

            -- 打开SYS_REFCURSOR返回本次处理的数据
            OPEN v_out FOR
                  SELECT p.outbound_event_id,
                         cl.seq_id,
                         p.bill_account_code
                    FROM sh1.publish p
                    JOIN sh1.contact_list cl ON p.outbound_event_id = cl.outbound_event_id
                    JOIN sh1.actions_cfg ac ON p.action_id = ac.action_id
                   WHERE p.outbound_event_id MEMBER OF v_outbound_ids
                     AND ac.das_service = v_run_type
                ORDER BY p.creation_date DESC;

            p_return_code := 0;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line ('Error code:' || SQLCODE);
                DBMS_OUTPUT.put_line ('Error message:' || SQLERRM);
                ROLLBACK;
                p_return_code := 5;
        END;
    END sms2;
END test;

方案二：使用临时表存储锁定数据，从临时表返回 #

如果担心重新查询时数据被其他事务修改，可在锁数据时将需要返回的字段插入临时表，后续直接从临时表返回数据。

步骤1：创建全局临时表 #

CREATE GLOBAL TEMPORARY TABLE tmp_sms_result (
    outbound_event_id NUMBER,
    seq_id NUMBER,
    bill_account_code VARCHAR2(100)
) ON COMMIT PRESERVE ROWS; -- 事务提交后保留数据，直到会话结束

步骤2：修改存储过程 #

/* Formatted on 10/12/2022 12:13:44 (QP5 v5.388) */
CREATE OR REPLACE PACKAGE BODY sh1.test IS
    PROCEDURE sms2 (p_return_code OUT INTEGER, v_out OUT SYS_REFCURSOR) IS
        v_run_type         VARCHAR (20) := 'SMS';
        v_batch_size       INTEGER := 100;
        v_threshold_size   INTEGER;
        v_retry_time       INTEGER;
        v_exception_list   VARCHAR (1);
        v_q_day            VARCHAR (10);
    BEGIN
        DECLARE
            CURSOR c_cur IS
                  SELECT p.outbound_event_id,
                         cl.seq_id,
                         p.bill_account_code
                    FROM sh1.publish p
                    JOIN sh1.contact_list cl ON p.outbound_event_id = cl.outbound_event_id
                    JOIN sh1.actions_cfg ac ON p.action_id = ac.action_id
                   WHERE ac.das_service = v_run_type
                     AND p.retry_count <= ac.das_retry_count
                     AND ROWNUM <= v_batch_size
                ORDER BY p.creation_date DESC
                FOR UPDATE OF p.action_status, cl.status, p.mode_date, cl.mode_date SKIP LOCKED;

            cur_rec c_cur%ROWTYPE;
        BEGIN
            -- 清空临时表当前会话数据
            DELETE FROM tmp_sms_result;

            OPEN c_cur;
            LOOP
                FETCH c_cur INTO cur_rec;
                EXIT WHEN c_cur%NOTFOUND;

                -- 将需要返回的数据插入临时表
                INSERT INTO tmp_sms_result VALUES (cur_rec.outbound_event_id, cur_rec.seq_id, cur_rec.bill_account_code);

                -- 更新publish表
                UPDATE sh1.publish p
                   SET p.action_status = '1', p.mode_date = SYSDATE
                 WHERE p.outbound_event_id = cur_rec.outbound_event_id;

                -- 更新contact_list表
                UPDATE sh1.contact_list cl
                   SET cl.status = 1, cl.mode_date = SYSDATE
                 WHERE cl.outbound_event_id = cur_rec.outbound_event_id;
            END LOOP;
            CLOSE c_cur;

            COMMIT;

            -- 从临时表返回数据
            OPEN v_out FOR SELECT * FROM tmp_sms_result;

            p_return_code := 0;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line ('Error code:' || SQLCODE);
                DBMS_OUTPUT.put_line ('Error message:' || SQLERRM);
                ROLLBACK;
                p_return_code := 5;
        END;
    END sms2;
END test;

关键注意事项 #

1. 原代码存在逻辑错误：打开游标后FETCH一次就关闭，随后用FOR cur_rec IN c_cur LOOP遍历会重新打开游标，导致之前的锁丢失，必须修正。
2. 使用BULK COLLECT和FORALL可大幅提升批量处理性能，比逐行循环更高效。
3. 临时表方案需注意会话级数据清理，避免残留数据影响后续调用。

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