[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的语句中，显的比较麻烦而且容易出错。如果了解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

/*
方法很简单，在语法on关键字处写上恒不等条件（如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表的两条记录，所以就出错了。
解决方法很简单，比如我们可以对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需要引起怀疑，不太应该。