对MERGE使用的心得

对MERGE使用的心得[@more@]很多人在使用merge的时候，两个匹配表都是不一样的，如果两个表相同会如何呢？

CREATE TABLE TEST_MERGE (CODE VARCHAR2(10),NAME VARCHAR2(10));
INSERT INTO TEST_MERGE VALUES ('1','APPLE');
INSERT INTO TEST_MERGE VALUES ('2','PEAR');
COMMIT;

过程对存在代码的记录进行更新，否则插入数据
CREATE OR REPLACE PROCEDURE PROC_TEST_MERGE (P_CODE VARCHAR2,P_NAME VARCHAR2)
AS
BEGIN

MERGE INTO TEST_MERGE FF
USING ( SELECT CODE,NAME FROM TEST_MERGE
WHERE CODE = P_CODE ) FC
ON ( FF.CODE = FC.CODE )
WHEN MATCHED THEN
UPDATE SET FF.NAME = P_NAME
WHEN NOT MATCHED THEN
INSERT (CODE,NAME)
VALUES (P_CODE,P_NAME);
COMMIT;
END;
/

SQL> select * from TEST_MERGE;

CODE NAME
---------- ----------
1 APPLE
2 PEAR

SQL> EXEC PROC_TEST_MERGE ('1','BANANA');

PL/SQL procedure successfully completed

SQL> select * from TEST_MERGE;

CODE NAME
---------- ----------
1 BANANA
2 PEAR
更新没有问题
SQL> EXEC PROC_TEST_MERGE ('3','APPLE');

PL/SQL procedure successfully completed

SQL> select * from TEST_MERGE;

CODE NAME
---------- ----------
1 BANANA
2 PEAR
？？新增却不执行了？？
问题在于 ( SELECT CODE,NAME FROM TEST_MERGE WHERE CODE = P_CODE ) FC
这个子查询，因为引用的是自身，所以对于新的代码是没有对应记录的，该子查询的结果集为空
也是这个原因导致了后面的matched匹配时无解，从而无法进行数据的插入
修改过程,随便增加一条数据中不可能的记录
CREATE OR REPLACE PROCEDURE PROC_TEST_MERGE (P_CODE VARCHAR2,P_NAME VARCHAR2)
AS
BEGIN

MERGE INTO TEST_MERGE FF
USING ( SELECT CODE,NAME FROM TEST_MERGE
WHERE CODE = P_CODE
UNION SELECT 'ERR','A' FROM DUAL) FC
ON ( FF.CODE = FC.CODE )
WHEN MATCHED THEN
UPDATE SET FF.NAME = P_NAME
WHEN NOT MATCHED THEN
INSERT (CODE,NAME)
VALUES (P_CODE,P_NAME);
COMMIT;
END;
/
SQL> EXEC PROC_TEST_MERGE ('3','APPLE');

PL/SQL procedure successfully completed

SQL> select * from TEST_MERGE;

CODE NAME
---------- ----------
1 BANANA
2 PEAR
3 APPLE

可见，使用merge时，必须保证对应匹配的结果集不为空，否则会影响新增记录的操作

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/177967/viewspace-904108/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/177967/viewspace-904108/