WHEN MATCHED THEN UPDATE 與 WHEN NOT MATCHED THEN INSERT,順序固定UPDATE 子句新增 DELETE WHERE,單一陳述式完成「更新+刪除」或「僅插入」等彈性組合
DROP TABLE open_projects;
DROP TABLE project_updates;
CREATE TABLE open_projects (
pno NUMBER(6) PRIMARY KEY,
title VARCHAR2(40),
startdate DATE,
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 on SRV01', DATE '2007-01-15', 0, 206);
INSERT INTO open_projects VALUES (30, 'Conduct skills assessment', DATE '2007-01-22', 0, 210);
CREATE TABLE project_updates (
action CHAR(1), -- 'C'=Change, 'D'=Delete
pno NUMBER(6),
pctdone NUMBER(3),
empno NUMBER(6)
);
INSERT INTO project_updates VALUES ('C', 10, 50, 214); -- 專案 10 進度 50%,負責人變 214
INSERT INTO project_updates VALUES ('D', 20, NULL, NULL); -- 刪除專案 20
COMMIT;
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' -- 10g 新增:符合條件則刪除該列
WHEN NOT MATCHED THEN
INSERT (pno, title, startdate, pctdone, empno)
VALUES (pu.pno, 'New Project', SYSDATE, pu.pctdone, pu.empno);
-- 執行前
PNO TITLE STARTDATE PCTDONE EMPNO
10 Inventory servers 08-JAN-07 0 206
20 Upgrade Oracle on SRV01 15-JAN-07 0 206
30 Conduct skills assessment 22-JAN-07 0 210
-- 2 rows merged.
-- 執行後
PNO TITLE STARTDATE PCTDONE EMPNO
10 Inventory servers 08-JAN-07 50 214 -- 更新
30 Conduct skills assessment 22-JAN-07 0 210 -- 保留
-- 專案 20 已刪除
| 特性 | 9i R2 | 10g R1+ |
|---|---|---|
| WHEN MATCHED UPDATE | 必須 | 可選 |
| WHEN NOT MATCHED INSERT | 必須 | 可選 |
| UPDATE 子句中 DELETE WHERE | 不支援 | 支援 |
| 可僅執行 UPDATE+DELETE | 否 | 是 |
| 可僅執行 INSERT | 否 | 是 |
MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.val = s.val;
-- 無 WHEN NOT MATCHED 子句
MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);
-- 無 WHEN MATCHED 子句
MERGE INTO orders o
USING (SELECT order_id FROM staging WHERE delete_flag = 'Y') s
ON (o.order_id = s.order_id)
WHEN MATCHED THEN UPDATE SET o.status = 'DELETED'
DELETE WHERE o.status = 'DELETED';
MERGE INTO fact_sales f
USING (
SELECT d.date_key, p.prod_key, SUM(amt) AS total_amt
FROM stage_sales s
JOIN dim_date d ON s.sale_dt = d.cal_dt
JOIN dim_prod p ON s.prod_cd = p.prod_cd
GROUP BY d.date_key, p.prod_key
) src
ON (f.date_key = src.date_key AND f.prod_key = src.prod_key)
WHEN MATCHED THEN UPDATE SET f.sales_amt = src.total_amt
WHEN NOT MATCHED THEN INSERT (date_key, prod_key, sales_amt) VALUES (src.date_key, src.prod_key, src.total_amt);
/*+ APPEND */ 僅適用 INSERT,MERGE 不直接支援V$SQL_PLAN 確認 HASH JOIN / MERGE JOIN 選擇正確若從 9i 升級,檢查現有 MERGE 語句:移除強制的空 UPDATE/INSERT 子句,善用 DELETE WHERE 簡化邏輯,減少程式碼行數與維護成本。