当前位置:文档之家› 关于oracle中的MERGE

关于oracle中的MERGE

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

相关主题
相关文档 最新文档