Oracle MERGE INTO語法詳解與UPSERT實戰範例

單一語句實現「存在更新、不存在插入」的標準UPSERT模式

Oracle 9i引入MERGE語句，取代傳統SELECT...IF EXISTS UPDATE ELSE INSERT邏輯，提供原子性、高效能、無競態條件的資料同步能力。

標準語法結構

MERGE [hint] INTO target_table [t_alias]
USING source_table|view|subquery [s_alias]
ON (join_condition)
WHEN MATCHED THEN
  UPDATE SET col1 = val1, col2 = val2 ...
[WHERE update_condition]          -- 10g+ 可選條件更新
[DELETE WHERE delete_condition]   -- 10g+ 可選刪除
WHEN NOT MATCHED THEN
  INSERT (col1, col2, ...) VALUES (val1, val2, ...)
[WHERE insert_condition];         -- 10g+ 可選條件插入

實戰範例：目標表同步

-- 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);

-- 2. 來源表(僅結構)
CREATE TABLE tj_test1 AS SELECT * FROM tj_test WHERE 1=0;
INSERT INTO tj_test1 VALUES (1, 'jkl', 23);  -- 同ID不同name

-- 3. MERGE同步：ID匹配更新name/age，否則插入
MERGE INTO tj_test1 t1
USING tj_test t
ON (t1.id = t.id)
WHEN MATCHED THEN
  UPDATE SET t1.name = t.name, t1.age = t.age
WHEN NOT MATCHED THEN
  INSERT (id, name, age) VALUES (t.id, t.name, t.age);

-- 4. 結果驗證
SELECT * FROM tj_test1 ORDER BY id;
-- 1 jan 23  (更新: jkl → jan)
-- 2 kk 22   (插入)
-- 3 joe 27  (插入)

進階用法場景

效能與最佳實踐

• ON條件欄位必須有索引：目標表與來源表連接鍵建立索引，避免全表掃描
• 大量資料分批處理：百萬級以上建議分批WHERE id BETWEEN ...或使用PARALLEL DML
• 啟用並行DML：ALTER SESSION ENABLE PARALLEL DML; MERGE /*+ PARALLEL(t1 4) */ ...
• 避免觸發器遞歸：MERGE觸發BEFORE/AFTER INSERT/UPDATE，注意業務邏輯遞歸
• 錯誤處理：11g+支援LOG ERRORS子句記錄失敗行至錯誤表

常見錯誤與避坑

1. ON條件不唯一：導致ORA-30926: unable to get a stable set of rows — 來源表連接鍵必須唯一
2. 未處理NULL值：ON (NVL(t1.id,-1) = NVL(t.id,-1)) 處理NULL匹配
3. 字面值硬編碼：務必使用綁定變數，共用游標