Oracle高级应用之合并MERGE

数据库技术学习 https://www.itkc8.com

MERGE是Oracle9i新增的语法，中文意思是“合并”，那合并什么呢？它能合并INSERT和UPDATE在一条SQL语句中执行，是不是很牛X？！还有更牛X的，就是在执行该条语句时只做一次全表扫描，效率非常高。我们需要做两点准备工作：
一、创建测试表且填充测试数据

create table merge_test as select * from scott.dept 

二、修改测试表的数据，为了与原表scott.dept的数据产生差异。

delete from merge_test t where t.deptno=10  

update merge_test t set t.dname = 'TEST'  

OK，那现在客户提出一个需求，让表merge_test中的机构信息与scott.dept表保持一致。
这个太简单了，执行下面的两条SQL语句不就可以了？！

delete from merge_test  

insert into merge_test select * from scott.dept 

虽然说此方法确实到达了“目的”，但是却太不完善了，如果客户只想要保证机构名称字段一致即可呢？此方法肯定便秘了，还有谁有更好的方法？

有！有！有！先把scott.dept表中的字段dname值更新到表merge_test的字段dname

update merge_test m  set dname = (select dname from scott.dept t where m.deptno = t.deptno)

再把没在merge_test表中而在scott.dept表中的数据插入表merge_test

insert into merge_test m select * from scott.dept where deptno not in (select deptno from merge_test)

讲一种更加高级的方法，先看SQL语句

merge into merge_test m using scott.dept t on(m.deptno = t.deptno) when
   matched then update set m.dname = t.dname when not matched then insert values(t.deptno, t.dname, t.loc)

把表scott.dept中的数据根据关联条件m.deptno = t.deptno更新到merge_test表中，如果表merge_test中的deptno在表scott.dept中存在（matched 匹配），则执行更新操作update set m.dname = t.dname，如果表merge_test中的deptno在表scott.dept中不存在（not matched 不匹配），则执行插入操作insert values(t.deptno, t.dname, t.loc)。

比如我只想更新deptno=20的部门名称，修改后的SQL语句如下：

merge into merge_test m using scott.dept t on(m.deptno = t.deptno) when
   matched then update set m.dname = t.dname
   where (m.deptno = 20) when not matched then insert values(t.deptno, t.dname, t.loc)

在该语法中甚至还可以使用delete，如果表merge_test中的deptno在表scott.dept中存在则删除deptno=20的机构信息，修改后的SQL语句如下：

merge into merge_test m using scott.dept t on(m.deptno = t.deptno) when
   matched then update set m.dname = t.dname delete
   where (m.deptno = 20) when not matched then insert values(t.deptno, t.dname, t.loc)

备注：where与delete的语法是Oracle10i新增的。
数据库技术学习 https://www.itkc8.com