SQL插入/更新时修改表列值及主键冲突处理方案咨询

推荐方案：使用MERGE语句 #

这是数据库原生的原子操作，能同时处理插入和更新逻辑，彻底避免主键冲突，是最优解。

针对你的业务场景，可编写如下MERGE语句（以Oracle数据库为例，适配OIC常用数据源）：

MERGE INTO EMP_TBL t
USING (
    -- 这里替换为待操作的EMP_ID列表，批量处理时可动态生成
    SELECT 'E3' AS EMP_ID FROM DUAL UNION ALL
    SELECT 'E4' AS EMP_ID FROM DUAL UNION ALL
    SELECT 'E6' AS EMP_ID FROM DUAL UNION ALL
    SELECT 'E7' AS EMP_ID FROM DUAL
) s
ON (t.EMP_ID = s.EMP_ID)
WHEN MATCHED THEN
    -- 匹配到已存在的EMP_ID，更新STATUS为'REPROCESS'
    UPDATE SET t.STATUS = 'REPROCESS'
WHEN NOT MATCHED THEN
    -- 未匹配到，插入新记录并设置STATUS为'NEW'
    INSERT (STATUS, EMP_ID) VALUES ('NEW', s.EMP_ID);

OIC中的配置方式： #

1. 在OIC的DB适配器中选择执行自定义SQL模式，将上述MERGE语句作为执行内容。
2. 若需批量处理动态EMP_ID列表，可使用绑定变量优化语句，示例如下：

MERGE INTO EMP_TBL t
USING (SELECT :emp_id AS EMP_ID FROM DUAL) s
ON (t.EMP_ID = s.EMP_ID)
WHEN MATCHED THEN
    UPDATE SET t.STATUS = 'REPROCESS'
WHEN NOT MATCHED THEN
    INSERT (STATUS, EMP_ID) VALUES ('NEW', s.EMP_ID);

随后在OIC适配器中配置批量参数，将待处理的EMP_ID列表批量传入执行。

备选方案：行级触发器实现 #

若无法使用MERGE语句，可通过触发器拦截插入操作，自动转为更新逻辑：

CREATE OR REPLACE TRIGGER TRG_EMP_TBL_INSERT
BEFORE INSERT ON EMP_TBL
FOR EACH ROW
DECLARE
    v_exists NUMBER;
BEGIN
    -- 检查当前EMP_ID是否已存在
    SELECT COUNT(1) INTO v_exists FROM EMP_TBL WHERE EMP_ID = :NEW.EMP_ID;
    
    IF v_exists > 0 THEN
        -- 存在则更新STATUS为'REPROCESS'
        UPDATE EMP_TBL SET STATUS = 'REPROCESS' WHERE EMP_ID = :NEW.EMP_ID;
        -- 抛出自定义错误阻止插入，避免主键冲突
        RAISE_APPLICATION_ERROR(-20001, 'Record updated to REPROCESS');
    ELSE
        -- 新记录默认设置STATUS为'NEW'
        :NEW.STATUS := 'NEW';
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获自定义错误，不向上抛出，确保更新操作生效
        IF SQLCODE = -20001 THEN
            NULL;
        ELSE
            RAISE;
        END IF;
END;
/

注意事项： #

• 触发器为行级操作，批量插入场景下性能劣于MERGE语句。
• 插入与更新为两个独立操作，原子性不如MERGE，高并发场景可能出现中间状态。

不推荐方案：先查询后操作 #

即在OIC中先查询EMP_ID是否存在，再分别执行更新或插入。此方案存在并发风险：若两个进程同时处理同一EMP_ID，查询时未检测到存在，但插入时已被其他进程写入，仍会触发主键冲突，因此不建议使用。

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