Oracle MERGE INTO 實戰指南：單一陳述式實現 UPSERT、USING 子句陷阱與效能優化

MERGE 語法結構

MERGE INTO target_table alias1
USING (source_table | view | subquery) alias2
ON (join_condition)
WHEN MATCHED THEN
  UPDATE SET col1 = val1, col2 = val2 ...
WHEN NOT MATCHED THEN
  INSERT (col_list) VALUES (val_list);

核心邏輯：USING 子句決定行為

關鍵原則：USING 子查詢產生的 每一行 都會與目標表依 ON 條件比對：

• 比對成功 → 執行 WHEN MATCHED UPDATE
• 比對失敗 → 執行 WHEN NOT MATCHED INSERT

因此 總 INSERT/UPDATE 筆數 = USING 子查詢回傳行數。

經典錯誤：USING 直接查目標表

-- 錯誤範例：只能 UPDATE，永遠不會 INSERT
MERGE INTO T T1
USING (SELECT a,b FROM T WHERE a='1001') T2
ON (T1.a = T2.a)
WHEN MATCHED THEN UPDATE SET T1.b = 2
WHEN NOT MATCHED THEN INSERT (a,b) VALUES ('1001',2);

原因：當目標表無 a='1001' 時，子查詢回傳 0 行，MERGE 無行可處理，直接結束，INSERT 從未觸發。

正確寫法：USING DUAL 或 VALUES 產生單行

MERGE INTO T T1
USING (SELECT '1001' AS a, 2 AS b FROM DUAL) T2
ON (T1.a = T2.a)
WHEN MATCHED THEN UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN INSERT (a,b) VALUES (T2.a, T2.b);

或 10g+ 支援 VALUES 子句（簡潔）：

MERGE INTO T T1
USING (SELECT '1001' a, 2 b FROM DUAL) T2
ON (T1.a = T2.a)
WHEN MATCHED THEN UPDATE SET b = T2.b
WHEN NOT MATCHED THEN INSERT (a,b) VALUES (T2.a, T2.b);

批次 UPSERT 範例（多筆資料）

MERGE INTO employees e
USING (
  SELECT 1001 emp_id, 'John' emp_name, 5000 sal FROM DUAL UNION ALL
  SELECT 1002, 'Jane', 6000 FROM DUAL UNION ALL
  SELECT 1003, 'Bob', 5500 FROM DUAL
) src
ON (e.emp_id = src.emp_id)
WHEN MATCHED THEN UPDATE SET e.emp_name = src.emp_name, e.sal = src.sal
WHEN NOT MATCHED THEN INSERT (emp_id, emp_name, sal) VALUES (src.emp_id, src.emp_name, src.sal);

進階用法

效能與鎖行為

• MERGE 會對目標表取得 TX 排他鎖，逐行處理，大批次建議分批 COMMIT 或使用 PARALLEL DML。
• ON 條件欄位建立索引可大幅減少全表掃描。
• 避免在高併發熱點表上執行長時間 MERGE，易引發 ORA-00060 死鎖。
• 12c+ 可用 MERGE /*+ BYPASS_RECURSIVE_CHECK */ 繞過遞迴檢查（特定場景）。

危險模式警示

-- 極度危險：USING 回傳全表，ON 條件為常數 TRUE
MERGE INTO T T1
USING (SELECT COUNT(*) cnt FROM T WHERE a='1001') T2
ON (T2.cnt > 0)
WHEN MATCHED THEN UPDATE SET T1.b = 2
WHEN NOT MATCHED THEN INSERT (a,b) VALUES ('1001',2);
-- 結果：全表所有行被 UPDATE，或全表 INSERT 重複行！

永遠確認 USING 回傳行數與預期一致，ON 條件為精確主鍵/唯一鍵比對。