MERGE 函数的使用[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。

1. MERGE是什么？

通过一个简单的需求来了解MERGE 是什么？

从T1表更新数据到T2表，如果T2表NAME字段的记录在T1表中存在，就将MONEY字段的值累加，

如果不存在，将T1表的记录插入到T2表中。

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

INSERT INTO T1 VALUES (‘A’,10);

INSERT INTO T1 VALUES (‘B’,20);

COMMIT;

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

INSERT INTO T2 VALUES (‘A’,30);

INSERT INTO T2 VALUES (‘C’,20);

COMMIT;

如果按照一般逻辑思路，该需求至少需要UPDATE和INSERT两条SQL语句才能完成，

但是使用MERGE语句，则可以实现“存在则UPDATE，不存在则INSERT”的逻辑。

MERGE 的语法：

MERGE INTO table [t.alias]

USING {table | view | subquery} [t.alias]

ON (condition)

WHEN MATCHED THEN

merge_updat_clause

WHEN NOT MATCHED THEN

merge_insert_clause;

注：USING后面必须包含要更新或插入的行，可以是表也可以是语句

则上面的需求我们可以利用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;

2. MERGE的完善

2.1. UPDATE 和 INSERT 动作可只出现其一

可选择仅更新目标表：

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY;

COMMIT;

可选择仅插入（INSERT）目标表而不做任何更新（UPDATE）操作：

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN NOT MATCHED THEN

INSERT VALUES (T1.NAME,T1.MONEY);

COMMIT;

2.2 可对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的条件进行过滤

COMMIT;

2.3 可用DELETE 子句清除行

清除行的前提条件是，要找到满足 T1.NAME=T2.NAME 的记录，如果T2.NAME=‘A’并不

满足T1.NAME=T2.NAME过滤出的记录集，那么 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=’C’); 虽然 DELETE 语句不生效，但前面的 UPDATE 语句还是生效的

COMMIT;

2.4 可采用无条件方式 INSERT

在语法 ON 关键字处写上恒不等条件（1=2）后，MATCHED 语句的 INSERT

就变为无条件 INSERT 了。

MERGE INTO T2

USING T1

ON (1=2)

WHEN NOT MATCHED THEN

INSERT VALUES (T1.NAME,T1.MONEY);

COMMIT;

3. MERGE 误区探索

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

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

就产生了ORA-30926错误

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

COMMIT;

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

INSERT INTO T2 VALUES (‘A’,30); — T2.NAME 连接到多条 T1.NAME

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;

Oracle 中的 MERGE 语句应该保证 ON 条件的唯一性，T1.NAME=’A’时，T2表记录

对应到了T1表的两条记录，所以就出错了。

解决方法：

a. 对T1表和T2表的关联字段建立主键，这样就基本避免了此类问题，若MERGE语句

的关联字段互相有主键，MERGE的效率将比较高;

b. 将T1表的NAME列做一个聚合，这样归并成单条，也能避免此类错误。

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; — 此时T2表中NAME=’A’的两条记录都被更新了

3.2 DELETE 子句的 WHERE 顺序必须在最后

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.3 DELETE 子句只可以删除目标表，而无法删除源表

无论DELETE WHERE T2.NAME=’A’ 这个写法的T2是否改写为T1，效果都一样，都是对目标表进行删除

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’ 删除的还是目标表

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/132216.html原文链接：https://javaforall.cn