MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。
下面看个具体的例子：
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE TABLE T1 AS
2 SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
3 FROM DBA_TABLES;
表已创建。
SQL> MERGE INTO T1 USING T
2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);
6165 行已合并。
SQL> SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
2 MINUS
3 SELECT * FROM T1;
未选定行
MERGE语法其实很简单，下面稍微修改一下例子。
SQL> DROP TABLE T;
表已丢弃。
SQL> DROP TABLE T1;
表已丢弃。
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE TABLE T1 AS SELECT ROWNUM ID, OWNER, TABLE_NAME FROM DBA_TABLES;
表已创建。
SQL> MERGE INTO T1 USING T
2 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
3 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
4 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
MERGE INTO T1 USING T
*
ERROR 位于第 1 行:
ORA-30926: 无法在源表中获得一组稳定的行
这个错误是使用MERGE最常见的错误，造成这个错误的原因是由于通过连接条件得到的T的记录不唯一。最简单的解决方法类似：
SQL> MERGE INTO T1
2 USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
3 ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
4 WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
5 WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
5775 行已合并。
另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错，详细信息可以参考：http://blog.itpub.net/post/468/14844

===============================================================

ref: http://tomszrp.itpub.net/post/11835/263865

在Oracle 10g之前，merge语句支持匹配更新和不匹配插入2种简单的用法，在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。

参考Oracle 的SQL Reference，大家可以看到Merge Statement的语法如下：
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;

下面我在windows xp 下10.2.0.1版本上做一个测试看看

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
一、创建测试用的表
SQL> create table subs(msid number(9),
2                    ms_type char(1),
3                    areacode number(3)
4                    );
表已创建。
SQL> create table acct(msid number(9),
2                    bill_month number(6),
3                    areacode   number(3),
4                    fee        number(8,2) default 0.00);
表已创建。
SQL>
SQL> insert into subs values(905310001,0,531);
已创建 1 行。
SQL> insert into subs values(905320001,1,532);
已创建 1 行。
SQL> insert into subs values(905330001,2,533);
已创建 1 行。
SQL> commit;
提交完成。
SQL>
　
二、下面先演示一下merge的基本功能
1) matched 和not matched clauses 同时使用
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode);
2) 只有not matched clause,也就是只插入不更新
merge into acct a
using subs b on (a.msid=b.msid)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode);
3) 只有matched clause, 也就是只更新不插入
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as study
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
905320001 1            532
905330001 2            533
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode
5     when NOT MATCHED then
6          insert(msid,bill_month,areacode)
7          values(b.msid,'200702',b.areacode);
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905320001     200702      532       0.00
905330001     200702      533       0.00
905310001     200702      531       0.00
SQL> insert into subs values(905340001,3,534);
1 row inserted
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905340001 3            534
905310001 0            531
905320001 1            532
905330001 2            533
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when NOT MATCHED then
4          insert(msid,bill_month,areacode)
5          values(b.msid,'200702',b.areacode);
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905320001     200702      532       0.00
905330001     200702      533       0.00
905310001     200702      531       0.00
905340001     200702      534       0.00
SQL> update subs set areacode=999;
4 rows updated
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905340001 3            999
905310001 0            999
905320001 1            999
905330001 2            999
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905320001     200702      532       0.00
905330001     200702      533       0.00
905310001     200702      531       0.00
905340001     200702      534       0.00
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode;
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905320001     200702      999       0.00
905330001     200702      999       0.00
905310001     200702      999       0.00
905340001     200702      999       0.00
SQL>
　
三、10g中增强一：条件操作
1) matched 和not matched clauses 同时使用
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
2) 只有not matched clause,也就是只插入不更新
merge into acct a
using subs b on (a.msid=b.msid)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
3) 只有matched clause, 也就是只更新不插入
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as study
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
905320001 1            532
905330001 2            533
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode
5          where b.ms_type=0
6     when NOT MATCHED then
7          insert(msid,bill_month,areacode)
8          values(b.msid,'200702',b.areacode)
9          where b.ms_type=0;
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905310001     200702      531       0.00
SQL> insert into subs values(905360001,0,536);
1 row inserted
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905360001 0            536
905310001 0            531
905320001 1            532
905330001 2            533
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when NOT MATCHED then
4          insert(msid,bill_month,areacode)
5          values(b.msid,'200702',b.areacode)
6          where b.ms_type=0;
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905310001     200702      531       0.00
905360001     200702      536       0.00
SQL> update subs set areacode=888 where ms_type=0;
2 rows updated
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905360001 0            888
905310001 0            888
905320001 1            532
905330001 2            533
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905310001     200702      531       0.00
905360001     200702      536       0.00
SQL>
SQL> merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode
5          where b.ms_type=0;
Done
SQL> select * from acct;
MSID BILL_MONTH AREACODE        FEE
---------- ---------- -------- ----------
905310001     200702      888       0.00
905360001     200702      888       0.00
SQL>
四、10g中增强二：删除操作
An optional DELETE WHERE clause can be used to clean up after a
merge operation. Only those rows which match both the ON clause
and the DELETE WHERE clause are deleted.
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
delete where (b.ms_type!=0);
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
905320001 1            532
905330001 2            533
SQL> select * from acct;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
905320001 1            532
905330001 2            533
SQL>
SQL>  merge into acct a
2       using subs b on (a.msid=b.msid)
3     when MATCHED then
4          update set a.areacode=b.areacode
5          delete where (b.ms_type!=0);
Done
SQL> select * from acct;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0            531
SQL>
更为详尽的语法，请参考Oracle SQL Reference手册！

re: MERGE语法 [回复]

merge 语法中，T1可以不是oracle 实际存在的表吗 ？
比如
TYPE my_type_array IS TABLE OF
my_type INDEX BY BINARY_INTEGER;

T1 my_type_array

谢谢

大狗 评论于：2006.03.21 21:10

re: MERGE语法 [回复]

T1必须是oracle实际存在的表！

xsb 评论于：2006.03.22 09:02

re: MERGE语法 [回复]

我也查了相关资料，的确是要实际存在的表，呵呵，谢谢你的分享精神！

大狗 评论于：2006.04.02 13:27

re: MERGE语法 [回复]

-- Created on 2006-3-22 by xsb
declare
--T1必须是oracle实际存在的表或视图！
--create table t (c1 number,c2 number);
--insert into t values (1,2);
type TYPE_REC_RESULT is record(
C1 number,
C2 number);
type TYPE_TAB_RESULT is table of TYPE_REC_RESULT index by binary_integer;
T1 TYPE_TAB_RESULT;
begin
merge into T1
using T
on (T1.C1 = T.C1)
when matched then
update
set T1.C2 = T.C2
when not matched then
insert
values
(T.C1, T.C2);
end;
/
PL/SQL: ORA-00942: 表或视图不存在