作者：夜笙_ciel | 来源：互联网 | 2014-07-11 17:33
oraclemerge从9i到10g的增强MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于I...SyntaxHighlig
oracle merge从9i到10g的增强
MERGE语句是
Oracle9i新增的语法，用来合并UPDATE和INSERT语句。连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。
下面看个具体例子：
[sql]
hr@ORCL> select * from p;
www.2cto.com
ID NAME
---------- ----------
1 d
3 e
8 f
hr@ORCL> select * from t;
ID NAME
---------- ----------
1 a
2 b
3 c
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 ;
www.2cto.com
3 rows merged.
hr@ORCL> select * from t;
ID NAME
---------- ----------
1 d
2 b
3 e
8 f
hr@ORCL> commit;
Commit complete.
下面稍微修改一下例子：
[sql]
hr@ORCL> insert into p values(1,&＃39;h&＃39;);
1 row created.
hr@ORCL> commit;
Commit complete.
www.2cto.com
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值是唯一的。
还有一个错误也是比较常见的：
[sql]
hr@ORCL> create table mm (id number,name varchar2(10));
www.2cto.com
Table created.
hr@ORCL> create table mn (id number,name varchar2(10));
Table created.
hr@ORCL> insert into mm values(1,&＃39;A&＃39;);
1 row created.
hr@ORCL> insert into mn values(1,&＃39;B&＃39;);
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)
* www.2cto.com
ERROR at line 2:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "MN"."ID"
10g增强一：where子句
下面看一个小例子：
[sql]
hr@ORCL> select * from p;
ID NAME
---------- ----------
1 d
2 f
7 g
hr@ORCL> select * from t;
ID NAME
---------- ----------
1 a
2 b
3 c
www.2cto.com
hr@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子句
[sql]
hr@ORCL> select * from p;
ID NAME
---------- ----------
1 d
2 f
7 g
www.2cto.com
hr@ORCL> select * from t;
ID NAME
---------- ----------
1 a
2 f
3 c
www.2cto.com
hr@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
作者 linwaterbin
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有