oracle merge從9i到10g的增強

    MERGE語句是Oracle9i新增的文法，用來合并UPDATE和INSERT語句。串連條件匹配上的進行UPDATE，無法匹配的執行INSERT。這個文法僅需要一次全表掃描就完成了全部工作，執行效率要高於INSERT＋UPDATE。

    下面看個具體例子：

hr@ORCL> select * from p;        ID NAME---------- ----------         1 d         3 e         8 fhr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 b         3 chr@ORCL> merge into t using p  2         on (t.id=p.id)  3       when matched then  4     update set  5       t.name=p.name  6       when not matched then  7     insert values  8       (p.id,p.name)  9  ;3 rows merged.hr@ORCL> select * from t;        ID NAME---------- ----------         1 d         2 b         3 e         8 fhr@ORCL> commit;Commit complete.

    下面稍微修改一下例子：

hr@ORCL> insert into p values(1,'h');1 row created.hr@ORCL> commit;Commit complete.hr@ORCL> merge into t using p  2         on (t.id=p.id)  3       when matched then  4     update set  5       t.name=p.name  6       when not matched then  7     insert values  8       (p.id,p.name)  9  ; merge into t using p                    *ERROR at line 1:ORA-30926: unable to get a stable set of rows in the source tables

    這個錯誤是使用MERGE最常見的錯誤，造成這個錯誤的原因是由於通過串連條件得到的源表的記錄不唯一。在merge into時需要設定一個key值，會根據這個key值來決定merge into的操作（update還是insert into），所以要求在merge時這個key值是唯一的。所以要先從源表中選出全部資料而且key值是唯一的。

    還有一個錯誤也是比較常見的：

hr@ORCL> create table mm (id number,name varchar2(10));Table created.hr@ORCL> create table mn (id number,name varchar2(10));Table created.hr@ORCL> insert into mm values(1,'A');1 row created.hr@ORCL> insert into mn values(1,'B');1 row created.hr@ORCL> merge into mn using mm  2         on (mn.id=mm.id)  3       when matched then  4     update set mn.id=mm.id  5       when not matched then  6     insert values  7      (mm.id,mm.name);       on (mn.id=mm.id)           *ERROR at line 2:ORA-38104: Columns referenced in the ON Clause cannot be updated: "MN"."ID"

    10g增強一：where子句

    下面看一個小例子：

hr@ORCL> select * from p;        ID NAME---------- ----------         1 d         2 f         7 ghr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 b         3 chr@ORCL> merge into t using p  2         on (p.id=t.id)  3      when matched then  4        update set  5          t.name=p.name  6        where p.id=2;1 row merged.hr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 f         3 c

    10g增強二：delete子句

hr@ORCL> select * from p;        ID NAME---------- ----------         1 d         2 f         7 ghr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 f         3 chr@ORCL> merge into t using p  2         on (p.id=t.id)  3       when matched then  4     update set       5         t.name=p.name  6     delete where  7        (t.id=2);2 rows merged.hr@ORCL> select * from t;        ID NAME---------- ----------         1 d         3 c