java merge_MERGE用法

最新推荐文章于 2023-04-26 17:01:49 发布

石昊悦

最新推荐文章于 2023-04-26 17:01:49 发布

阅读量2.1k

点赞数

文章标签： java merge

本文链接：https://blog.csdn.net/weixin_28665677/article/details/114200348

版权

MERGEMERGE是什么，如何使用呢？先看一个简单的需求如下:从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);INSERMERGE

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 (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE

SET T2.MONEY=T1.MONEY+T2.MONEY

WHEN NOT MATCHED THEN

INSERT

VALUES (T1.NAME,T1.MONEY);

此外MERGE语句同样可以分析执行计划，具体如下：

SQL> explain plan for

2 MERGE INTO T2

3 USING T1

4 ON (T1.NAME=T2.NAME)

5 WHEN MATCHED THEN

6 UPDATE

7 SET T2.MONEY=T1.MONEY+T2.MONEY

8 WHEN NOT MATCHED THEN

9 INSERT

10 VALUES (T1.NAME,T1.MONEY);

Explained

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------

Plan hash value: 2414655244

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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_clauseWHEN 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 (T1.NAME=T2.NAME)

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 (T1.NAME=T2.NAME)

4 WHEN NOT MATCHED THEN

5 INSERT

6 VALUES (T1.NAME,T1.MONEY);

Done

注：Oracle9i必须同时出现INSERT和UPDATE操作。

1.5 可对MERGE语句加where条件

SQL> MERGE INTO T2

2 USING T1

3 ON (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN

5 UPDATE

6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 WHERE T1.NAME='A' ---此处表示对MERGE的条件进行过滤

8 ;

Done

1.6 可用DELETE子句清除行(必须同时满足on后的条件和delete where后的条件才有效)

在这种情况下，首先是要先满足T1.NAME=T2.NAME的记录，如果T2.NAME=’A’并不满足T1.NAME=T2.NAME过滤出的记录集，那这个DELETE是不会生效的，在满足的条件下，可以删除目标表的记录。

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE

SET T2.MONEY=T1.MONEY+T2.MONEY

DELETE WHERE (T2.NAME = '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 (T1.NAME,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 (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN

5 UPDATE

6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 ;

ORA-30926: 无法在源表中获得一组稳定的行

Oracle中的merge语句应该保证on中的条件的唯一性，T1.NAME=’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 (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN

5 UPDATE

6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 ;

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 (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN

5 UPDATE

6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 ;

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 (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN

5 UPDATE

6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 DELETE WHERE (T2.NAME = 'A')

8 WHERE T1.NAME='A';

ORA-00933: SQL 命令未正确结束

改为如下即可

SQL> MERGE INTO T2

2 USING T1

3 ON (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN

5 UPDATE

6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 WHERE T1.NAME='A'

8 DELETE WHERE (T2.NAME = 'A')

9 ;

Done

注：只要是MERGE语句，UPDATE和DELETE两者必须要出现其一，所以上面的脚本是不能省略UPDATE而只做DELETE的。另外WHERE (T2.NAME = 'A')的括号可以省略。

2.5 DELETE 子句只可以删除目标表，而无法删除源表

这里需要引起注意，无论DELETE WHERE (T2.NAME = '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 (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN

5 UPDATE

6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 DELETE WHERE (T2.NAME = 'A' )

8 ;

Done

SQL> SELECT * FROM T1;

NAME MONEY

-------------------------------------------------------

A 10

B 20

SQL> SELECT * FROM T2;

NAME MONEY

-------------------- ---------------------------------

C 20

可以看出目标表的A记录被删除了，但是如果把DELETE WHERE (T2.NAME = 'A' )修改为DELETE WHERE (T1.NAME = 'A' )，是否就会把源表的T1记录给删除了呢？试验如下：

Rollback complete

SQL> MERGE INTO T2

2 USING T1

3 ON (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN

5 UPDATE

6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 DELETE WHERE (T2.NAME = '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 (T.NAME=T2.NAME)

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

2 USING (SELECT COUNT(*) CNT FROM t2 WHERE NAME='D') T

3 ON (T.CNT<>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 TEST.name = t.name

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,t.name FROM test ,t

7 WHERE test.id=t.id;

ID NAME

---------- --------------------

1 b

2 a

在这里了解一下with as的语法：

create 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 ,t.name FROM test ,t

9 WHERE test.id=t.id

10 ) n

11 on(test.rowid=n.rn)

12 when matched then update

13 set test.name=n.name;

SQL> SELECT * FROM test;

ID NAME

---------- --------------------

1 a

2 b

注：直接UPDATE一个子查询的写法也可行，但是却有很多限制，稍微复杂的查询都易出错。此时用MERGE是最好的办法，结合ROWID的方式，可快速准确的利用一个已查询出的结果集来更新自己，是一个非常好的思路的扩展，希望对大家有借鉴。

案例3

在文章的最后，举一个USING里面有复杂的连接、聚合、分析函数的综合性例子来加深读者的印象，从而更深入的理解MERGE的强大功能！脚本选自NEWKID在答网友提问的一次精彩回复，提问需求如下：

declarecursor c1 isSELECT art_no,stock

FROM tb_fin_art_stockWHERE run_date=to_date('&日期','yyyymmdd')-1 and art_no in (158756);t_art tb_fin_art_stock.art_no%type;t_stock tb_fin_art_STOCK.stock%type;beginopen c1;loop fetch c1 into t_art,t_stock; exit when c1%notfound; update tb_fin_art_stock

set stock=t_stock+gor_qty+return_qty-sale_qty+stock_corr+DEL_CORR WHERE run_date=to_date('&日期','yyyymmdd') and art_no=t_art; commit;end loop;close c1;end; 以上代码，我想更新tb_fin_art_stock 这个表中某个货号某一天往后所有记录的 stock(库存)字段的值，每天的stock是根据前一天的stock字段的值加进货减销售得出来的。现在只能一天一天更新。我想问的是如何能输入日期范围，比如10号到20号的记录，根据9号更改10号，根据10号再改11号，。。。。以此类推，一次就更新了。

MERGE精彩解决方案如下，有兴趣的读者自行研究，相信必有收获！

MERGE INTO tb_fin_art_stock tUSING (SELECT t.ROWID rid ,t2.stock+SUM(gor_qty+return_qty-sale_qty+stock_corr+DEL_CORR) OVER(PARTITION BY art_no ORDER BY run_date) AS stock FROM tb_fin_art_stock t ,(SELECT art_no,stock FROM tb_fin_art_stock

WHERE run_date = lv_start_date-1) t2 WHERE t.run_date BETWEEN lv_start_date AND lv_end_date AND t.art_no = t2.art_no ) nUSING (t.ROWID = n.rid)WHEN MATCHED THEN UPDATE SET t.stock = n.stock;

T INTO T2 VALUES ('C',20);COMMIT;大家知道，一般逻辑思路，该需要至少要UPDATE和INSERT两条SQL才能完成，如考虑在PL/SQL中用纯编程语言思路实现，则必须要考虑判断的逻辑，这样显得更麻烦了。而MERGE语句可直接用单条SQL简洁明快的实现了此业务逻辑，具体如下：MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEYWHEN NOT MATCHED

THENINSERTVALUES (T1.NAME,T1.MONEY); 此外MERGE语句同样可以分析执行计划，具体如下：SQL> explain plan for 2 MERGE INTO T2 3 USING T1 4 ON (T1.NAME=T2.NAME) 5 WHEN MATCHED THEN 6 UPDATE 7 SET T2.MONEY=T1.MONEY+T2.MONEY 8 WHEN NOT MATCHED THEN 9 INSERT 10 VALUES

(T1.NAME,T1.MONEY);ExplainedSQL> 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 statement21 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_clauseWHEN 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 (T1.NAME=T2.NAME) 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 (T1.NAME=T2.NAME) 4 WHEN NOT MATCHED THEN 5 INSERT 6 VALUES (T1.NAME,T1.MONEY);Done

注：Oracle9i必须同时出现INSERT和UPDATE操作。1.5 可对MERGE语句加where条件SQL> MERGE INTO T2 2 USING T1 3 ON (T1.NAME=T2.NAME) 4 WHEN MATCHED THEN 5 UPDATE 6 SET T2.MONEY=T1.MONEY+T2.MONEY 7 WHERE T1.NAME='A' ---此处表示对MERGE的条件进行过滤 8 ;Done 1.6 可用DELETE子句清除行(必须同时满足on后的条件和delete

where后的条件才有效)在这种情况下，首先是要先满足T1.NAME=T2.NAME的记录，如果T2.NAME=’A’并不满足T1.NAME=T2.NAME过滤出的记录集，那这个DELETE是不会生效的，在满足的条件下，可以删除目标表的记录。MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEYDELETE WHERE (T2.NAME = '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 (T1.NAME,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 (T1.NAME=T2.NAME) 4 WHEN MATCHED THEN 5 UPDATE 6 SET T2.MONEY=T1.MONEY+T2.MONEY 7 ;ORA-30926: 无法在源表中获得一组稳定的行 Oracle中的merge语句应该保证on中的条件的唯一性，T1.NAME=’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 (T1.NAME=T2.NAME) 4 WHEN MATCHED THEN 5 UPDATE 6 SET T2.MONEY=T1.MONEY+T2.MONEY 7 ;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 (T1.NAME=T2.NAME) 4 WHEN MATCHED THEN 5 UPDATE 6 SET T2.MONEY=T1.MONEY+T2.MONEY 7 ;DoneSQL> COMMIT;Commit complete

查看T2表，发现T2表中NAME=A的2条记录都被更新了SQL> SELECT * FROM T2;NAME MONEY-------------------- ----------------------------------A 40A 50C 20 2.4 DELETE子句的WHERE顺序必须最后SQL>

MERGE INTO T2 2 USING T1 3 ON (T1.NAME=T2.NAME) 4 WHEN MATCHED THEN 5 UPDATE 6 SET T2.MONEY=T1.MONEY+T2.MONEY 7 DELETE WHERE (T2.NAME = 'A') 8 WHERE T1.NAME='A';/ORA-00933: SQL 命令未正确结束改为如下即可SQL> MERGE INTO T2 2 USING T1 3 ON (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN 5 UPDATE 6 SET T2.MONEY=T1.MONEY+T2.MONEY 7 WHERE T1.NAME='A' 8 DELETE WHERE (T2.NAME = 'A') 9 ;Done 注：只要是MERGE语句，UPDATE和DELETE两者必须要出现其一，所以上面的脚本是不能省略UPDATE而只做DELETE的。另外WHERE (T2.NAME = 'A')的括号可以省略。2.5 DELETE 子句只可以删除目标表，而无法删除源表

这里需要引起注意，无论DELETE WHERE (T2.NAME = 'A' )这个写法的T2是否改写为T1，效果都一样，都是对目标表进行删除！SQL> SELECT * FROM T1;NAME MONEY-------------------- ------------------------------A 10B 20SQL> SELECT * FROM

T2;NAME MONEY-------------------- --------------------------------A 30C 20SQL> MERGE INTO T2 2 USING T1 3 ON (T1.NAME=T2.NAME) 4 WHEN MATCHED THEN 5 UPDATE 6 SET T2.MONEY=T1.MONEY+T2.MONEY

7 DELETE WHERE (T2.NAME = 'A' ) 8 ;DoneSQL> SELECT * FROM T1;NAME MONEY-------------------------------------------------------A 10B 20SQL> SELECT * FROM T2;NAME

MONEY-------------------- ---------------------------------C 20可以看出目标表的A记录被删除了，但是如果把DELETE WHERE (T2.NAME = 'A' )修改为DELETE WHERE (T1.NAME = 'A' )，是否就会把源表的T1记录给删除了呢？试验如下：Rollback completeSQL> MERGE INTO T2 2 USING T1 3 ON (T1.NAME=T2.NAME)

4 WHEN MATCHED THEN 5 UPDATE 6 SET T2.MONEY=T1.MONEY+T2.MONEY 7 DELETE WHERE (T2.NAME = 'A' ) 8 ;DoneSQL> SELECT * FROM T1;NAME MONEY-------------------- ---------------------------------A 10B

20SQL> SELECT * FROM T2;NAME MONEY-------------------- --------------------------------C 20 发现其实T1源表的记录根本还是保留着，还只是目标表被删除了。2.6 更新同一张表的数据,需担心USING的空值SQL> SELECT * FROM T2;NAME MONEY--------------------

---------------------------------A 30C 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 (T.NAME=T2.NAME) 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

30C 20原因是USING后面必须包含要更新或插入的行。而第一个USING (SELECT * FROM t2 WHERE NAME='D') T 根本没有这一行，可改造如下巧妙实现需求:SQL> MERGE INTO T2 2 USING (SELECT COUNT(*) CNT FROM t2 WHERE NAME='D') T 3 ON (T.CNT<>0) 4 WHEN MATCHED THEN 5 UPDATE 6 SET

T2.MONEY=100 7 WHEN NOT MATCHED THEN 8 INSERT 9 VALUES ('D',100) 10 ;DoneSQL> SELECT * FROM T2;NAME MONEY-------------------- -----------------------------------A 30C 20D

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 TEST.name

= t.name 8 ;DoneSQL> SELECT * FROM test; ID NAME---------- -------------------- 1 b 2 a注：如果是9I固定需要INSERT，所以需要随便加上如下内容WHEN NOT MATCHED THENINSERT VALUES (1,'a') 本案例用的是MERGE的方法，当然，其中的构造虚拟表也是一个非常重要的思路，如果只是查询出改变后的结果而不是真实的进行更新，就可以不采用MERGE，直接可以采用如下方式取出结果SQL>

rollback;Rollback completeSQL>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,t.name FROM test ,t 7 WHERE

test.id=t.id; ID NAME---------- -------------------- 1 b 2 a在这里了解一下with as的语法：create 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 ,t.name

FROM test ,t 9 WHERE test.id=t.id10 ) n 11 on(test.rowid=n.rn) 12 when matched then update 13 set test.name=n.name;SQL> SELECT * FROM test; ID NAME---------- -------------------- 1 a 2 b 注：直接UPDATE一个子查询的写法也可行，但是却有很多限制，稍微复杂的查询都易出错。此时用MERGE是最好的办法，结合ROWID的方式，可快速准确的利用一个已查询出的结果集来更新自己，是一个非常好的思路的扩展，希望对大家有借鉴。案例3在文章的最后，举一个USING里面有复杂的连接、聚合、分析函数的综合性例子来加深读者的印象，从而更深入的理解MERGE的强大功能！脚本选自NEWKID在答网友提问的一次精彩回复，提问需求如下：declarecursor

c1 isSELECT art_no,stockFROM tb_fin_art_stockWHERE run_date=to_date('&日期','yyyymmdd')-1 and art_no in (158756);t_art tb_fin_art_stock.art_no%type;t_stock tb_fin_art_STOCK.stock%type;beginopen c1;loop fetch c1 into t_art,t_stock; exit when

c1%notfound; update tb_fin_art_stock set stock=t_stock+gor_qty+return_qty-sale_qty+stock_corr+DEL_CORR WHERE run_date=to_date('&日期','yyyymmdd') and art_no=t_art; commit;end loop;close c1;end; 以上代码，我想更新tb_fin_art_stock

这个表中某个货号某一天往后所有记录的 stock(库存)字段的值，每天的stock是根据前一天的stock字段的值加进货减销售得出来的。现在只能一天一天更新。我想问的是如何能输入日期范围，比如10号到20号的记录，根据9号更改10号，根据10号再改11号，。。。。以此类推，一次就更新了。MERGE精彩解决方案如下，有兴趣的读者自行研究，相信必有收获！MERGE INTO tb_fin_art_stock tUSING (SELECT t.ROWID rid ,t2.stock+SUM(gor_qty+return_qty-sale_qty+stock_corr+DEL_CORR)

OVER(PARTITION BY art_no ORDER BY run_date) AS stock FROM tb_fin_art_stock t ,(SELECT art_no,stock FROM tb_fin_art_stock WHERE run_date = lv_start_date-1) t2 WHERE t.run_date BETWEEN lv_start_date AND lv_end_date AND

t.art_no = t2.art_no ) nUSING (t.ROWID = n.rid)WHEN MATCHED THEN UPDATE SET t.stock = n.stock;