MERGE函数的妙用案例

 

本文来至《剑破冰山》-Oracle开发艺术

/******************************************************************************
 *探索merge方法
 *
 *****************************************************************************/
 --1.what is merge?
 /*
 for exmple
 从T1表更新数据到T2表，如果T2表NAME字段的记录在T1表中存在，就将MONEY字段的值累加，如果不
 存在，将T1表的记录插入到T2表中。
 */
 DROP TABLE T1;
 CREATE TABLE t1(NAME VARCHAR2(20),money NUMBER);
 INSERT INTO t1 VALUES('A',100);
 INSERT INTO t1 VALUES('B',200);
 
 DROP TABLE t2;
 CREATE TABLE t2(NAME VARCHAR2(20),money NUMBER);
 INSERT INTO t2 VALUES('A',300);
 INSERT INTO t2 VALUES('C',100);
 COMMIT;
 
 SELECT * FROM t1;
 /*
  大家都知道，按照一般逻辑思路，该需求至少需要update和insert两条sql才能完成,
  如考虑在PL/SQL中用纯编程语言思路实现，则必须要考虑逻辑判断，这样就显得更麻烦了。
  merge语句的出现正是为了解决此类问题，使用merge语句，可以实现“存在则update，
  不存在则insert”的逻辑
 */
 --以下是使用merge语句简洁明了地单条实现该需求，如下：
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
    );
COMMIT;

SELECT * FROM t2;

/******************
merge的巧妙用法 
*******************/
/**
 案例1
 需求为：将如下TEST记录ID=1 的 NAME 改为 ID=2 的 NAME 值，
 将 ID=2 的 NAME 改为 ID=1 的 NAME 值
*/
DROP TABLE test;
CREATE TABLE test(ID NUMBER,NAME VARCHAR2(20));
INSERT INTO test VALUES(1,'a');
INSERT INTO test VALUES(2,'b');
COMMIT;
SELECT * FROM test;

--如果执行如下：
UPDATE test SET NAME =(SELECT NAME FROM test WHERE ID=2)WHERE ID =1;
--此时ID=1的NAME值已经变了，就不可能用如下代码来更新了。
UPDATE test SET NAME =(SELECT NAME FROM test WHERE ID=1)WHERE ID=2;

--如果是过程就很简单了，可以把原来的值先存储起来。但是是否但条件SQL一定不行呢？
--其实单条SQL是可以解决的，可以考虑灵活利用merge特性。可考虑先构造一个虚拟表T，
--然后再根据此虚拟表T和真实的TEST表进行merge更新，这样就方便快捷地完成了。
--1.构造虚拟表
SELECT 1 ID, (SELECT NAME FROM TEST WHERE ID = 2) NAME
  FROM DUAL
UNION ALL
SELECT 2, (SELECT NAME FROM TEST WHERE ID = 1) FROM DUAL;

--有了此思路，结合前面所学的merge知识，可以通过如下简洁的代码来完成更新。
MERGE INTO TEST
USING (SELECT 1 ID, (SELECT NAME FROM TEST WHERE ID = 2) NAME
         FROM DUAL
       UNION ALL
       SELECT 2, (SELECT NAME FROM TEST WHERE ID = 1) FROM DUAL
       
       ) T
ON (TEST.ID = T.ID)
WHEN MATCHED THEN
  UPDATE SET TEST.NAME = T.NAME
WHEN NOT MATCHED THEN
  INSERT VALUES (T.ID, T.NAME);

SELECT * FROM test;

--本案例用的是merge的方法，当然，其中构造虚拟表也是一个非常重要的思路，如果只是
--查询出改变后的结果而不是真实地进行更新，则可以不采用merge，直接采用如下方式
--取出结果
WITH t AS (
SELECT 1 ID, (SELECT NAME FROM TEST WHERE ID = 2) NAME
  FROM DUAL
UNION ALL
SELECT 2, (SELECT NAME FROM TEST WHERE ID = 1) FROM DUAL
)
SELECT TESt.ID, T.NAME FROM TEST, T WHERE TEST.ID = T.ID;

--案例2
--通过merge 可以得到一个非常有用的方法，就是只要检查出更新后的结果集，就可以
--利用该结果集来更新原表记录，即MERGE+ROWID方法

--案例2是案例1的延伸，改变了案例1的处理思路，不再采用构造虚拟表T来关联TEST表的方式，
--而是直接把真实结果用SELECT的方式取出，然后利用这个结果集更新原表记录。
MERGE INTO TEST
USING (WITH T AS (SELECT 1 ID, (SELECT NAME FROM TEST WHERE ID = 2) NAME
                    FROM DUAL
                  UNION ALL
                  SELECT 2, (SELECT NAME FROM TEST WHERE ID = 1) FROM DUAL)
  SELECT TEST.ID, TEST.ROWID AS RN, T.NAME
    FROM TEST, T
   WHERE T.ID = TEST.ID) N ON (TEST.ROWID = N.RN) WHEN MATCHED THEN
    UPDATE SET TEST.NAME = N.NAME
  WHEN NOT MATCHED THEN
    INSERT VALUES (N.ID, N.NAME);
SELECT * FROM test;

--后记
/******
 直接UPDATE一个子查询的写法也是可以的，但是却又很多限制，稍微复杂的查询就容易出错。
 此时用MERGE是最好的办法，结合ROWID的方法，可快速准确地利用一个已查询出的结果集来
 更新自己，是一个非常好的思路，希望对大家有借鉴。
****/