使用Oracle 10g 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;

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

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

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