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_USERINFOminvalue 1maxvalue 99999999999999999start with 1increment by 1cache 20;

merge into 操作

merge into userinfo uusing (select 4 u_id, 'ykp' u_name, '[e-mail]' u_email from dual) ton (u.u_id = t.u_id and u.u_name = t.u_name)when matched then  update set u.updatetime = sysdatewhen 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 uusing (select * from userinfo u2 where u2.u_id = 3) ton (u.u_id = t.u_id and u.u_name = t.u_name)when matched then  update set u.updatetime = sysdatewhen not matched then  insert    (u_id, u_name, u_email, addtime)  values    (seq_userinfo.nextval, 'ykp', '[e-mail]', sysdate);

2.2多表操作

多表操作也会存在的，在insert的时候如果满足某个条件，则进行更新，如果不满足则进行insert

创建table

-- Create tablecreate 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_ORDERINFOminvalue 1maxvalue 99999999999999999start with 1increment by 1cache 20;

merge into 操作

--多表操作merge into order_info ousing (select * from userinfo u where u.u_name = 'ykp') ton (o.o_u_id = t.u_id)when matched then  update set o.updatetime = sysdatewhen 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;