Oracle MERGE语句

目的

使用MERGE语句从一个或多个数据源中选择数据插入到一个表或视图中。你可以指定条件，以确定是否更新或插入到目标表或视图。

MERGE语句是一种方便结合多个操作的方式，它可以让你避免多个INSERT，UPDATE和DELETEDML语句。

MERGE是一种确定性的语句，在同一个MERGE语句中不能多次更新目标表的同一行。

前提

       你必须有目标表的INSERT和UPDATE对象权限和源表的SELECT对象权限。要指定merge_update_clause的DELETE从句，也必须要有目标表的DELETE对象权限。

 

语法

MERGE [ hint ]
   INTO [ schema. ] { table | view } [ t_alias ]
   USING { [ schema. ] { table | view }
         | subquery 
         } [ t_alias ]
   ON ( condition )
   [ merge_update_clause ]
   [ merge_insert_clause ]
   [ error_logging_clause ] ;

注意：虽然这个SQL语句有UPDATE、INSERT和LOGGING从句，但是只能有一个分号(;)。

• merge_update_clause从句

WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]

• merge_inser_clause

WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr [, expr ]... | DEFAULT })
[ where_clause ]

• error_logging_clause

LOG ERRORS 
  [ INTO [schema.] table ]
  [ (simple_expression) ]
  [ REJECT LIMIT { integer | UNLIMITED } ]

语义

INTO 子句

使用into子句指定要插入和更新的目标表或视图。

USING 子句

使用using子句指定插入和更新的数据源。数据源可以一张表、视图或是子查询。

ON子句

使用on子句指定merge操作条件是插入或更新。对于目标表的搜索条件为真时，Oracle数据库从源表获取数据更新到目标表的相应行。如果条件不成立的任意行，则数据库插入基于来源表相应的行到目标表。

merge_update_clause

 merge_update_clause指定目标表的新列值。如果ON子句的条件为真，Oracle执行更新。如果update子句被执行，那么所有目标表上定义的更新触发器都被激活。

如果你想数据库只有指定条件为真时才执行更新操作，则需要指定 where_clause。条件可以参考数据源或目标表。如果条件为假，则合并表时数据库跳过更新操作。

指定 DELETE where_clause删除表中正在填充或更新的数据。该从句只影响目标表中被合并操作更新的行。DELETE WHERE条件求被UPDATE SET ... WHERE计算后的更新值，而不是原始值。如果目标表的行满足DELETE条件，但不包含在ON子句中所定义的条件，该行是不被删除的。定义在目标表上的删除触发器在删除每一行后都会被触发。

merge_insert_clause

如果ON子句的条件为假， merge_insert_clause 指定值插入到目标表的列上。如果INSERT子句被执行，那么定义在目标表上的所有插入触发器被激活。如果省略了INSERT关键字后列的列表，则目标的列数必须与VALUES子句中值的列数匹配。

为了将来源表的所有行都插入目标表，可以在ON子句中使用常量过滤条件。一个常量过滤条件的例子是ON(1=0)。Oracle承认这样一个常量条件，并无条件插入源表所有行到目标表。这种方法是不同于省略 merge_update_clause，在这样情况下，数据库仍然执行关联。使用常量过滤条件，没有关联被执行。

如果想要数据执行插入操作仅仅是指定条件为真时，则需要指定 where_clause 。该条件只能是参考数据源表。Oracle数据库跳过所有行条件为假的插入操作。

error_logging_clause

在合并语句中error_logging_clause具有与INSERT语句相同的行为。

 

Examples

创建员工表emp，部门表depart和奖金表bonuses。

创建员工表

create  table emp(
employee_id number,
employee_code varchar2(30),
employee_name varchar2(50),
brithdate date,
depart_id number);

insert into emp values (1,'0001','KIT',date'1996-10-18',1);
insert into emp values (2,'0002','张三',date'2013-10-3',1);
insert into emp values (3,'0003','李四',date'2003-11-23',2);
insert into emp values (4,'0004','王五',date'2001-1-06',2);
insert into emp values (5,'0005','小六',date'2011-09-08',3);

创建部门表

create  table depart (
depart_id number,
depart_code varchar2(30),
depart_name varchar2(50));

insert into depart values  (1,'10','市场部');
insert into depart values  (2,'20','研发部');
insert into depart values (3,'30','拓展部');
insert into depart values  (4,'40','后勤部');

