語法結構
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;
實戰建表與測試資料
-- 目標表
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);
-- 來源表 (含 1 筆相同 ID、不同 NAME)
CREATE TABLE tj_test1 AS SELECT * FROM tj_test WHERE 1=0;
INSERT INTO tj_test1 VALUES (1,'jkl',23); -- ID=1 已存在,NAME 不同
MERGE 實現 UPSERT
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 VALUES (tt.id, tt.name, tt.age);
執行結果驗證
SELECT * FROM tj_test1 ORDER BY id;
-- 1 jan 23 -- 原 jkl 被更新為 jan
-- 2 kk 22 -- 新增
-- 3 joe 27 -- 新增
進階用法
1. 僅更新、不插入
MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.val = s.val;
-- 省略 WHEN NOT MATCHED
2. 僅插入、不更新
MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.val);
3. 刪除條款 (10g+)
MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.val = s.val
DELETE WHERE s.val < 0 -- 更新後若符合條件則刪除該行
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.val);
4. 搭配 Hint 控制執行計畫
MERGE /*+ USE_HASH(t s) */ INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.val);
效能優勢
- 單一語句完成「查找-決定-執行」,減少應用層往返與解析開銷。
- 避免
SELECT ... FOR UPDATE + INSERT/UPDATE 兩階段鎖定競爭。
- 優化器可選擇 HASH JOIN / NESTED LOOPS 最佳路徑。
注意事項
ON 條件建議走唯一索引/主鍵,確保匹配唯一性。
- 大量資料合併時,考慮
PARALLEL Hint 或 CTAS 重建表。
- 12c+ 支援
MERGE ... WHEN NOT MATCHED BY SOURCE THEN DELETE (反向同步)。