用merge 語句代替 insert 和delete

Oracle merge語句
前一段時間優化一個儲存過程，用到了merge語句，現在再來舉一個稍微詳細點的例子。
merge語句可以起到update and insert功能，並且在一條語句中實現。語法如下：

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
{ table | view | subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

例項：
13:32:55 SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

13:33:04 SQL>  create table dept_test as select * from dept where 1=2;

表已建立。

建立測試procudere
13:33:08 SQL> Create Or Replace Procedure merge_dept
13:34:14   2  Is
13:34:14   3  Begin
13:34:14   4  Merge Into dept_test a
13:34:14   5  Using (Select deptno,
13:34:14   6                dname,
13:34:14   7                loc
13:34:14   8           From dept
13:34:14   9           ) b
13:34:14  10  On (a.deptno=b.deptno)
13:34:14  11  When Matched Then
13:34:14  12  Update Set a.dname=b.dname, /*a.deptno=b.deptno  注意不要更新on 對應的列 */
13:34:14  13         a.loc=b.loc
13:34:14  14  When Not Matched Then
13:34:14  15  Insert (deptno,
13:34:14  16          dname,
13:34:14  17          loc)
13:34:14  18  Values (b.deptno,
13:34:14  19          b.dname,
13:34:14  20          b.loc
13:34:14  21          )
13:34:14  23          ;
13:34:14  24  dbms_output.put_line('successful!!!');
13:34:14  25  Commit;
13:34:14  26  exception
13:34:14  27  when Others Then
13:34:14  28  dbms_output.put_line('unsccessful!!!');
13:34:14  29  End merge_dept;
13:34:17  30  /
過程已建立。
13:34:19 SQL>
執行過程，測試如下：
13:34:19 SQL> set serveroutput on;
13:38:05 SQL> select count(*) from dept_test;

  COUNT(*)
----------
         0
13:38:13 SQL> exec merge_dept;
successful!!!

PL/SQL 過程已成功完成。
13:38:55 SQL> select * from dept_test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
結果dept和dept_test的資料一致
繼續測試，

13:39:33 SQL> delete dept where rownum<2;

已刪除 1 行。
13:39:41 SQL> update dept set loc='test' where deptno='30';
已更新 1 行。
13:39:59 SQL> commit;
提交完成。

13:40:01 SQL>  exec merge_dept;
successful!!!
PL/SQL 過程已成功完成。

13:40:07 SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          test
        40 OPERATIONS     BOSTON

13:40:13 SQL> select * from dept_test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          test
        20 RESEARCH       DALLAS
        40 OPERATIONS     BOSTON

13:40:19 SQL>
可以看到，dept中update的記錄在dept_test中也同樣變更過來了，但是
delete的記錄不會同步。此外用merge語句常出的一個錯誤就是update子
句中有on條件關聯列，這樣oracle會報錯的，把關聯條件的列從update子
句中移去即可（當when matched 的話，on 條件的二個欄位肯定要一樣）
，以上是在oracle9i環境下測試，

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-163514/，如需轉載，請註明出處，否則將追究法律責任。