在本教程中将学习如何使用Oracle MERGE语句来执行更新或基于指定条件插入数据。

Oracle MERGE语句简介

Oracle MERGE语句从一个或多个源表中选择数据并更新或将其插入到目标表中。 MERGE语句可指定一个条件来确定是更新数据还是将数据插入到目标表中。

以下说明了Oracle MERGE语句的语法：

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语句根据指定的条件更新或插入数据。