Oracle 10g MERGE 語句進階應用：UPSERT 與條件刪除

MERGE 基礎語法

MERGE INTO target_table t
USING source_table s
ON (t.pk = s.pk)
WHEN MATCHED THEN
  UPDATE SET t.col1 = s.col1, t.col2 = s.col2
  DELETE WHERE   -- 10g 新增：符合條件則刪除
WHEN NOT MATCHED THEN
  INSERT (pk, col1, col2) VALUES (s.pk, s.col1, s.col2);

10g 關鍵增強

• WHEN MATCHED 子句中 UPDATE 與 DELETE 皆為可選，可只做其中之一。
• DELETE WHERE 子句允許在更新後依條件刪除該列。
• 可省略 WHEN NOT MATCHED 僅做更新/刪除。

實戰範例：專案進度批次同步

-- 目標表：現有專案
CREATE TABLE open_projects (
  pno      NUMBER(6) PRIMARY KEY,
  title    VARCHAR2(40),
  startdate DATE,
  pctdone  NUMBER(3),
  empno    NUMBER(6)
);

-- 來源表：批次更新指令
CREATE TABLE project_updates (
  action   CHAR(1),      -- 'C'=變更, 'D'=刪除
  pno      NUMBER(6),
  pctdone  NUMBER(3),
  empno    NUMBER(6)
);

-- 測試資料
INSERT INTO open_projects VALUES (10, 'Inventory servers', DATE'2007-01-08', 0, 206);
INSERT INTO open_projects VALUES (20, 'Upgrade Oracle',    DATE'2007-01-15', 0, 206);
INSERT INTO open_projects VALUES (30, 'Skills assessment', DATE'2007-01-22', 0, 210);

INSERT INTO project_updates VALUES ('C', 10, 50, 214);  -- 更新專案 10
INSERT INTO project_updates VALUES ('D', 20, NULL, NULL); -- 刪除專案 20
COMMIT;

單一 MERGE 完成更新與刪除

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'  -- action='D' 者更新後刪除
WHEN NOT MATCHED THEN
  INSERT (pno, title, startdate, pctdone, empno)
  VALUES (pu.pno, 'New Project', SYSDATE, pu.pctdone, pu.empno);

執行結果

-- 專案 10：進度 0→50，負責人 206→214
-- 專案 20：被刪除
-- 專案 30：不變
-- project_updates 表保持不變

效能與最佳實務

• 來源表建議建立 ON 條件欄位索引（此處 project_updates.pno）。
• 大量資料可加 /*+ APPEND */ Hint 於 INSERT 子句（11g+）。
• 避免在 WHEN MATCHED 更新非索引鍵欄位導致索引維護開銷。
• 配合 LOG ERRORS 子句（10g R2+）記錄錯誤列而不中斷整批次。

擴展：多來源合併

可使用 UNION ALL 或 PIVOT 將多個來源表合併為單一 USING 來源：

MERGE INTO target t
USING (
  SELECT * FROM src1 WHERE batch_id = 1
  UNION ALL
  SELECT * FROM src2 WHERE batch_id = 1
) s
ON (t.id = s.id)
...