Oracle Merge语句

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;
MERGE是什么,怎样使用呢?让我们先看一个简单的需求:

需求是,从T1表更新数据到T2表中。假设T2表的NAME 在T1表中已存在,就将MONEY累加,假设不存在。将T1表的记录插入到T2表中。

大家知道,在等价的情况下,一定需要至少两条语句,一条为UPDATE,一条为INSERT,并且语句中必需要与推断的逻辑,或者写在过程中,假设是单条语句,就要写全条件。
写在UPDATE和INSERT的语句中,显的比較麻烦并且easy出错。假设了解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的四大灵活之处

上面讲了Merge的语法和基本使用方法,其实Merge能够很灵活。
1.UPDATE和INSERT动作可仅仅出现其一(9I必须同一时候出现。)
--我们可选择只UPDATE目标表
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY;

--也可选择只INSERT目标表而不做不论什么UPDATE动作

MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN NOT MATCHED THEN
INSERT
VALUES (T1.NAME,T1.MONEY);
2.可对MERGE语句加条件
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
WHERE T1.NAME='A';
3.可用DELETE子句清除行
/*

在这样的情况下,首先是要先满足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');
4.可採用无条件方式Insert
/*
方法非常easy,在语法ONkeyword处写上恒不等条件(如1=2)后,MATCHED语句的INSERT就变为无条件INSERT了,详细例如以下
*/


MERGE INTO T2
 USING T1
 ON (1=2)
 WHEN NOT MATCHED THEN
 INSERT
VALUES (T1.NAME,T1.MONEY);

Merge的误区

1. 不能更新ON子句引用的列
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.NAME=T1.NAME;

ORA-38104: 无法更新 ON 子句中引用的列: "T2"."NAME"
2. DELETE子句的WHERE顺序必须最后
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')
WHERE T1.NAME='A';

ORA-00933: SQL 命令未正确结束
3.DELETE 子句仅仅能够删除目标表。而无法删除源表
/*
 这里须要引起注意,不管DELETE WHERE (T2.NAME = 'A' )这个写法的T2是否改写为T1。效果都一样,都是对目标表进行删除。
*/

SELECT * FROM T1;
NAME                      MONEY
-------------------- ----------
A                            10
B                            20

SELECT * FROM T2;
NAME                      MONEY
-------------------- ----------
A                            30
C                            20

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' );
  
  
SELECT * FROM T1;

NAME                      MONEY
-------------------- ----------
A                            10
B                            20


SELECT * FROM T2;

NAME                      MONEY
-------------------- ----------
C                            20

4.更新同一张表的数据,需操心USING的空值
SELECT * FROM T2;
NAME                      MONEY
-------------------- ----------
A                            30
C                            20

/*

需求为对T2表进行自我更新。假设在T2表中发现NAME=D的记录,就将该记录的MONEY字段更新为100,假设NAME=D的记录不存在,
则自己主动添加。NAME=D而且MONEY=100的记录。依据语法完毕例如以下代码:

*/

MERGE INTO T2
USING (select * from t2 where NAME='D') T
ON (T.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=100
WHEN NOT MATCHED THEN
INSERT
VALUES ('D',200);



--可是查询发现。本来T表应该由于NAME=D不存在而要添加记录。可是实际却根本无变化。
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------------------------------------------
A                            30
C                            20

/*
   原来是由于此时select * from t2 where NAME='D'为NULL,所以出现了无法插入的情况。
   我们能够利用COUNT(*)的值不会为空的特点来等价改造。详细例如以下:
*/

MERGE INTO T2
USING (select COUNT(*) CNT from t2 where NAME='D') T
ON (T.CNT<>0)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=100
WHEN NOT MATCHED THEN
INSERT
VALUES ('D',100);


SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------------------
A                            30
C                            20
D                           100
5. 必需要在源表中获得一组稳定的行
---构造数据,请注意这里多插入一条A记录,就产生了ORA-30926错误
INSERT INTO T1 VALUES ('A',30);
COMMIT;

---此时继续运行例如以下
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY;
ORA-30926: 无法在源表中获得一组稳定的行

/*
oracle中的merge语句应该保证on中的条件的唯一性,T1.NAME=T2.NAME的时候。T1表记录相应到了T2表的两条记录,所以就出错了。

解决方法非常easy。比方我们能够对T1表和T2表的关联字段建主还键,这样基本上就不可能出现这种问题,并且一般而言,MERGE语句的关联字段互相有主键。MERGE的效率将比較高!或者是将T1表的ID列做一个聚合。这样归并成单条,也能避免此类错误。

如:*/ MERGE INTO T2 USING (select NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY;--正常情况下,一般出现反复的NAME须要引起怀疑,不太应该。

转自:https://www.cnblogs.com/cynchanpin/p/7074205.html

阅读更多
文章标签: oracle merge
个人分类: Oracle DB
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