前言
傳統「先查後插/更新」模式需兩次數據庫往返，且存在競態條件。Oracle 9i+ 引入 MERGE 語句，單條 SQL 原子化實現 UPSERT。本文詳解語法、實戰範例、性能優化、並發陷阱與最佳實踐。
一、MERGE 語法結構
MERGE INTO target_table t
USING source_data s
ON (join_condition)
WHEN MATCHED THEN
UPDATE SET col1 = expr1, col2 = expr2 ...
[WHERE update_condition]
WHEN NOT MATCHED THEN
INSERT (col1, col2, ...) VALUES (expr1, expr2, ...)
[WHERE insert_condition];
關鍵點
USING 來源：表、視圖、子查詢、雙表連接ON 條件：匹配邏輯，決定走 UPDATE 還是 INSERTWHEN MATCHED：可加 WHERE 進一步過濾WHEN NOT MATCHED：可加 WHERE 條件插入- UPDATE/INSERT 子句不寫表別名 (與獨立 DML 不同)
二、基礎實戰範例
1. 測試表準備
CREATE TABLE test_merge (
id NUMBER PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
sex VARCHAR2(2) DEFAULT '男'
);
INSERT INTO test_merge VALUES (1, 'SUNZHENXING', '男');
INSERT INTO test_merge VALUES (2, 'SUNHAILONG', '女');
COMMIT;
2. MERGE 實現：按 name 判斷，存在更新 sex，不存在插入
MERGE INTO test_merge t
USING (SELECT 3 AS id, 'SUNZHENXING' AS name, '女' AS sex FROM DUAL) s
ON (t.name = s.name)
WHEN MATCHED THEN
UPDATE SET t.sex = s.sex WHERE t.name = 'SUNZHENXING'
WHEN NOT MATCHED THEN
INSERT (id, name, sex) VALUES (s.id, s.name, s.sex);
3. 執行結果
| 場景 | 執行前 | 執行後 | 說明 |
|---|
| name='SUNZHENXING' 存在 | id=1, sex='男' | id=1, sex='女' | 走 UPDATE 分支 |
| name='NEW_USER' 不存在 | 無 | id=3, name='NEW_USER', sex='女' | 走 INSERT 分支 |
三、進階用法
1. 來源為子查詢 (批量 UPSERT)
MERGE INTO target_table t
USING (
SELECT id, name, status, updated_at
FROM staging_table
WHERE batch_id = :current_batch
) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.name = s.name, t.status = s.status, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
INSERT (id, name, status, created_at, updated_at)
VALUES (s.id, s.name, s.status, s.updated_at, s.updated_at);
2. 僅更新、不插入 (同步修改)
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.col1 = s.col1, t.col2 = s.col2;
3. 僅插入、不更新 (去重插入)
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN NOT MATCHED THEN
INSERT (id, col1, col2) VALUES (s.id, s.col1, s.col2);
4. DELETE 子句 (10g+)：匹配且滿足條件則刪除
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.col1 = s.col1
DELETE WHERE t.status = 'OBSOLETE' -- 更新後再判斷是否刪除
WHEN NOT MATCHED THEN
INSERT (id, col1) VALUES (s.id, s.col1);
四、性能優化關鍵
| 優化點 | 建議 | 原理 |
|---|
| ON 條件列有索引 | 目標表 ON 列創建索引 | 避免全表掃描匹配 |
| 來源數據量控制 | 單次 MERGE 建議 < 10 萬行 | 大數據量分批處理 |
| 並行 DML | ALTER SESSION ENABLE PARALLEL DML; MERGE /*+ PARALLEL(t,4) */ ... | 大表合併利用多 CPU |
| 避免觸發器 | 目標表盡量無行級觸發器 | 逐行觸發器殺死 MERGE 性能 |
| 統計信息新鮮 | 合併前收集目標/來源表統計 | 優化器選擇最佳連接方式 |
五、並發與一致性陷阱
1. 競態條件
會話 A 查無記錄 → 準備 INSERT | 會話 B 查無記錄 → 準備 INSERT | 兩者均執行 INSERT → 唯一鍵衝突
2. MERGE 原子性保障
- 單條 MERGE 在語句級別原子執行，無競態條件
- 鎖機制：掃描目標表獲取行鎖 (RX) 或表鎖，阻塞並發衝突
3. 死鎖風險
- 多會話 MERGE 同一目標表，不同來源數據順序不同 → 死鎖
- 緩解：來源數據按主鍵排序、縮短事務、設置
LOCK TABLE ... IN EXCLUSIVE MODE NOWAIT 串行化
六、常見錯誤與調試
| 錯誤 | 原因 | 修正 |
|---|
| ORA-00904: "T"."COL": invalid identifier | UPDATE/INSERT 子句使用了表別名 | 直接寫列名：UPDATE SET col = val |
| ORA-30926: unable to get a stable set of rows | 來源數據 ON 條件非唯一，匹配多行 | 來源去重、加唯一約束、或用 ROW_NUMBER() 取第一行 |
| ORA-01400: cannot insert NULL | NOT NULL 列 INSERT 未賦值 | 檢查 INSERT 列表完整性、DEFAULT 值 |
| 性能極差 | 無索引、大表全掃描、觸發器 | 加索引、分批、禁用觸發器 |
七、MERGE vs 傳統 PL/SQL 對比
| 維度 | 傳統 SELECT + IF + INSERT/UPDATE | MERGE |
|---|
| 網絡往返 | 2-3 次 | 1 次 |
| 原子性 | 需顯式鎖 (SELECT FOR UPDATE) | 語句級原子 |
| 代碼複雜度 | 高 (PL/SQL 塊) | 低 (單條 SQL) |
| 批量處理 | 需 FORALL/BULK COLLECT | 原生集合操作 |
| 可讀性 | 分散邏輯 | 聲明式、意圖清晰 |
結語
MERGE 是 Oracle 實現 UPSERT 的標準利器。掌握 ON 匹配邏輯、WHEN MATCHED/NOT MATCHED 分支、DELETE 子句、並行 Hint、索引策略，並警惕 ORA-30926 來源非唯一、死鎖風險，即可在高併發、大數據量場景下構建高效、正確、可維護的數據同步邏輯。
