Oracle高级应用之合并MERGE

iinrsgksbg

　　为了方便大家学习和测试，所有的例子都是在Oracle自带用户Scott下建立的。
　　
　　MERGE是什么？这么厉害的东西你都不知道，你这学生是怎么学习的？老师不给你讲你就不会自学了吗？哎！可怜天下老师心啊，罢了罢了，老师现在给你好好讲一下吧。
MERGE是Oracle9i新增的语法，中文意思是“合并”，那合并什么呢？它能合并INSERT和UPDATE在一条SQL语句中执行，是不是很牛 X？！还有更牛X的，就是在执行该条语句时只做一次全表扫描，效率非常高。是不是有点心痒难耐了，哈哈，不着急，老师接下来给你具体讲讲它的用法。不过在 讲之前我们需要做两点准备工作：
一、创建测试表且填充测试数据
　　
[sql]  view plaincopy

• create table merge_test as select * from scott.dept  
  

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

• delete from merge_test t where t.deptno=10  
  

　　
[sql]  view plaincopy

• update merge_test t set t.dname = 'TEST'  
  

　　OK，那现在客户提出一个需求，让表merge_test中的机构信息与scott.dept表保持一致。

这个太简单了，执行下面的两条SQL语句不就可以了？！
[sql]  view plaincopy

• delete from merge_test  
  

　　
[sql]  view plaincopy

• insert into merge_test select * from scott.dept  
  

　　不得不说这位同学确实有过人之处，这个别人想都不敢想的方法都被他想到了，老师只能说：“我要是你爸，当年你就在墙上了”。虽然说此方法确实到达了“目的”，但是却太不完善了，如果客户只想要保证机构名称字段一致即可呢？此方法肯定便秘了，还有谁有更好的方法？

有！有！有！先把scott.dept表中的字段dname值更新到表merge_test的字段dname。
[sql]  view plaincopy

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

　　再把没在merge_test表中而在scott.dept表中的数据插入表merge_test。
[sql]  view plaincopy

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

　　这位同学还不错，有点SQL基础，很好，为了激励其他同学能想出更好的方法，老师决定下课后给这位同学买小布丁吃。不过是否还有更好更便捷的方法呢？我想 同学们的水平应该也就到这，那老师就不再谦虚了（老师是干什么使的？老师就是在你们不知道的时候让你们知道，在你们都知道的时候回家睡大觉）。下面老师就 给你们讲一种更加高级的方法，先看SQL语句。
[sql]  view plaincopy

• 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语句如下：
[sql]  view plaincopy

• 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)  
  

　　为了表达老师是一位可爱的、正直的、无私的、（此处省略掉1500字褒义词）人，老师决定在本节课最后赠送另外一个“小礼品”给同学们。在该语法中甚至还 可以使用delete，如果表merge_test中的deptno在表scott.dept中存在则删除deptno=20的机构信息，修改后的SQL 语句如下：
[sql]  view plaincopy

• 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)  
  

　　好了现在老师可以下课回家睡觉了，GOOD GOOD STUDY, DAY DAY UP.

备注：where与delete的语法是Oracle10i新增的。
　　
　　原文地址：http://blog.csdn.net/fu0208/article/details/8812536