oracle之merge into的使用

前言

• 问题
      工作上常遇到，当表中存在某一条数据就进行更新，如果不存在就得插入一条新数据，最近使用oracle就遇到了。
• 思路
      先（select）查询表中是否存在该数据，如果（exist）存在，就（update）更新该条数据，否则就（insert）插入一条新数据。
• 缺点
      上述的方式得写三条sql（select、update、insert），一次任务执行就得执行两次sql语句（select与update、select与insert），并且在并发场景下，可能会导致同样的数据，插入两次。
• 改进
      oracle中有一个merge into的关键字，可以实现一条sql语句即实现上述的任务，提高了运行效率，并且一定程度上可以防止并发插入两次相同数据。

知识

• 语法

MERGE INTO table_name alias1
USING (table | view | sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE SET col1 = col_val1, col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);

• 解释
     1.using里面的表，可以和mege into的表相同，也可以不同，常用dual表来做参数的一些拼接。

     2.when matched then和when not matched then可以只存在其中一个或同时存在。

     3.update语句和insert语句中可见省略了表名，此处就是merge into的这张主表。

实战

• 题目
     1.实现a表一天只有一条数据。

     2.如果a表中当天无数据，则insert插入一条数据，设置num为1。

     3.如果a表中当天有数据，则update更新当天的数据为num = num + 1

     4.num的最大值为5，当num值等于5时，则不更新。

• 创建表

create table a(
  id integer,
  num integer,
  currdate varchar2(10)
);
alter table a add  constraint pk_a  primary key (id);
comment on table a is '测试表';
comment on column a.id is '主键id';
comment on column a.num is '数量，最大值5';
comment on column a.currdate is '日期，yyyymmdd，一天一条数据';

• merge sql

merge into a t1
using (select to_number(to_char(current_timestamp, 'yymmddhh24miss') || substr(cast(dbms_random.value(100, 1000) as varchar2(10)), 1, 3)) id,
              to_char(sysdate, 'yyyymmdd') currdate
         from dual) t2
on (t1.currdate = t2.currdate)
when matched then
  update set t1.num = t1.num + 1 where t1.num < 5
when not matched then
  insert (id, num, currdate) values (t2.id, 1, t2.currdate)