Oracle存储过程实现staging表同步主表按e_id存在则更新的方法

修改方案 #

Oracle 提供原生MERGE语句支持「存在则更新、不存在则插入」的upsert逻辑，直接替换原有INSERT逻辑即可，修改后的存储过程如下：

create or replace procedure sp_main(ov_err_msg OUT varchar2)
is
begin
  MERGE INTO new_details_main m
  USING (
    SELECT 
      n.e_id,
      n.e_name,
      lp.ref_id AS portal_ref,
      lr.ref_id AS risk_ref
    FROM new_details_staging n
    -- 关联映射表获取portal对应的ref_id
    LEFT JOIN lookup_ref lp 
      ON lp.ref_typ = 'portal' 
      AND lp.ref_typ_desc = n.portal_desc
    -- 关联映射表获取risk对应的ref_id
    LEFT JOIN lookup_ref lr 
      ON lr.ref_typ = 'risk' 
      AND lr.ref_typ_desc = n.risk_dec
  ) src
  -- 匹配条件：主键e_id相等
  ON (m.e_id = src.e_id)
  -- 匹配到则更新对应字段
  WHEN MATCHED THEN
    UPDATE SET 
      m.e_name = src.e_name,
      m.portal = src.portal_ref,
      m.risk = src.risk_ref
  -- 未匹配到则插入新记录
  WHEN NOT MATCHED THEN
    INSERT (e_id, e_name, portal, risk)
    VALUES (src.e_id, src.e_name, src.portal_ref, src.risk_ref);

  -- 执行成功清空错误信息
  ov_err_msg := null;
EXCEPTION
  WHEN OTHERS THEN
    -- 捕获异常返回错误信息
    ov_err_msg := '执行失败：' || SQLERRM;
    ROLLBACK;
end;
/

逻辑说明 #

• 相比原有的子查询写法，改用左连接关联映射表，逻辑更清晰、执行效率更高
• 批量执行无需逐行判断存在性，性能更优，同时避免并发场景下的主键冲突问题
• 新增异常捕获逻辑，执行报错时会通过出参ov_err_msg返回具体错误信息

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