创建奖金表create table bonuses (
employee_id number,
bonuses number not null);

 

创建日志表
create table logs(
log_id number,
log_message varchar2(200));

1.按照部门的发放奖金，市场部100，研发部200，拓展部200，后勤部80。使用merge语句插入各部门员工的奖金。

merge into bonuses b
using (select e.employee_id, e.employee_name, d.depart_id, d.depart_code
         from emp e, depart d
        where e.depart_id = d.depart_id) a
on (b.employee_id = a.employee_id)
when not matched then
  insert
    (b.employee_id, b.bonuses)
  values
    (a.employee_id,
     decode(a.depart_code, '10', 100, '20', 200, '30', 200, '40', 80));

查询奖金表

SQL> select * from bonuses;
 
EMPLOYEE_ID    BONUSES
----------- ----------
          5        200
          4        200
          3        200
          1        100
          2        100

 

2.市场不所有员工，在原来奖金的基础上加20%。

merge into bonuses b
using (select e.employee_id, e.employee_name, d.depart_id, d.depart_code
         from emp e, depart d
        where e.depart_id = d.depart_id
          and d.depart_code = '10') a
on (b.employee_id = a.employee_id)
when matched then
  update set b.bonuses = b.bonuses + b.bonuses * 0.2;

查询奖金表

SQL> select *from bonuses;
 
EMPLOYEE_ID    BONUSES
----------- ----------
          5        200
          4        200
          3        200
          1        120
          2        120

市场部的员工的奖金有原来的100变成了120。

3.删除研发部员工张三的奖金，其他员工奖金减少10%。

merge into bonuses b
using (select e.employee_id, e.employee_name, d.depart_id, d.depart_code
         from emp e, depart d
        where e.depart_id = d.depart_id
          and d.depart_code = '20') a
on (b.employee_id = a.employee_id)
when matched then
  update
     set b.bonuses = b.bonuses - b.bonuses * 0.1
  delete where b.employee_id = 3;

 

查询奖金：

SQL> select *from bonuses;
 
EMPLOYEE_ID    BONUSES
----------- ----------
          5        200
          4        180
          1        120
          2        120

研发部现在只有李四有奖金，由原来的200变为180.。

 

注意：使用DELETE子句有以下条件限制。

a.删除记录比较在目标表中存在。

b.必须满足On子句的条件。

c.删除的记录必须包含update set ...子句中。

 

4.研发部门所有有奖金的员工，奖金加30%，没有奖金的员工加200.

merge into bonuses b
using (select e.employee_id, e.employee_name, d.depart_id, d.depart_code
         from emp e, depart d
        where e.depart_id = d.depart_id
          and d.depart_code = '20') a
on (b.employee_id = a.employee_id)
when matched then
  update set b.bonuses = b.bonuses + b.bonuses * 0.3
when not matched then
  insert (b.employee_id, b.bonuses) values (a.employee_id, 200);

 

查询奖金表

SQL> select *from bonuses;
 
EMPLOYEE_ID    BONUSES
----------- ----------
          5        200
          4        234
          3        200
          1        120
          2        120

研发部张三原来没有奖金，现在为200。李四由180变成234.

 

5.创建创建错误日志表

SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('bonuses', 'errlog');
将错误日志表与奖金关联在一起，这样在做奖金表错误时。如果发生错误，就会将错误信息记录到日志表。

例如，将拓展部员工的奖金置空。

merge into bonuses b
using (select e.employee_id, e.employee_name, d.depart_id, d.depart_code
         from emp e, depart d
        where e.depart_id = d.depart_id
          and d.depart_code = '30') a
on (b.employee_id = a.employee_id)
when matched then
  update set b.bonuses = null
log errors into errlog('bonuses_error') reject limit 20;

查看奖金表

SQL> select *from bonuses;
 
EMPLOYEE_ID    BONUSES
----------- ----------
          5        200
          4        234
          3        200
          1        120
          2        120

发现拓展部的员工小刘的奖金还是200，并没有置空。

查看错误日志表

SQL> select ORA_ERR_NUMBER$,ORA_ERR_MESG$ from errlog;
 
ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- --------------------------------------------------------------------------------
           1407 ORA-01407: 无法更新 ("GDSHEC"."BONUSES"."BONUSES") 为 NULL