萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 9i MERGE 語法詳解:UPSERT (有則更新、無則插入) 實戰範例

語法結構


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 (反向同步)。
copyright © 萬盛學電腦網 all rights reserved