Oracle合并数据

merge into target_table 
using source_table 
on search_condition
    when matched then
        update set col1 = value1, col2 = value2,...
        where <update_condition>
        [delete where <delete_condition>]
    when not matched then
        insert (col1,col2,...)
        values(value1,value2,...)
        where <insert_condition>;

下面来仔细看看上面merge语句的语法：

首先，指定要在into子句中更新或插入的目标表(target_table)。
其次，指定要更新或插入using子句中的数据源(source_table)。
第三，指定合并操作在on子句中更新或插入的搜索条件。

对于目标表中的每一行，oracle都会评估搜索条件：

如果结果为true，则oracle使用源表(source_table)中的相应数据更新该行。
如果任何行的结果为false，则oracle将源表(source_table)中相应的行插入到目标表(target_table)中。

当想要在单个操作中组合多个insert，update和delete语句时，merge语句变得很方便。

因为merge是确定性语句，所以不能在同一个merge语句中多次更新目标表的同一行。

可以将一个可选的delete where子句添加到matched子句中，以在合并操作之后进行清理。 delete子句只删除目标表中与on和delete where子句匹配的行。

oracle merge前提条件

要执行merge语句，必须在源表上具有insert和update对象权限。如果使用delete子句，则还必须在目标表上具有delete对象特权。

oracle merge示例

假设有两个表：members和member_staging。

每当有一个新会员信息时，则插入一个新的行记录到members表。然后，members表中的数据将与member_staging表的数据合并。

以下语句用于创建members和member_staging表：

create table members (
    member_id number primary key,
    first_name varchar2(50) not null,
    last_name varchar2(50) not null,
    rank varchar2(20)
);

create table member_staging as 
select * from members;

使用以下insert语句将示例数据插入到members和member_staging表中：

-- insert into members table    
insert into members(member_id, first_name, last_name, rank) values(1,'abel','wolf','gold');
insert into members(member_id, first_name, last_name, rank) values(2,'clarita','franco','platinum');
insert into members(member_id, first_name, last_name, rank) values(3,'darryl','giles','silver');
insert into members(member_id, first_name, last_name, rank) values(4,'dorthea','suarez','silver');
insert into members(member_id, first_name, last_name, rank) values(5,'katrina','wheeler','silver');
insert into members(member_id, first_name, last_name, rank) values(6,'lilian','garza','silver');
insert into members(member_id, first_name, last_name, rank) values(7,'ossie','summers','gold');
insert into members(member_id, first_name, last_name, rank) values(8,'paige','mcfarland','platinum');
insert into members(member_id, first_name, last_name, rank) values(9,'ronna','britt','platinum');
insert into members(member_id, first_name, last_name, rank) values(10,'tressie','short','bronze');

-- insert into member_staging table
insert into member_staging(member_id, first_name, last_name, rank) values(1,'abel','wolf','silver');
insert into member_staging(member_id, first_name, last_name, rank) values(2,'clarita','franco','platinum');
insert into member_staging(member_id, first_name, last_name, rank) values(3,'darryl','giles','bronze');
insert into member_staging(member_id, first_name, last_name, rank) values(4,'dorthea','gate','gold');
insert into member_staging(member_id, first_name, last_name, rank) values(5,'katrina','wheeler','silver');
insert into member_staging(member_id, first_name, last_name, rank) values(6,'lilian','stark','silver');

将members表中的数据更新到member_staging表时，应该执行以下操作：

更新成员id为1,3,4和6的行记录，因为表中这些成员的排名或姓氏是不同的。
要插入成员id为7到10的行记录，这是因为这些行存在于members表中，但不存在于member_staging表中。

总共有8行数据需要合并。参考以下图示 -

以下是一次性执行所有这些操作的merge语句。

merge into member_staging x
using (select member_id, first_name, last_name, rank from members) y
on (x.member_id  = y.member_id)
when matched then
    update set x.first_name = y.first_name, 
                        x.last_name = y.last_name, 
                        x.rank = y.rank
    where x.first_name <> y.first_name or 
           x.last_name <> y.last_name or 
           x.rank <> y.rank 
when not matched then
    insert(x.member_id, x.first_name, x.last_name, x.rank)  
    values(y.member_id, y.first_name, y.last_name, y.rank);

merge语句根据member_id列中的值(参见上面的on子句)，将member表中的每一行与member_staging表中的每一行进行比较。

如果两个表的member_id列中的值相等，merge语句只有在两个表的first_name,last_name或rank列的不值时，才将members表中的first_name,last_name或rank列的不值作为member_staging对应列的值来更新，否则它将members的行直接插入member_staging表。

oracle按照预期返回了8行合并数据。

在本教程中，您已学习如何使用oracle merge语句根据指定的条件更新或插入数据。