Oracle PL/SQL：将批量MERGE改为单条提交循环并解决游标错误

问题描述 #

原存储过程实现了source_table到target_table的批量合并逻辑：

PROCEDURE merger
IS
BEGIN
    MERGE INTO target_table tat
         USING (SELECT s.W_ID, s.c_id, s.s_id
                  FROM source_table s) stat
            ON (tat.w_id = stat.w_id AND tat.c_id = stat.c_id)
    WHEN MATCHED
    THEN
        UPDATE SET tat.s_id = stat.s_id
    WHEN NOT MATCHED
    THEN
        INSERT     (W_ID, C_ID, S_ID)
            VALUES (stat.w_id, stat.c_id, stat.s_id);
END;

需求为修改代码，实现循环处理每条记录并在每次处理后提交。尝试Copilot生成的代码后，触发错误ORA-01002: 提取操作对无效或已关闭的游标执行，错误代码如下：

PROCEDURE merger
IS
    CURSOR w_cursor IS
        SELECT s.W_ID, s.c_id, s.s_id
          FROM source_table s;

    w_record   w_cursor%ROWTYPE;
BEGIN
    OPEN w_cursor;

    LOOP
        FETCH w_cursor INTO w_record;

        EXIT WHEN w_cursor%NOTFOUND;

        BEGIN
            MERGE INTO target_table tat
                 USING (SELECT w_record.W_ID     AS w_id,
                               w_record.c_id       AS c_id,
                               w_record.s_id     AS s_id
                          FROM DUAL) stat
                    ON (tat.w_id = stat.w_id AND tat.c_id = stat.c_id)
            WHEN MATCHED
            THEN
                UPDATE SET tat.sdst_id = stat.sdst_id
            WHEN NOT MATCHED
            THEN
                INSERT     (W_ID, C_ID, S_ID)
                    VALUES (stat.w_id, stat.c_id, stat.s_id);

            COMMIT;
        EXCEPTION
            WHEN OTHERS
            THEN
                ROLLBACK;
                RAISE;
        END;
    END LOOP;

    CLOSE w_cursor;
END;

错误原因分析 #

1. 游标被隐式关闭：Oracle中显式游标在执行COMMIT/ROLLBACK后会被自动关闭（Oracle 12c之前无保留游标特性），错误代码中第一次COMMIT后游标已失效，后续FETCH操作触发报错。
2. 字段名笔误：错误代码中UPDATE语句使用了不存在的字段sdst_id，与原逻辑的s_id不符。

修正后的解决方案 #

方案1：使用WITH HOLD保持游标打开（Oracle 12c+） #

Oracle 12c及以上版本支持WITH HOLD特性，可让游标在COMMIT后保持打开状态：

PROCEDURE merger
IS
    -- 声明带WITH HOLD的游标，提交后不关闭
    CURSOR w_cursor WITH HOLD IS
        SELECT s.W_ID, s.c_id, s.s_id
          FROM source_table s;

    w_record   w_cursor%ROWTYPE;
BEGIN
    OPEN w_cursor;

    LOOP
        FETCH w_cursor INTO w_record;
        EXIT WHEN w_cursor%NOTFOUND;

        BEGIN
            MERGE INTO target_table tat
                 USING (SELECT w_record.W_ID AS w_id,
                               w_record.c_id AS c_id,
                               w_record.s_id AS s_id
                          FROM DUAL) stat
                    ON (tat.w_id = stat.w_id AND tat.c_id = stat.c_id)
            WHEN MATCHED
            THEN
                UPDATE SET tat.s_id = stat.s_id -- 修正字段笔误
            WHEN NOT MATCHED
            THEN
                INSERT (W_ID, C_ID, S_ID)
                    VALUES (stat.w_id, stat.c_id, stat.s_id);

            COMMIT;
        EXCEPTION
            WHEN OTHERS
            THEN
                ROLLBACK;
                RAISE;
        END;
    END LOOP;

    CLOSE w_cursor;
END;

方案2：逐行获取数据（兼容低版本Oracle） #

针对Oracle 12c以下版本，可通过SELECT ... FOR UPDATE SKIP LOCKED逐行获取数据，避免游标被关闭的问题：

PROCEDURE merger
IS
    v_w_id  source_table.W_ID%TYPE;
    v_c_id  source_table.c_id%TYPE;
    v_s_id  source_table.s_id%TYPE;
BEGIN
    LOOP
        -- 每次获取一条未被锁定的记录，避免并发冲突
        SELECT s.W_ID, s.c_id, s.s_id
          INTO v_w_id, v_c_id, v_s_id
          FROM source_table s
         WHERE ROWNUM = 1
           FOR UPDATE SKIP LOCKED;

        BEGIN
            MERGE INTO target_table tat
                 USING (SELECT v_w_id AS w_id,
                               v_c_id AS c_id,
                               v_s_id AS s_id
                          FROM DUAL) stat
                    ON (tat.w_id = stat.w_id AND tat.c_id = stat.c_id)
            WHEN MATCHED
            THEN
                UPDATE SET tat.s_id = stat.s_id
            WHEN NOT MATCHED
            THEN
                INSERT (W_ID, C_ID, S_ID)
                    VALUES (stat.w_id, stat.c_id, stat.s_id);

            COMMIT;
        EXCEPTION
            WHEN OTHERS
            THEN
                ROLLBACK;
                RAISE;
        END;
    END LOOP;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 无更多记录时退出循环
        NULL;
END;

方案3：替换MERGE为UPDATE+INSERT逻辑 #

简化逻辑，先尝试更新，无匹配记录时再执行插入，避免依赖DUAL表：

PROCEDURE merger
IS
    CURSOR w_cursor WITH HOLD IS -- Oracle 12c+使用，低版本替换为方案2的逐行获取方式
        SELECT s.W_ID, s.c_id, s.s_id
          FROM source_table s;

    w_record   w_cursor%ROWTYPE;
BEGIN
    OPEN w_cursor;

    LOOP
        FETCH w_cursor INTO w_record;
        EXIT WHEN w_cursor%NOTFOUND;

        BEGIN
            -- 先尝试更新目标表
            UPDATE target_table tat
               SET tat.s_id = w_record.s_id
             WHERE tat.w_id = w_record.w_id
               AND tat.c_id = w_record.c_id;

            -- 无匹配记录时执行插入
            IF SQL%ROWCOUNT = 0 THEN
                INSERT INTO target_table (W_ID, C_ID, S_ID)
                VALUES (w_record.w_id, w_record.c_id, w_record.s_id);
            END IF;

            COMMIT;
        EXCEPTION
            WHEN OTHERS
            THEN
                ROLLBACK;
                RAISE;
        END;
    END LOOP;

    CLOSE w_cursor;
END;

注意事项 #

• 逐行提交会显著降低处理性能，仅在必须逐行提交的场景（如及时释放锁、避免大事务占用资源）下使用，原批量MERGE逻辑效率远高于逐行处理。
• FOR UPDATE SKIP LOCKED可避免并发处理时的锁等待，适合多会话同时处理数据的场景。
• 异常处理中RAISE会将异常向上抛出，可根据实际需求调整异常处理逻辑。

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