oracle的merge into操作详解

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;