Oracle MERGE 語法詳解：UPSERT 核心技術與 10g/11g/12c/19c 新特性

MERGE 核心價值：單一陳述式完成 INSERT/UPDATE

MERGE (Oracle 9i 引入) 允許根據來源資料與目標表比對，單次執行即可完成「存在則更新、不存在則插入」(UPSERT) 邏輯，避免分開執行的競態條件與效能損耗。

基礎語法架構

MERGE INTO 目標表 T
USING 來源資料 S
ON (T.KEY = S.KEY)
WHEN MATCHED THEN
  UPDATE SET T.COL = S.COL ...
WHEN NOT MATCHED THEN
  INSERT (COL...) VALUES (S.COL...);

版本演進關鍵特性

Oracle 10g 增強 (本文重點)

1. UPDATE/INSERT 子句可選：可僅執行 UPDATE 或 INSERT
2. 支援 WHERE 子句：條件式更新/插入
3. ON 條件常數過濾：ON (1=0) 強制全量插入，無需連接
4. DELETE 子句：UPDATE 後可緊接 DELETE 移除不需要的行

11g/12c/19c 進階特性

• 錯誤記錄：LOG ERRORS 子句將 DML 錯誤導向錯誤表
• IGNORE_ROW_ON_DUPKEY_INDEX Hint：忽略唯一鍵衝突
• 合併多表：USING 子查詢可聯結多表

實戰範例完整演示

建立測試環境

CREATE TABLE PRODUCTS (
  PRODUCT_ID INTEGER PRIMARY KEY,
  PRODUCT_NAME VARCHAR2(60),
  CATEGORY VARCHAR2(60)
);

CREATE TABLE NEWPRODUCTS (
  PRODUCT_ID INTEGER,
  PRODUCT_NAME VARCHAR2(60),
  CATEGORY VARCHAR2(60)
);

-- 初始資料
INSERT INTO PRODUCTS VALUES (1501, 'VIVITAR 35MM', 'ELECTRNCS');
INSERT INTO PRODUCTS VALUES (1502, 'OLYMPUS IS50', 'ELECTRNCS');
INSERT INTO PRODUCTS VALUES (1600, 'PLAY GYM', 'TOYS');
INSERT INTO PRODUCTS VALUES (1601, 'LAMAZE', 'TOYS');
INSERT INTO PRODUCTS VALUES (1666, 'HARRY POTTER', 'DVD');

INSERT INTO NEWPRODUCTS VALUES (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
INSERT INTO NEWPRODUCTS VALUES (1601, 'LAMAZE', 'TOYS');
INSERT INTO NEWPRODUCTS VALUES (1666, 'HARRY POTTER', 'TOYS');
INSERT INTO NEWPRODUCTS VALUES (1700, 'WAIT INTERFACE', 'BOOKS');
COMMIT;

案例 1：僅更新匹配行

MERGE INTO PRODUCTS P
USING NEWPRODUCTS NP
ON (P.PRODUCT_ID = NP.PRODUCT_ID)
WHEN MATCHED THEN
  UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY;
-- 結果：1502,1601,1666 更新；1700 未插入

案例 2：僅插入新行

MERGE INTO PRODUCTS P
USING NEWPRODUCTS NP
ON (P.PRODUCT_ID = NP.PRODUCT_ID)
WHEN NOT MATCHED THEN
  INSERT VALUES (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY);
-- 結果：1700 插入；既有行不動

案例 3：條件式更新與插入

MERGE INTO PRODUCTS P
USING NEWPRODUCTS NP
ON (P.PRODUCT_ID = NP.PRODUCT_ID)
WHEN MATCHED THEN
  UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY
  WHERE P.CATEGORY = 'DVD'          -- 僅更新 DVD 類
WHEN NOT MATCHED THEN
  INSERT VALUES (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY)
  WHERE NP.CATEGORY != 'BOOKS';      -- 排除 BOOKS 類

案例 4：無條件全量插入 (ON 1=0)

MERGE INTO PRODUCTS P
USING NEWPRODUCTS NP
ON (1=0)  -- 永不匹配，強制走 NOT MATCHED
WHEN NOT MATCHED THEN
  INSERT VALUES (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY)
  WHERE NP.CATEGORY = 'BOOKS';       -- 僅插入 BOOKS

案例 5：UPDATE 後刪除 (DELETE 子句)

MERGE INTO PRODUCTS P
USING NEWPRODUCTS NP
ON (P.PRODUCT_ID = NP.PRODUCT_ID)
WHEN MATCHED THEN
  UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY
  DELETE WHERE (P.CATEGORY = 'ELECTRNCS')  -- 更新後刪除電子類
WHEN NOT MATCHED THEN
  INSERT VALUES (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY);

執行邏輯：1502 同時匹配 ON 與 DELETE 條件 → 刪除；1501 僅匹配 DELETE 不匹配 ON → 保留；1700 不匹配 ON → 插入；1601/1666 匹配 ON 不匹配 DELETE → 更新。

效能優化關鍵

• 索引支援：ON 條件欄位必須有索引 (唯一/主鍵最佳)
• 避免全表掃描：來源資料集較大時考慮 /*+ LEADING */ Hint
• 並行執行：MERGE /*+ PARALLEL(4) */ INTO ...
• 錯誤處理：LOG ERRORS INTO ERR_TABLE REJECT LIMIT UNLIMITED

常見錯誤與解決