Oracle 9i MERGE 語法詳解與「有則更新無則插入」實戰

語法結構

MERGE [hint] INTO [schema.]table [t_alias]
USING [schema.]{table | view | subquery} [t_alias]
ON (condition)
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

Oracle 9i 引入 MERGE，單條語句實現 UPSERT，避免「SELECT → IF EXISTS UPDATE ELSE INSERT」的競爭條件與雙次解析開銷。

實戰演示

1. 準備源表與目標表

CREATE TABLE tj_test (id NUMBER, name VARCHAR2(20), age NUMBER);
INSERT INTO tj_test VALUES (1,'jan',23);
INSERT INTO tj_test VALUES (2,'kk',22);
INSERT INTO tj_test VALUES (3,'joe',27);

CREATE TABLE tj_test1 AS SELECT * FROM tj_test WHERE 1=0;  -- 空表結構
INSERT INTO tj_test1 VALUES (1,'jkl',23);  -- 僅 id=1 存在，name 不同

2. 執行 MERGE

MERGE INTO tj_test1 tt1
USING tj_test tt
ON (tt1.id = tt.id)
WHEN MATCHED THEN
  UPDATE SET tt1.name = tt.name, tt1.age = tt.age
WHEN NOT MATCHED THEN
  INSERT (id, name, age) VALUES (tt.id, tt.name, tt.age);

3. 結果驗證

SELECT * FROM tj_test1 ORDER BY id;
-- 輸出:
-- 1 jan 23   -- 原 jkl 被更新為 jan
-- 2 kk 22    -- 新增
-- 3 joe 27   -- 新增

進階用法

條件更新/刪除

MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.val = s.val
  WHERE s.val > 100          -- 只有滿足條件才更新
  DELETE WHERE s.val < 10    -- 更新後再刪除滿足條件的行
WHEN NOT MATCHED THEN
  INSERT (id, val) VALUES (s.id, s.val)
  WHERE s.val >= 10;         -- 只有滿足條件才插入

僅更新/僅插入

省略 WHEN NOT MATCHED 則僅更新；省略 WHEN MATCHED 則僅插入 (等同 INSERT WHERE NOT EXISTS)。

性能優勢

• 單次掃描源數據，減少一致性讀開銷。
• 避免應用層「查-改」往返網絡延遲。
• 原子性：要么全成功要么全回滾，無中間狀態。

常見錯誤

• ORA-30926: ON 條件在源數據中不唯一，導致無法確定單一匹配行。解決：源端加 DISTINCT 或聚合保證唯一鍵。
• 並發死鎖： 大批量 MERGE 建議按主鍵範圍分批提交，並確保索引完善。