欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[Oracle]Merge语句

程序员文章站 2022-08-08 09:41:05
merge的语法如下: merge [hint] into [schema .] table [t_alias] using [schema .] { table | v...

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需要引起怀疑,不太应该。