Oracle之merge经典案例

--MERGE的巧妙运用
/* 

案例1：
需求为：将如下t记录的ID=1的NAME改为ID=2的NAME的值，把ID=2的NAME改为ID=1的NAME的值。
*/
drop table t;
create table t (id number,name varchar2(20));
insert into t values (1,'a');
insert into t values (2,'b');
COMMIT;
SQL> select * from t;
        ID NAME
---------- --------------------
         1 a
         2 b
         
--如果执行如下：
UPDATE t SET NAME =(SELECT NAME FROM t WHERE ID=2) WHERE ID=1;这个时候ID=1的NAME值已改变了，就不可能用如下来更新了。
UPDATE t SET NAME =(SELECT NAME FROM t WHERE ID=1) WHERE ID=1;


--如果是过程就很简单了，可以把原先的值先存储起来。但是是否单条SQL一定不行呢？
--其实单条SQL是可以解决的，我们可灵活用MERGE搞定！构造出一个虚拟表T，然后再根据这个虚拟T表和真实的t表进行MERGE更新，就完成了。虚拟表类似如下：


SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL;


  ID NAME
-----------
   1 b
   2 a
   
--有了这个思路，结合前面所学的MERGE知识，可以通过如下代码实现更新，代码相当简洁优雅。
   
MERGE INTO t
USING (SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
        UNION ALL
        SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
        ) t_tmp
ON (t.id = t_tmp.id)
WHEN MATCHED THEN UPDATE set t.name = t_tmp.name;


SQL> select * from t;
        ID NAME
---------- --------------------
         1 b
         2 a
         
--注：如果是9I固定需要INSERT，所以需要随便加上如下内容 WHEN NOT MATCHED THEN  INSERT   VALUES (1,'a')


--本案例用的是MERGE的方法，当然，其中的构造虚拟表也是个非常重要的思路，如果只查询出改变后的结果而无需更新，就可以不用MERGE，直接如下查出结果：


rollback;


WITH T AS
(SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
       UNION ALL
       SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
       )
select t.id,t.name from t ,t
where t.id=t.id;


     ID NAME
    --------
      1 b
      2 a
         
/*  
       
案例2
通过MERGE，我们可以得到一个非常有用的思想，就是如下：只要能查出自己的改变后的结果，就可以更新到原来的表。用MERGE+ROWID方式。
在案例1中，我们还有另外一种思路来更新记录，不再采用构造出虚拟表，来关联t表，而是直接把真实结果用SELECT的方式取出，然后利用这个结果集更新回原表中。  


*/


merge into  t using
 (
 WITH t_tmp AS
  (SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
         UNION ALL
         SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
         )
  select t.id,t.rowid as rn ,t.name from t ,t_tmp
  where t.id=t_tmp.id)n
  on(t.rowid=n.rn)
   when matched then  update
   set t.name=n.name; 
   
SQL> select * from t;
  ID NAME
----------
   1 a
   2 b


/*


 注：直接UPDATE一个子查询的写法也可以，但是却有很多限制，稍微复杂的查询都不行。这时候用MERGE是最好的办法，结合ROWID的方式，
     我们可快速准确的利用一个已经查询出来的结果集来更新自己，是一个非常好的思路的扩展，极其实用，希望给大家开发中带来帮助。
*/