MERGE用法
MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。其语法如下：
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;
示例：
SQL> create table tab_emp as select empno, ename, deptno, sal from emp where 1 = 0;
Table created
SQL> select * from tab_emp;
EMPNO ENAME DEPTNO SAL
----- ---------- ------ ---------
SQL> merge into tab_emp a
2 using emp b
3 on(a.empno = b.empno)
4 when matched then
5 update
6 set a.ename = b.ename,
7 a.deptno = b.deptno,
8 a.sal = b.sal
9 when not matched then
10 insert(empno, ename, deptno, sal)
11 values(b.empno, b.ename, b.deptno, b.sal);
Done
SQL> commit;
Commit complete
SQL> select * from tab_emp;
EMPNO ENAME DEPTNO SAL
----- ---------- ------ ---------
7369 SMITH 20 800.00
7499 ALLEN 30 1600.00
7521 WARD 30 1250.00
7566 JONES 20 2975.00
7654 MARTIN 30 1250.00
7698 BLAKE 30 2850.00
7782 CLARK 10 2450.00
7788 SCOTT 20 3000.00
7839 KING 10 5000.00
7844 TURNER 30 1500.00
7876 ADAMS 20 1100.00
7900 JAMES 30 950.00
7902 FORD 20 3000.00
7934 MILLER 10 1300.00
14 rows selected
SQL> update emp set sal = 2000 where empno = '7499';
1 row updated
SQL> commit;
Commit complete
SQL>
SQL> merge into tab_emp a
2 using emp b
3 on(a.empno = b.empno)
4 when matched then
5 update
6 set a.ename = b.ename,
7 a.deptno = b.deptno,
8 a.sal = b.sal
9 when not matched then
10 insert(empno, ename, deptno, sal)
11 values(b.empno, b.ename, b.deptno, b.sal);
Done
SQL> commit;
Commit complete
SQL> select * from tab_emp;
EMPNO ENAME DEPTNO SAL
----- ---------- ------ ---------
7369 SMITH 20 800.00
7499 ALLEN 30 2000.00
7521 WARD 30 1250.00
7566 JONES 20 2975.00
7654 MARTIN 30 1250.00
7698 BLAKE 30 2850.00
7782 CLARK 10 2450.00
7788 SCOTT 20 3000.00
7839 KING 10 5000.00
7844 TURNER 30 1500.00
7876 ADAMS 20 1100.00
7900 JAMES 30 950.00
7902 FORD 20 3000.00
7934 MILLER 10 1300.00
14 rows selected
注：以上示例为merge语法中matched 和not matched clauses 同时使用，同时merge语法中也可以只插入不更新（即not mateched clause），也能只更新不插入（即matched then）。
在oracle10g也可用之delete语句，如下示例
SQL> merge into tab_emp a
2 using emp b
3 on (a.empno = b.empno)
4 when MATCHED then
5 update set a.ename = b.ename,
6 a.deptno = b.deptno,
7 a.sal = b.sal
8 delete where (b.deptno != 20);
Done
SQL> commit;
Commit complete
SQL> select * from tab_emp;
EMPNO ENAME DEPTNO SAL
----- ---------- ------ ---------
7369 SMITH 20 800.00
7566 JONES 20 2975.00
7788 SCOTT 20 3000.00
7876 ADAMS 20 1100.00
7902 FORD 20 3000.00
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20948385/viewspace-666456/ ，如需转载，请注明出处，否则将追究法律责任。
