用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/，如需转载，请注明出处，否则将追究法律责任。