使用Oracle 10gMERGE語句更新數據行

在Oracle 9i R2版中引入的MERGE語句通常被稱作“更新插入”(upsert)，因為使用MERGE可以在同一個步驟中更新(update)並插入(insert)數據行，對於抽取、轉換和載入類型的應用軟件可以節省大量寶貴的時間，比如向數據倉庫中加載數據，數據倉庫中沒有的數據行可以插入到數據倉庫中，而已經存在的數據行也同時被更新。

在MERGE語句引入的時候，需要同時使用一條UPDATE和一條INSERT語句，順序也是固定的(先使用UPDATE語句，然後是INSERT語句)。如果您只需要使用其中的某一條一句，您只需要使用現有的INSERT或者UPDATE語句，而不必使用MERGE語句，而刪除數據可以使用DELETE語句。

在Oracle 10g R1版中，MERGE語句發生了變化，UPDATE或INSERT語句不再是必須的，而是可選項，您可以兩者都用也可以都不用，而且，UPDATE語句也具備了DELETE的功能，您可以在同一個步驟中對現有的有效記錄進行升級並清理廢棄的記錄。

列表A創建了一個表格列出現有項目：項目號碼、標題、開始日期、進度完成比例以及員工對項目的響應，還創建了一個事務表格使用MERGE語句進行升級批處理。

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', '08-JAN-07',0, 206);
　　 INSERT INTO open_projects VALUES
　　 (20, 'Upgrade Oracle on SRV01','15-JAN-07', 0, 206);
　　 INSERT INTO open_projects VALUES
　　 (30, 'Conduct skills assessment','22-JAN-07', 0, 210);
　　 CREATE TABLE project_updates
　　 (action CHAR(1),
　　 pno NUMBER(6),
　　 pctdone NUMBER(3),
　　 empno NUMBER(6)
　　 );
　　 INSERT INTO project_updates VALUES
　　 ('C', 10, 50, 214);
　　 INSERT INTO project_updates VALUES
　　 ('D', 20, NULL, NULL);
　　 COMMIT;

列表A

　一個典型的MERGE語句從識別表格開始執行升級，而且對現有的記錄進行篩選測試：

MERGE INTO open_projects op
　　 USING project_updatespu
　　 ON (op.pno = pu.pno)
　　 ...

表格open_projects會接受更新的數據，而project_updates表格則不會改變，如果項目號碼(pno)在兩個表格中都一樣，那麼數據行則被認為是相同的。

MERGE語句剩下的部分是更新語句，以及DELETE WHERE語法。

...
　　 WHEN MATCHED THEN
　　 UPDATE SET pctdone = pu.pctdone,
　　 empno = pu.empno
　　 DELETE
　　 WHERE pu.action = 'D';

列表B展示了MERGE語句運行前後的表格情況。

SQL> @mergedel_b
　　 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
　　 A PNO PCTDONE EMPNO
　　 - ---------- ---------- ----------
　　 C 10 50 214
　　 D 20
　　 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
　　 A PNO PCTDONE EMPNO
　　 - ---------- ---------- ----------
　　 C 10 50 214
　　 D 20
　　 SQL> spool off

列表B

第一個事務對第10號項目進行了改變(操作‘c’)，項目完成比例從0變成了50，項目員工人數變成了214人;第二個事務產出了第20號項目，“隨後”的列表展示了刪除後的狀態，而project_updates表格沒有發生改變。這個例子展示了這些語句並不是必須的，而且在MERGE語句中也並不需要使用INSERT語句。