merge into的使用场景
一段业务逻辑,需要先判断一条记录在数据库中是否有存在,若存在则更新该记录,若不存在则插入记录。
应用之前的做法是:
1、先用条件判断记录在数据库中的个数。
2.1、若count(*)>0,则执行UPDATE操作。
2.2、若count(*)=0,则执行INSERT操作。
或
1、先插入记录。
2.1、若报ORA-001主键错误,则存在记录,此时执行UPDATE操作。
2.2、若无报错,认为插入完成。
以上两种方法,我认为都可以实现这种业务逻辑,区别在于第二种方法可能只需要一次SQL操作,前提是大部分记录都不存在,如果大部分操作都是UPDATE操作,可以这么改:
1、先更新。
2.1、若更新条数>0,则存在记录,执行完成。
2.2、若更新条数=0,则不存在记录,执行INSERT操作。
以上逻辑最差的情况就是需要执行两次SQL,如果数据量不大,则可以忽略消耗时间,但如果是大表,可能消耗就会翻倍。针对这种情况,或许可以考虑使用merge。
merge可以用于单条数据的处理,也可以用于数据的批处理。而且效率要比单独执行update+insert 操作效率要高。
1.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}
MERGE INTO 是Oracle 9i以后才出现的新的功能。那这个功能 是什么呢?
简单来说,就是:“有则更新,无则插入”从这句话里,应该可以理解到,merge into 操作一个对象’A’的时候,要有另外一个结果集做为源数据 ‘B’.‘merge into’ 将B中的数据与A中的数据按照一定条件’C’进行对比,如果 A中数据满足C条件,则进行update操作,如果不满足条件 ‘C’,则进行insert操作。
2.案例操作
2.1单表操作
创建用户的时候,需要判断用户是否存在,如果存在则更新某些字段的值,否则insert。
创建table
create table USERINFO
(
u_id NUMBER(20) not null,
u_name VARCHAR2(80),
u_email VARCHAR2(40),
updatetime DATE,
addtime DATE
);
alter table USERINFO
add constraint PRIMARY_U_ID primary key (U_ID)
using index
tablespace XINGHUO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
创建序列
-- Create sequence
create sequence SEQ_USERINFO
minvalue 1
maxvalue 99999999999999999
start with 1
increment by 1
cache 20;
merge into 操作
merge into userinfo u
using (select 4 u_id, 'ykp' u_name, 'yan095650@163.com' u_email from dual) t
on (u.u_id = t.u_id and u.u_name = t.u_name)
when matched then
update set u.updatetime = sysdate
when not matched then
insert
(u_id, u_name, u_email,addtime)
values
(seq_userinfo.nextval, t.u_name, t.u_email,sysdate);
注意,using语句中的结果集 B不可以与merge into 的对象A相同,否则,会因为结果集A,B恒等。当 on() 进行等值判断时,只可以进行update操作,不能进行insert 操作,当 on() 进行不等值判断时,只可以进行insert操作,不能进行update操作。
--这个案例是一个错误案例展示,永远都不会进行insert操作,只会进行update操作,如果u_id=3的用户不存在时,结果集A和B都是空,二者还是匹配的,此时结果集A和B还是matched的,此时的update操作只不过是没有修改任何记录而已
merge into userinfo u
using (select * from userinfo u2 where u2.u_id = 3) t
on (u.u_id = t.u_id and u.u_name = t.u_name)
when matched then
update set u.updatetime = sysdate
when not matched then
insert
(u_id, u_name, u_email, addtime)
values
(seq_userinfo.nextval, 'ykp', 'yan095650@163.com', sysdate);
2.2多表操作
多表操作也会存在的,在insert的时候如果满足某个条件,则进行更新,如果不满足则进行insert
创建table
-- Create table
create table ORDER_INFO
(
o_id NUMBER(20) not null,
o_name VARCHAR2(100),
o_u_id NUMBER(20),
o_desc VARCHAR2(100),
addtime DATE,
updatetime DATE
);
alter table ORDER_INFO
add constraint PRIMARY_O_ID primary key (O_ID)
using index
tablespace XINGHUO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
创建sequence
create sequence SEQ_ORDERINFO
minvalue 1
maxvalue 99999999999999999
start with 1
increment by 1
cache 20;
merge into 操作
--多表操作
merge into order_info o
using (select * from userinfo u where u.u_name = 'ykp') t
on (o.o_u_id = t.u_id)
when matched then
update set o.updatetime = sysdate
when not matched then
insert
(o.o_id, o.o_name, o.o_u_id, o.addtime)
values
(seq_orderinfo.nextval, t.u_name, t.u_id, sysdate);
select * from order_info;