本文来至《剑破冰山》-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的方法,可快速准确地利用一个已查询出的结果集来
更新自己,是一个非常好的思路,希望对大家有借鉴。
****/