大家好,又见面了,我是你们的朋友全栈君。
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’ 删除的还是目标表
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/132216.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...