如何获知Oracle中MERGE语句执行的操作？含存储过程单行场景

嘿，针对你这两个关于Oracle MERGE的问题，结合你提到的仅处理单行的场景，我给你分享几个实用的解决方案：

对于单行MERGE的场景，有两种直接且靠谱的方式：

• 用RETURNING子句直接捕获操作类型
  这是最直观的方法，你可以在MERGE的WHEN MATCHED和WHEN NOT MATCHED分支里，分别返回自定义的操作标识（比如'UPDATE'或'INSERT'），然后把这个值存入变量。示例代码如下：

  DECLARE
      v_operation VARCHAR2(10);
  BEGIN
      MERGE INTO target_table t
      USING (SELECT :p_id AS id, :p_value AS value FROM dual) s
      ON (t.id = s.id)
      WHEN MATCHED THEN
          UPDATE SET t.value = s.value
          RETURNING 'UPDATE' INTO v_operation
      WHEN NOT MATCHED THEN
          INSERT (id, value) VALUES (s.id, s.value)
          RETURNING 'INSERT' INTO v_operation;
      
      DBMS_OUTPUT.PUT_LINE('MERGE执行了: ' || v_operation);
  END;
  /
  

  这种方法在MERGE执行的同时就拿到了操作类型，没有额外的查询开销，非常适合单行场景。

• 先检查记录存在性，再结合SQL%ROWCOUNT判断
  如果RETURNING不符合你的需求，还可以在MERGE前先查询目标表是否存在该记录，MERGE后根据之前的检查结果判断操作类型。示例：

  DECLARE
      v_exists NUMBER;
      v_operation VARCHAR2(10);
  BEGIN
      -- MERGE前检查目标记录是否存在
      SELECT COUNT(1) INTO v_exists FROM target_table WHERE id = :p_id;
      
      MERGE INTO target_table t
      USING (SELECT :p_id AS id, :p_value AS value FROM dual) s
      ON (t.id = s.id)
      WHEN MATCHED THEN
          UPDATE SET t.value = s.value
      WHEN NOT MATCHED THEN
          INSERT (id, value) VALUES (s.id, s.value);
      
      -- 单行场景下，SQL%ROWCOUNT肯定是1（只要MERGE命中），直接根据之前的存在性判断
      v_operation := CASE v_exists WHEN 1 THEN 'UPDATE' ELSE 'INSERT' END;
      DBMS_OUTPUT.PUT_LINE('MERGE执行了: ' || v_operation);
  END;
  /
  

  注意：这个方法要考虑并发问题，如果有其他会话同时操作这条记录，可能会出现判断偏差，但如果是单会话或能保证并发安全的场景，完全可用。

基于上面的方法，在存储过程里可以直接把操作类型作为判断条件，调用对应的后续过程。这里推荐用RETURNING的方式，简洁且可靠：

CREATE OR REPLACE PROCEDURE handle_merge(p_id NUMBER, p_value VARCHAR2)
IS
    v_operation VARCHAR2(10);
BEGIN
    MERGE INTO target_table t
    USING (SELECT p_id AS id, p_value AS value FROM dual) s
    ON (t.id = s.id)
    WHEN MATCHED THEN
        UPDATE SET t.value = s.value
        RETURNING 'UPDATE' INTO v_operation
    WHEN NOT MATCHED THEN
        INSERT (id, value) VALUES (s.id, s.value)
        RETURNING 'INSERT' INTO v_operation;
    
    -- 根据操作类型调用不同的后续过程
    IF v_operation = 'UPDATE' THEN
        post_update_process(p_id); -- 替换成你的更新后处理存储过程
    ELSIF v_operation = 'INSERT' THEN
        post_insert_process(p_id); -- 替换成你的插入后处理存储过程
    END IF;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE; -- 抛出异常便于上层处理
END;
/

因为是单行场景，MERGE只会执行其中一个分支，所以v_operation只会是'UPDATE'或'INSERT'，判断逻辑完全不会出错。

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