oracle中merge into语句详解

由于工作中使用，研究了merge into 语句是insert 与update语句的结合，可以同时实现update和insert的功能。

一、merge into语句的语法。

MERGE INTO schema. table alias

USING { schema. table | views | query} alias

ON {(condition) }

WHEN MATCHED THEN

UPDATE SET {clause}

WHEN NOT MATCHED THEN

INSERT VALUES {clause};

--解析

INTO 子句

用于指定你所update或者Insert目的表。

USING 子句

用于指定你要update或者Insert的记录的来源，它可能是一个表，视图，子查询。

ON Clause

用于目的表和源表（视图，子查询）的关联，如果匹配（或存在），则更新，否则插入。

merge_update_clause

用于写update语句

merge_insert_clause

用于写insert语句

二、merge 语句的各种用法练习

创建表及插入记录

create table t_B_info_aa

( id varchar2(20),

name varchar2(50),

type varchar2(30),

price number

);

insert into t_B_info_aa values('01','冰箱','家电',2000);

insert into t_B_info_aa values('02','洗衣机','家电',1500);

insert into t_B_info_aa values('03','热水器','家电',1000);

insert into t_B_info_aa values('04','净水机','家电',1450);

insert into t_B_info_aa values('05','燃气灶','家电',800);

insert into t_B_info_aa values('06','太阳能','家电',1200);

insert into t_B_info_aa values('07','西红柿','食品',1.5);

insert into t_B_info_aa values('08','黄瓜','食品',3);

insert into t_B_info_aa values('09','菠菜','食品',4);

insert into t_B_info_aa values('10','香菇','食品',9);

insert into t_B_info_aa values('11','韭菜','食品',2);

insert into t_B_info_aa values('12','白菜','食品',1.2);

insert into t_B_info_aa values('13','芹菜','食品',2.1);

create table t_B_info_bb

( id varchar2(20),

type varchar2(50),

price number

);

insert into t_B_info_bb values('01','家电',2000);

insert into t_B_info_bb values('02','家电',1000);

1) update和insert同时使用

merge into t_B_info_bb b

using t_B_info_aa a --如果是子查询要用括号括起来

on (a.id = b.id and a.type = b.type) --关联条件要用括号括起来

when matched then

update set b.price = a.price --update 后面直接跟set语句

when not matched then

insert (id, type, price) values (a.id, a.type, a.price) --insert 后面不加into

---这条语句根据t_B_info_aa 更新了t_B_info_bb中的一条记录，插入了11条记录

2)只插入不更新

--处理表中数据，使表中的一条数据发生变化，删掉一部分数据。用来验证只插入不更新的功能

update t_B_info_bb b set b.price=1000 where b.id='02';

delete from t_B_info_bb b where b.type='食品';

--只是去掉了when matched then update语句

merge into t_B_info_bb b

using t_B_info_aa a

on (a.id = b.id and a.type = b.type)

when not matched then

insert (id, type, price) values (a.id, a.type, a.price)

3)只更新不插入

--处理表中数据，删掉一部分数据，用来验证只更新不插入

delete from t_B_info_bb b where b.type='食品';

--只更新不插入，只是去掉了when not matched then insert语句

merge into t_B_info_bb b

using t_B_info_aa a

on (a.id = b.id and a.type = b.type)

when matched then

update set b.price = a.price

三、加入限制条件的操作

变更表中数据，便于练习

update t_B_info_bb b

set b.price = 1000

where b.id='02'

delete from t_b_info_bb b where b.id not in ('01','02') and b.type='家电'

update t_B_info_bb b

set b.price = 8

where b.id='10'

delete from t_b_info_bb b where b.id in ('11','12','13') and b.type='食品'

表中数据

执行merge语句：脚本一和脚本二执行结果相同

脚本一： merge into t_B_info_bb b

using t_B_info_aa a

on (a.id = b.id and a.type = b.type)

when matched then

update set b.price = a.price where a.type = '家电'

when not matched then

insert

(id, type, price)

values

(a.id, a.type, a.price) where a.type = '家电';

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

脚本二： merge into t_B_info_bb b

using (select a.id, a.type, a.price

from t_B_info_aa a

where a.type = '家电') a

on (a.id = b.id and a.type = b.type)

when matched then

update set b.price = a.price

when not matched then

insert (id, type, price) values (a.id, a.type, a.price);

结果：

上面两个语句是只对类型是家电的语句进行插入和更新

而脚本三是只对家电进行更新，其余的全部进行插入

merge into t_B_info_bb b

using t_B_info_aa a

on (a.id = b.id and a.type = b.type)

when matched then

update set b.price = a.price

where a.type = '家电'

when not matched then

insert

(id, type, price)

values

(a.id, a.type, a.price);

四、加删除操作

update子句后面可以跟delete子句来去掉一些不需要的行

delete只能和update配合，从而达到删除满足where条件的子句的记录

例句：

merge into t_B_info_bb b

using t_B_info_aa a

on (a.id=b.id and a.type = b.type)

when matched then

update set b.price=a.price

delete where (a.type='食品')

when not matched then

insert values (a.id, a.type, a.price) where a.id = '10'

————————————————