Oracle存储过程插入报主键冲突，如何用NOT EXISTS实现去重插入

核心逻辑：你遇到的唯一键冲突是因为schedule表的联合主键为(schedule_id, location_id, base_date)，重复传入相同i_schedule_id、i_base_date调用存储过程时，生成的待插入数据主键和已存在的记录重复。使用NOT EXISTS方案只需要在插入的查询逻辑中，过滤掉已经存在的主键组合即可。

修改后的完整存储过程代码如下：

CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE
 (
  i_schedule_id IN PLS_INTEGER,
  i_base_date IN DATE,
  i_offset IN PLS_INTEGER DEFAULT 0, 
i_incr IN PLS_INTEGER DEFAULT 10,
  i_duration         IN PLS_INTEGER DEFAULT 5
)
 AS 
 
l_offset  interval day to second;
   l_incr interval day to second;
  l_duration interval day to second;

BEGIN

l_offset :=
NUMTODSINTERVAL(i_offset, 'SECOND') ;

l_incr :=
NUMTODSINTERVAL(i_incr, 'MINUTE') ;

l_duration :=
NUMTODSINTERVAL(i_duration, 'MINUTE') ;

        INSERT INTO schedule(
                schedule_id
              ,location_id
              ,base_date
              ,start_date
              ,end_date
          )
    SELECT   i_schedule_id
    ,        l.location_id
    ,        i_base_date
    ,      i_base_date + l_offset
                  + (l_incr * (ROWNUM - 1)) AS start_date
    ,      i_base_date + l_offset
                  + (l_incr * (ROWNUM - 1))
                + l_duration         AS end_date
    FROM      locations l
    -- 新增NOT EXISTS判断，过滤已存在的主键组合
    WHERE NOT EXISTS (
        SELECT 1 FROM schedule s
        WHERE s.schedule_id = i_schedule_id
          AND s.location_id = l.location_id
          AND s.base_date = i_base_date
    );
END;
/

验证方法 #

• 首次执行EXEC CREATE_SCHEDULE(1,TRUNC(SYSDATE))，15条对应location的schedule记录全部插入成功
• 再次执行相同调用，不会触发唯一键冲突报错，无符合条件的新记录插入，执行无异常

可选优化 #

如果存在高并发调用场景，可在INSERT关键字后添加hint /*+ ignore_row_on_dupkey_index(schedule schedule_pk) */，和NOT EXISTS做双层防护，彻底避免冲突可能。

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