oracle中的MERGE的使用

oracle中的MERGE的使用

1 MERGE是什么

--- 测试脚本

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;

 

--- 简单应用

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);

 

 

2.MERGE在10g的一些改进 

 

--- 可对MERGE 语句加条件

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' ---此处表示对MERGE 的条件进行过滤

;

--- 可用DELETE 子句清除行(清除的是目标表T2)

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')

;

--- 可采用无条件方式INSERT(等同于INSERT....SELECT....)

MERGE INTO T2

USING T1

ON (1=2)

WHEN NOT MATCHED THEN

INSERT

VALUES (T1.NAME,T1.MONEY);

 

3.使用中需要注意的地方 

 

--- 无法在源表中获得一组稳定的行

DROP TABLE T1;

CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);

INSERT INTO T1 VALUES ('A',10);

INSERT INTO T1 VALUES ('A',30);

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;

  --执行脚本

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE

SET T2.MONEY=T1.MONEY+T2.MONEY

;

  ORA-30926: unable to get a stable set of rows in the source tables

  

--将 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

;

  --但是注意 这样可能跟你的原意图偏离

 

--- 在MERGE INTO T2 USING T1 ON…的MERGE 表达式中，如果反过来，一条T1 记录被连接到多条T2 记录，

--是可以使多条T2 记录都被更新而不会出错。

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 ('A',40);

INSERT INTO T2 VALUES ('C',20);

COMMIT;

 

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE

SET T2.MONEY=T1.MONEY+T2.MONEY

;

COMMIT;

 

--- 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';

/

--正确写法

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')

;

--- DELETE 子句只可以删除目标表，而无法删除源表

 

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')

;

--检测如果把DELETE WHERE (T2.NAME='A')修改为DELETE WHERE (T1.NAME='A')，是否就会把源表的T1 记录给删除;

rollback;

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE

SET T2.MONEY=T1.MONEY+T2.MONEY

DELETE WHERE (T1.NAME='A')

;

--结果 

SQL> select * from t1;

 

NAME                      MONEY

-------------------- ----------

A                            10

B                            20

 

SQL> select * from t2;

 

NAME                      MONEY

-------------------- ----------

C                            20

  --结果是不会删除源表数据

 

--- 更新同一张表的数据，需担心USING 的空值

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)

;

--发现T2表中并没有插入记录 那是因为源表 (SELECT * FROM t2 WHERE NAME='D') 中本来就不存在

--解决方法：

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)

;

 

 

4.MERGE 的巧妙运用  

 

--案例1:行直接互换字段值

 

drop table test;

create table test (id number,name varchar2(20));

insert into test values (1, 'a');

insert into test values (2, 'b');

COMMIT;

--可能你会这么写，但是结果显然是不对的

UPDATE TEST SET NAME=(SELECT NAME FROM TEST WHERE ID=2) WHERE ID=1;

UPDATE TEST SET NAME=(SELECT NAME FROM TEST WHERE ID=1) WHERE ID=2;

--变通方法：构建一个虚拟表，如下的形式

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

;

 

--如果只是查询出改变后的结果而不是真实地进行更新，则可以不采用MERGE

rollback;

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 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 test.id=t.id

) n

on(test.rowid=n.rn)

when matched then update

set test.name=n.name;