Oracle无法识别表中已有记录触发ORA-00001唯一约束错误咨询

• 并发竞争条件
  你当前的先查询、后插入的逻辑不是原子操作，当多个会话同时处理相同匹配字段的记录时，会同时查到计数为0，随后先后发起插入操作，后提交的会话就会触发ORA-00001唯一约束报错。数据量低的时候冲突概率极低，所以清空表重插后会暂时恢复正常，业务运行一段时间数据量、并发量上来后，冲突概率上升就会再次复现，完全符合你描述的故障特征。
• 空值匹配逻辑缺陷
  Oracle中=判断遇到空值时会直接返回未知（等效于FALSE），如果你的匹配字段允许为空，哪怕表中已经存在相同空值的记录，查询条件也会判定为不匹配，导致你误以为没有重复记录，插入时触发约束冲突。

你可以根据业务场景任选以下方案：

1. 优先推荐：使用MERGE原子语句
   直接用Oracle原生的MERGE语句完成"不存在则插入"的逻辑，整个操作是原子性的，天然规避并发竞争问题，性能也优于先查后插的逻辑：

FOR loading_data IN LoadingDataCursor
LOOP
  MERGE INTO loaded_data l
  USING dual 
  ON (
    l.field1 = loading_data.field1 
    AND l.field2 = loading_data.field2
    -- 其他匹配字段，如果字段允许为空，要调整判断逻辑，例如：
    -- AND NVL(l.fieldX, '$$_NULL_MARKER_$$') = NVL(loading_data.fieldX, '$$_NULL_MARKER_$$')
  )
  WHEN NOT MATCHED THEN 
    INSERT (field1, field2, ... fieldN) 
    VALUES (loading_data.field1, loading_data.field2, ... loading_data.fieldN);
END LOOP;
COMMIT;

2. 轻量修复：捕获唯一约束异常
   如果不想大幅改动原有逻辑，可以给插入逻辑加异常捕获，遇到唯一约束冲突时直接跳过即可，冲突本身就说明已经存在符合要求的记录，符合你的业务逻辑：

FOR loading_data IN LoadingDataCursor
LOOP
  SELECT COUNT(1) INTO data_count FROM loaded_data l WHERE
  l.field1 = loading_data.field1 AND
  ...
  l.fieldN = loading_data.fieldN;

  IF data_count = 0 THEN
    BEGIN
      INSERT INTO loaded_data ...;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        NULL; -- 唯一约束冲突直接跳过即可
    END;
  END IF;
END LOOP;

COMMIT;

3. 加锁规避并发问题
   如果必须保留原有查询逻辑，可以给查询语句加行级锁，避免多个会话同时查到相同的空记录：

SELECT COUNT(1) INTO data_count FROM loaded_data l WHERE
l.field1 = loading_data.field1 AND
...
l.fieldN = loading_data.fieldN
FOR UPDATE;

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