Oracle之merge误区讨论

---merge五大误区

---构造数据，请注意这里多插入一条A记录，就产生了ORA-30926错误
DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 VALUES ('B',20);
DROP TABLE T2;
CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T2 VALUES ('A',30);
INSERT INTO T2 VALUES ('C',20);
COMMIT; 
  
---1. 不能更新ON子句引用的列
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.NAME=T1.NAME;


ORA-38104: 无法更新 ON 子句中引用的列: "T2"."NAME"
  




---2. DELETE子句的WHERE顺序必须最后
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
DELETE WHERE (T2.NAME = 'A')
WHERE T1.NAME='A';


ORA-00933: SQL 命令未正确结束


--改为如下即可：


MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
WHERE T1.NAME='A'
DELETE WHERE (T2.NAME = 'A');


---3.DELETE 子句只可以删除目标表，而无法删除源表,无论是否在where后面加原表限制条件，都无法删除原表的数据
/*
 这里需要引起注意，无论DELETE WHERE (T2.NAME = 'A' )这个写法的T2是否改写为T1，效果都一样，都是对目标表进行删除！
*/


SELECT * FROM T1;
NAME                      MONEY
-------------------- ----------
A                            10
B                            20


SELECT * FROM T2;
NAME                      MONEY
-------------------- ----------
A                            30
C                            20


MERGE INTO T2
  USING T1
  ON (T1.NAME=T2.NAME)
  WHEN MATCHED THEN
  UPDATE
  SET T2.MONEY=T1.MONEY+T2.MONEY
  DELETE WHERE (T2.NAME = 'A' );
  
  
SELECT * FROM T1;


NAME                      MONEY
-------------------- ----------
A                            10
B                            20




SELECT * FROM T2;


NAME                      MONEY
-------------------- ----------
C                            20

---4.更新同一张表的数据,需担心USING的空值
rollbac;
SELECT * FROM T2;
NAME                      MONEY
-------------------- ----------
A                            30
C                            20


/*


需求为对T2表进行自我更新，如果在T2表中发现NAME=D的记录，就将该记录的MONEY字段更新为100，如果NAME=D的记录不存在，
则自动增加，NAME=D并且MONEY=100的记录。根据语法完成如下代码:


*/


MERGE INTO T2
USING (select * from t2 where NAME='D') T
ON (T.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=100
WHEN NOT MATCHED THEN
INSERT
VALUES ('D',200);


--但是查询发现，本来T表应该因为NAME=D不存在而要增加记录，但是实际却根本无变化。
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------------------------------------------
A                            30
C                            20


/*
   原来是因为此时select * from t2 where NAME='D'为NULL,所以出现了无法插入的情况，
   我们可以利用COUNT(*)的值不会为空的特点来等价改造，具体如下:
*/


MERGE INTO T2
USING (select COUNT(*) CNT from t2 where NAME='D') T
ON (T.CNT<>0)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=100
WHEN NOT MATCHED THEN
INSERT
VALUES ('D',100);




SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------------------
A                            30
C                            20
D                           100


rollback;


---5. 必须要在源表中获得一组稳定的行


---构造数据，请注意这里多插入一条A记录，就产生了ORA-30926错误
INSERT INTO T1 VALUES ('A',30);
COMMIT;


---此时继续执行如下
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY;
ORA-30926: 无法在源表中获得一组稳定的行


/*
oracle中的merge语句应该保证on中的条件的唯一性，T1.NAME=T2.NAME的时候，T1表记录对应到了T2表的两条记录，所以就出错了。
解决方法很简单，比如我们可以对T1表和T2表的关联字段建主还键，这样基本上就不可能出现这样的问题，而且一般而言，MERGE语句的关联字段互相有主键，
MERGE的效率将比较高！或者是将T1表的ID列做一个聚合，这样归并成单条，也能避免此类错误。如：
*/   


MERGE INTO T2
  USING (select NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1
  ON (T1.NAME=T2.NAME)
  WHEN MATCHED THEN
  UPDATE
  SET T2.MONEY=T1.MONEY+T2.MONEY;


--正常情况下，一般出现重复的NAME需要引起怀疑，不太应该。