MERGE用法
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
----------------------------------------------------------------------------
| 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_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;
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
----------------------------------------------------------------------------
| 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_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;
