Oracle 10g MERGE 語句進階用法：Upsert 與同步刪除實戰

Oracle 9i R2 引入 MERGE（Upsert），10g R1 大幅增強：UPDATE/INSERT 子句成為可選、新增 DELETE WHERE 子句，可在單一陳述式完成「更新既有、插入新增、刪除標記」三合一操作，極適合 ETL 載入。

語法演進

MERGE INTO target_table t
USING source_table s
ON (t.pk = s.pk)
WHEN MATCHED THEN
  UPDATE SET t.col = s.col
  DELETE WHERE s.action = 'D'   -- 10g 新增
WHEN NOT MATCHED THEN
  INSERT (pk, col) VALUES (s.pk, s.col);

實戰範例：專案狀態批次同步

目標表 OPEN_PROJECTS（專案編號、標題、開始日、完成度、負責人）。
異動表 PROJECT_UPDATES（動作碼 C=更新/D=刪除、專案編號、完成度、負責人）。

MERGE INTO open_projects op
USING project_updates pu
ON (op.pno = pu.pno)
WHEN MATCHED THEN
  UPDATE SET op.pctdone = pu.pctdone, op.empno = pu.empno
  DELETE WHERE pu.action = 'D'
WHEN NOT MATCHED THEN
  INSERT (pno, title, startdate, pctdone, empno)
  VALUES (pu.pno, 'New Project', SYSDATE, pu.pctdone, pu.empno);

執行結果

• 專案 10：動作 C，完成度 0→50，負責人 206→214。
• 專案 20：動作 D，被刪除。
• 專案 30：無異動保持原狀。
• 專案 40（假設來源有新增）：被插入。

關鍵優勢

• 單一 SQL 完成三種操作，避免多次掃描目標表。
• 原子性保證：全部成功或全部回滾。
• DELETE WHERE 可依來源資料條件精準清理廢棄記錄。

10g MERGE 是資料倉庫增量載入、主檔同步的利器，建議取代傳統 PL/SQL 迴圈邏輯。