Oracle存储过程EMPNO主键重复加10插入仍报唯一约束错误排查

原存储过程存在3个核心逻辑错误，导致二次执行时报主键冲突：

• 异常分支插入逻辑完全错误：触发唯一键冲突时，没有针对当前冲突的单条记录做EMPNO+10处理，而是直接从源表employee_details查询全量数据，把所有记录的EMPNO统一加10后批量插入。第一次执行时批量插入的+10值刚好不存在，能跑通；第二次执行时原始值、+10后的值都已经存在于目标表，再次触发批量插入时必然报主键冲突。
• 多余逻辑干扰循环：FOR形式的游标循环本身会自动处理遍历终止逻辑，循环内写的EXIT WHEN table1_data%notfound属于无效冗余代码，极端场景下会导致循环提前终止。
• 异常覆盖不全、事务逻辑混乱：只捕获了主键冲突异常，其余错误没有统一处理；每插入一条记录就提交一次，还使用了自治事务，一旦执行中途报错，之前插入的数据无法回滚，会导致目标表数据不一致。

CREATE OR REPLACE PROCEDURE check_duplicate_row IS
    -- 待插入的员工号临时变量
    v_insert_empno emp_target.empno%TYPE;
    -- 冲突重试计数，避免死循环
    v_retry_cnt NUMBER := 0;
    -- 最大重试次数，防止无限加10都冲突
    c_max_retry CONSTANT NUMBER := 100;
BEGIN
    -- 遍历源表所有记录
    FOR employee_record IN (
        SELECT empno, empname FROM employee_details
    ) LOOP
        -- 初始化待插入的员工号为原始值
        v_insert_empno := employee_record.empno;
        v_retry_cnt := 0;
        
        <<insert_retry>>
        BEGIN
            INSERT INTO emp_target(empno, empname)
            VALUES (v_insert_empno, employee_record.empname);
        EXCEPTION
            WHEN dup_val_on_index THEN
                v_retry_cnt := v_retry_cnt + 1;
                IF v_retry_cnt > c_max_retry THEN
                    RAISE_APPLICATION_ERROR(-20001, '员工号'||employee_record.empno||'重试'||c_max_retry||'次后仍存在主键冲突，插入终止');
                END IF;
                -- 仅对当前冲突记录的员工号加10，重试插入
                v_insert_empno := v_insert_empno + 10;
                GOTO insert_retry;
            -- 统一捕获其余所有类型异常
            WHEN OTHERS THEN
                ROLLBACK;
                RAISE_APPLICATION_ERROR(-20002, '插入员工记录失败，员工号：'||employee_record.empno||'，错误信息：'||SQLERRM);
        END;
    END LOOP;
    -- 所有记录插入完成后统一提交
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

• 单条记录维度做冲突重试：遍历源表时，每条记录单独维护待插入的EMPNO值，触发主键冲突时仅给当前记录的EMPNO加10后重试，不会批量操作全表数据，从根源避免二次冲突。
• 增加重试熔断机制：设置最大重试次数，避免极端场景（比如目标表已存在同序列间隔10的员工号）下出现无限循环，重试超限时直接抛出明确错误终止流程。
• 全场景异常覆盖：单条记录插入、全局流程两层都加了异常捕获，非主键类错误触发时统一回滚全量事务，抛出带定位信息的错误提示，满足统一异常处理要求。
• 优化事务逻辑：去掉不必要的自治事务，所有记录全部插入成功后统一提交，出现错误全量回滚，保证目标表数据一致性。

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