MERGE
MERGE是什么,如何使用呢?先看一个简单的需求如下:
从T1表更新数据到T2表中,如果T2表的NAME 在T1表中已存在,就将MONEY累加,如果不存在,将T1表的记录插入到T2表中。
DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 VALUES ('B',20);
DROP TABLE T2;
CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T2 VALUES ('A',30);
INSERT INTO T2 VALUES ('C',20);
COMMIT;
大家知道,一般逻辑思路,该需要至少要UPDATE和INSERT两条SQL才能完成,如考虑在PL/SQL中用纯编程语言思路实现,则必须要考虑判断的逻辑,这样显得更麻烦了。而MERGE语句可直接用单条SQL简洁明快的实现了此业务逻辑,具体如下:
MERGE INTO T2
USING T1
ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
WHEN NOT MATCHED THEN
INSERT
VALUES (https://www.doczj.com/doc/316807974.html,,T1.MONEY);
此外MERGE语句同样可以分析执行计划,具体如下:
SQL> explain plan for
2 MERGE INTO T2
3 USING T1
4 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
5 WHEN MATCHED THEN
6 UPDATE
7 SET T2.MONEY=T1.MONEY+T2.MONEY
8 WHEN NOT MATCHED THEN
9 INSERT
10 VALUES (https://www.doczj.com/doc/316807974.html,,T1.MONEY);
Explained
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2414655244
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 2 | 152 | 7 (15)| 00:00:01 | | 1 | MERGE | T2 | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 124 | 7 (15) | 00:00:01 |
| 4 | TABLE ACCESS FUL L | T1 | 2 | 50 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | T2 | 2 | 74 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."NAME"="T2"."NAME"(+))
Note
-----
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
- dynamic sampling used for this statement
21 ows selected
1.2 MERGE语法简介
语法如下:
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;
1.3 Oracle10g中MERGE的完善
在Oracle10g以后,Oracle的MERGE发生了改变
1.4 UPDATE和INSERT动作可只出现其一可选择仅UPDATE目标表
SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY;
Done
也可选择仅仅INSERT目标表而不做任何UPDATE动作SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN NOT MATCHED THEN
5 INSERT
6 VALUES (https://www.doczj.com/doc/316807974.html,,T1.MONEY);
Done
注:Oracle9i必须同时出现INSERT和UPDATE操作。
1.5 可对MERGE语句加where条件
SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY
7 WHERE https://www.doczj.com/doc/316807974.html,='A' ---此处表示对MERGE的条件进行过滤
8 ;
Done
1.6 可用DELETE子句清除行(必须同时满足on后的条件和delete where后的条件才有效)
在这种情况下,首先是要先满足https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,的记录,如果https://www.doczj.com/doc/316807974.html,=’A’并不满足https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,过滤出的记录集,那这个DELETE是不会生效的,在满足的条件下,可以删除目标表的记录。
MERGE INTO T2
USING T1
ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
DELETE WHERE (https://www.doczj.com/doc/316807974.html, = 'A')
;
1.7可采用无条件方式Insert
方法很简单,在语法ON关键字处写上恒不等条件(如1=2)后,MATCHED语句的INSERT 就变为无条件INSERT了,同于INSERT...SELECT的写法,具体如下
SQL> MERGE INTO T2
2 USING T1
3 ON (1=2)
4 WHEN NOT MATCHED THEN
5 INSERT
6 VALUES (https://www.doczj.com/doc/316807974.html,,T1.MONEY);
Done
2 MERGE误区探索
2.3 无法在源表中获得一组稳定的行(目标表的一条记录对应源表的多条记录)
MERGE INTO T2 USING T1 ON...的MERGE表达式中,如果一条T2记录被连接到多条T1记录,就产生了ORA-30926错误。构造T1,T2表进行试验如下,此次T1表中增加了('A',30)的记录,如下:
DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 VALUES ('A',30);
INSERT INTO T1 VALUES ('B',20);
DROP TABLE T2;
CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T2 VALUES ('A',30);
INSERT INTO T2 VALUES ('C',20);
COMMIT
此时继续执行如下
SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY
7 ;
ORA-30926: 无法在源表中获得一组稳定的行
Oracle中的merge语句应该保证on中的条件的唯一性,https://www.doczj.com/doc/316807974.html,=’A’时,T2表记录对应到了T1表的两条记录,所以就出错了。
解决方法很简单,可对T1表和T2表的关联字段建主键,这样基本上就不可能出现这样的问题,而且一般而言,MERGE语句的关联字段互相有主键,MERGE的效率将比较高!
或者是将T1表的ID列做一个聚合,这样归并成单条,也能避免此类错误。如:
SQL> MERGE INTO T2
2 USING (SELECT NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY
Done
但是这样的改造需要注意,因为有可能改变了最终的需求。此外需要引起注意的是,MERGE INTO T2 USING T1 ON...的MERGE表达式中,如果反过来,一条T1记录被连接到多条T2记录,是可以导致多条T2记录都被更新而不会出错!继续构造T1,T2表进行试验如下,此次是在T2表中增加了('A',40)的记录,如下:
DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 VALUES ('B',20);
DROP TABLE T2;
CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T2 VALUES ('A',30);
INSERT INTO T2 VALUES ('A',40);
INSERT INTO T2 VALUES ('C',20);
COMMIT
此时继续执行如下,发现执行可以成功并没有报无法在源表中获得一组稳定的行的
ORA-30926错误
SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY
Done
SQL> COMMIT;
Commit complete
查看T2表,发现T2表中NAME=A的2条记录都被更新了SQL> SELECT * FROM T2;
NAME MONEY
-------------------- ----------------------------------
A 40
A 50
C 20
2.4 DELETE子句的WHERE顺序必须最后SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY
7 DELETE WHERE (https://www.doczj.com/doc/316807974.html, = 'A')
8 WHERE https://www.doczj.com/doc/316807974.html,='A';
/
ORA-00933: SQL 命令未正确结束
改为如下即可
SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY
7 WHERE https://www.doczj.com/doc/316807974.html,='A'
8 DELETE WHERE (https://www.doczj.com/doc/316807974.html, = 'A')
9 ;
Done
注:只要是MERGE语句,UPDATE和DELETE两者必须要出现其一,所以上面的脚本是不能省略UPDATE而只做DELETE的。另外WHERE (https://www.doczj.com/doc/316807974.html, = 'A')的括号可以省略。
2.5 DELETE 子句只可以删除目标表,而无法删除源表
这里需要引起注意,无论DELETE WHERE (https://www.doczj.com/doc/316807974.html, = 'A' )这个写法的T2是否改写为T1,效果都一样,都是对目标表进行删除!
SQL> SELECT * FROM T1;
NAME MONEY
-------------------- ------------------------------
A 10
B 20
SQL> SELECT * FROM T2;
NAME MONEY
-------------------- --------------------------------
A 30
C 20
SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY
7 DELETE WHERE (https://www.doczj.com/doc/316807974.html, = 'A' )
8 ;
Done
SQL> SELECT * FROM T1;
NAME MONEY
-------------------------------------------------------
A 10
B 20
SQL> SELECT * FROM T2;
NAME MONEY
-------------------- ---------------------------------
C 20
可以看出目标表的A记录被删除了,但是如果把DELETE WHERE (https://www.doczj.com/doc/316807974.html, = 'A' )修改为DELETE WHERE (https://www.doczj.com/doc/316807974.html, = 'A' ),是否就会把源表的T1记录给删除了呢?试验如下:
Rollback complete
SQL> MERGE INTO T2
2 USING T1
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=T1.MONEY+T2.MONEY
7 DELETE WHERE (https://www.doczj.com/doc/316807974.html, = 'A' )
8 ;
Done
SQL> SELECT * FROM T1;
NAME MONEY
-------------------- ---------------------------------
A 10
B 20
SQL> SELECT * FROM T2;
NAME MONEY
-------------------- --------------------------------
C 20
发现其实T1源表的记录根本还是保留着,还只是目标表被删除了。
2.6 更新同一张表的数据,需担心USING的空值
SQL> SELECT * FROM T2;
NAME MONEY
-------------------- ---------------------------------
A 30
C 20
需求为对T2表进行自我更新,如果在T2表中发现NAME=’D’的记录,就将该记录的MONEY 字段更新为100,如果NAME=D的记录不存在,则自动增加NAME=’D’的记录。
根据语法完成如下代码
SQL> MERGE INTO T2
2 USING (SELECT * FROM t2 WHERE NAME='D') T
3 ON (https://www.doczj.com/doc/316807974.html,=https://www.doczj.com/doc/316807974.html,)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=100
7 WHEN NOT MATCHED THEN
8 INSERT
9 VALUES ('D',200)
10 ;
Done
但是查询发现,本来T表应该因为NAME=D不存在而要增加记录,但是实际却根本无变化。SQL> SELECT * FROM T2;
NAME MONEY
-------------------------------------------------------
A 30
C 20
原因是USING后面必须包含要更新或插入的行。而第一个USING (SELECT * FROM t2 WHERE NAME='D') T 根本没有这一行,可改造如下巧妙实现需求:
SQL> MERGE INTO T2
2USING (SELECT COUNT(*) CNT FROM t2 WHERE NAME='D') T
3 ON (https://www.doczj.com/doc/316807974.html,T<>0)
4 WHEN MATCHED THEN
5 UPDATE
6 SET T2.MONEY=100
7 WHEN NOT MATCHED THEN
8 INSERT
9 VALUES ('D',100)
10 ;
Done
SQL> SELECT * FROM T2;
NAME MONEY
-------------------- -----------------------------------
A 30
C 20
D 100
3 MERGE的巧妙运用
案例1:
需求为:将如下TEST记录的ID=1的NAME改为ID=2的NAME的值,把ID=2的NAME 改为ID=1的NAME的值。
drop table test;
create table test (id number,name varchar2(20));
insert into test values (1,'a');
insert into test values (2,'b');
COMMIT;
SQL> SELECT * FROM test;
ID NAME
---------- --------------------
1 a
2 b
如果执行如下:
UPDATE TEST SET NAME =(SELECT NAME FROM TEST WHERE ID=2) WHERE ID=1;
此时ID=1的NAME值已改变了,就不可能用如下来更新了
UPDATE TEST SET NAME =(SELECT NAME FROM TEST WHERE ID=1) WHERE ID=2;
如果是过程就很简单了,可以把原先的值先存储起来。但是是否单条SQL一定不行呢?
其实单条SQL是可以解决的,可考虑灵活利用MERGE特性!思路可考虑构造出一个虚拟表T,然后再根据此虚拟T表和真实的TEST表进行MERGE更新,就方便快捷的完成了。构造的虚拟表方法类似如下:
SQL> SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL
2 UNION ALL
3 SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL
4 ;
ID NAME
---------- --------------------
1 b
2 a
有了此思路,结合前面所学的MERGE知识,可以通过如下简洁优雅定的代码完成更新。SQL> MERGE INTO TEST
2 USING (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL
3 UNION ALL
4 SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL
5 ) t
6 ON (test.id = t.id)
7 WHEN MATCHED THEN UPDATE set https://www.doczj.com/doc/316807974.html, = https://www.doczj.com/doc/316807974.html,
8 ;
Done
SQL> SELECT * FROM test;
ID NAME
---------- --------------------
1 b
2 a
注:如果是9I固定需要INSERT,所以需要随便加上如下内容
WHEN NOT MATCHED THEN
INSERT VALUES (1,'a')
本案例用的是MERGE的方法,当然,其中的构造虚拟表也是一个非常重要的思路,如果只是查询出改变后的结果而不是真实的进行更新,就可以不采用MERGE,直接可以采用如下方式取出结果
SQL> rollback;
Rollback complete
SQL>
SQL> WITH T AS
2 (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL
3 UNION ALL
4 SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL
5 )
6 SELECT test.id,https://www.doczj.com/doc/316807974.html, FROM test ,t
7 WHERE test.id=t.id;
ID NAME
---------- --------------------
1 b
2 a
在这里了解一下with as的语法:
c reate table t (x number(10), y number(10));
insert into t values (1,110);
insert into t values (2,120);
insert into t values (2,80);
insert into t values (3,150);
insert into t values (3,30);
insert into t values (3,60);
commit;
select * from t;
需求描述
按照x列分组后统计y列的总值,最终目标是选出比y列总值的三分之一大的那些分组统计信息
使用子查询方式实现
最容易想到的方法
SELECT x, SUM (y) AS total_y
FROM t
GROUP BY x
HAVING SUM (y) > (SELECT SUM(y) / 3 FROM t)
ORDER BY total_y
WITH Clause方法闪亮登场
WITH secooler_sum AS (SELECT x, SUM (y) total_y
FROM t
GROUP BY x)
SELECT x, total_y
FROM secooler_sum
WHERE total_y > (SELECT SUM (total_y) / 3 FROM secooler_sum)
ORDER BY total_y
查询语句不是以select开始的,而是以“WITH”关键字开头
可认为在真正进行查询之前预先构造了一个临时表secooler_sum,之后便可多次使用它做进一步的分析和处理
WITH Clause方法的优点
增加了SQL的易读性,如果构造了多个子查询,结构会更清晰;
更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标
知其所以然
为什么WITH Clause方法会提高效率?通过查看上面两种方法的执行计划便可略知一二
1)使用子查询的执行计划
set autot trace exp
第一种使用子查询的方法T表被扫描了两次,而使用WITH Clause方法,T表仅被扫描一次
这也是为什么在大型数据仓库系统中推荐使用WITH Clause方法进行查询统计的原因,这样可以大大的提高数据分析和查询的效率
另外,观察WITH Clause方法执行计划,其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。
案例2
通过MERGE可以得到一个非常有用的思想,就是如下:只要能查出更新后的结果集,就可利用该结果集来更新原表记录,即MERGE+ROWID方式。感谢NEWKID给予的指点,他精于使用此类方法,下文案例3中的复杂MERGE更新例子即来自NEWKID的精彩脚本。
本案例2来源于案例1的延伸,改变了案例1的处理思路,不再采用构造虚拟表T来关联TEST表的方式,而是直接把真实结果用SELECT的方式取出,然后利用这个结果集更新回原表中。
SQL> merge into test using
2 (
3 WITH T AS
4 (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL
5 UNION ALL
6 SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL
7 )
8 SELECT test.id,test.rowid as rn ,https://www.doczj.com/doc/316807974.html, FROM test ,t