Oracle merge into用法详解：如何同时更新插入删除数据_Oracle

在oracle中，常规的dml语句只能完成单一功能，例如insert/delete/update只能三选一，而merge into语句可以同时对一张表进行更新/插入/删除。

一、基本语法

merge into常用在数据同步的场景，它会选定一张基表作为数据源，然后与目标表的记录进行匹配，根据匹配的结果，可以对目标表同时进行更新/插入/删除操作。

语法：

merge into target_table
using source_table on (join_condition)
when matched then update set … [where …] [delete … where …]
when not matched then insert (column_list) values (value_list) [where …]
log errors into … reject limit [integer|unlimited]

target_table 是要更新的表
source_table 是参考表
using … on () 是两个表的连接条件，用于判断记录是否匹配
when matched 对于满足匹配条件的记录进行的操作，可以更新或删除
when not matched 对于不满足匹配条件的记录，可以插入
log errors into 可以将匹配错误的记录记录到日志表中

二、用法示例

先创建2张表，一张源表，一张目标表：

create table src_table(
id number(6),
name varchar2(32),
salary number(6),
bonus number(6));

insert into src_table values(1,'vincent',1000,100);
insert into src_table values(2,'victor',2000,200);
insert into src_table values(3,'grace',3000,300);


create table tgt_table(
id number(6),
name varchar2(32),
age number(6),
salary number(6),
bonus number(6));

insert into tgt_table values(1,'someone',1,0,0);
insert into tgt_table values(3,'someone',3,0,0);
insert into tgt_table values(4,'someone',4,0,0);
commit;

两张表的数据如下：

select * from src_table;
select * from tgt_table;

2.1同时更新和插入

下面是最基本的用法，利用源表对目标表同时进行更新和插入

merge into tgt_table t
using src_table s on (t.id=s.id)
when matched then update set t.name=s.name, t.salary=s.salary, t.age=10
when not matched then insert values(s.id,s.name,10,s.salary,s.bonus+50);

commit;

select * from tgt_table;

when matched 为匹配存在的记录（id为1，3），更新了name, salary, age个字段
when not match 为目标表不存在的记录（id为2），插入了该记录，同时bouns字段加50
matched 和 not matched的子句是独立的，可以任意选择一项，或同时出现
目标表中id为4的记录在源表中不存在（不满足连接条件），因此不会涉及

2.2 where子句

对于匹配的记录，可以使用where子句进一步限制范围

merge into tgt_table t
using src_table s on (t.id=s.id)
when matched then update set t.bonus=s.bonus where s.id>=2;

select * from tgt_table order by id;

这里对匹配的记录bonus字段进行更新，同时进一步限制id>=2的记录才更新
where 子句的条件可以通过源表或者目标表指定，这里是通过源表的s.id指定

2.3 delete子句

在update子句后，还可以跟上delete … where …子句，对匹配上的记录进行删除操作

merge into tgt_table t
using src_table s on (t.id=s.id)
when matched then update set t.bonus=s.bonus+50 where s.id>=3 delete where id>=2;

select * from tgt_table order by id;

delete where id>=2 指定将id大于等于2的记录删除，但注意id为2的记录并未被删除，只有3被删除了
因为delete只会在update匹配的到记录范围内删除，update子句有个where s.id>=3，delete也会受到这个条件的限制
实际执行的效果是 delete where s.id>=3 and id>=2，只有id为3的记录满足这个条件
id为4的记录不在匹配范围内，不受merge into语句的影响，也不会被删除

2.4记录同步错误

数据同步的时，源表和目标表的结构/数据类型/约束可能并不一致，这就导致数据同步可能部分失败，现在我们修改tgt表，限制salary字段值不能超过3000，即插入超过3000的数字将失败：

alter table tgt_table modify salary number(6) check(salary<=3000);

首先调用dbms_errlog.create_error_log为tgt_table创建一张错误日志表，表名为errlog：

exec dbms_errlog.create_error_log('tgt_table', 'errlog');

第一个参数指定要创建错误日志的基表，第二个参数是错误日志表名

然后在执行merge into 语句时，在最后跟上log errors into子句，如果语句执行过程中遇到错误，则会将错误记录到错误日志中，方便后期排查和修复，这里将tgt_table清空，尝试将src_table的3条数据同步进去，同时salary增加1000

truncate table tgt_table;

merge into tgt_table t
using src_table s on (t.id=s.id)
when not matched then insert values(s.id,s.name,10,s.salary+1000,s.bonus)
log errors into errlog('something is wrong.');

select * from tgt_table;

这里将tgt_table表清空，然后尝试将src_table中的3条记录同步进来，同步过程中我们将salary增加了1000
因为tgt_table表salary有约束不能超过3000，因此语句回滚，一条记录都没同步进来
errlog中的'something is wrong.' 是用户定义的错误标记，可以帮助识别是哪个语句导致的错误

查询errlog表，可以看到导致失败的原因，id为3的记录，salary在增加1000后为4000，违反了目标表的约束（check salary<=3000）：

select * from errlog;

如果我们不想让出现错误的时候语句就回滚，可以在后面跟上一个reject limit n子句，限制只有出现n个以上的错误时才回滚语句：

merge into tgt_table t
using src_table s on (t.id=s.id)
when not matched then insert values(s.id,s.name,10,s.salary+1000,s.bonus)
log errors into errlog('allow one error.') reject limit 1;

select * from tgt_table;