Oracle Merge Into的用法详解

基本语法

  1. MERGE INTO 的用途 MERGE INTO 是Oracle 9i以后才出现的新的功能。那这个功能 是什么呢？ 简单来说，就是：“有则更新，无则插入”； 从这句话里，应该可以理解到，merge into 操作一个对象’A’的时候，要有另外一个结果集做为源数据 ‘B’； ‘merge into’ 将B中的数据与A中的数据按照一定条件’C’进行对比，如果 A中数据满足C条件，则进行update操作，如果不满足条件 ‘C’，则进行insert操作。（请注意这种对应关系）；

  2. 语法结构

    1
    2
    3
    4
    5
    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 可以将匹配错误的记录记录到日志表中

可以用于单条数据的处理，也可以用于数据的批处理。对于merge into来说效率要比单独执行update+insert 操作效率要高。

但是请注意，using语句中的结果集 B不可以与merge into 的对象A相同，否则，会因为结果集A,B恒等。

当 on() 进行等值判断时，只可以进行update操作，不能进行insert 操作，当 on() 进行不等值判断时，只可以进行insert操作，不能进行update操作。

可能这样说还不是很清楚。下面我们实际操作演示，就会理解清楚了。

用法示例

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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;

两张表的数据如下：

1
2
Select * from src_table;
Select * from tgt_table;

image-20240531174424130

image-20240531174437219

同时更新和插入

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

1
2
3
4
5
6
7
8
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;

image-20240531174635579

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

where子句

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

1
2
3
4
5
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;

image-20240531175114254