Oracle数据库中的merge语句

在Oracle数据库的使用中，向表中插入数据时，经常有这样的需求：如果待插入的记录表中已经存在，就用新记录的值更新原记录；如果不存在，就插入新记录。这时候，就需要用merge语句。通过merge语句能够避免自己手写好多if判断，程序简洁，更好维护。

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>;

这个语句的执行过程：
对于source_table中的每一条记录，结合target_table判断该记录是否满足search_condition：如果满足，执行MATCHED部分的update语句；如果不满足，执行NOT MATCHED部分的insert语句。
其中的upate和insert语句都可以指定where条件，来控制只更新或者插入满足条件的记录。
此外，update语句的部分还可以指定一个可选的带where条件的delete语句。它用来删除这样的记录：符合search_condition，而且在执行update操作之后，各个字段的值满足delete where语句中的条件。

例子

简单的merge

-- create tablesCREATE 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;
The following INSERT statements insert sample data into the members and member_staging tables:-- 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 tableINSERT 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');
When updating data from the members table to member_staging table, we should perform the following actions:-- merge the dataMERGE INTO member_staging xUSING (SELECT member_id, first_name, last_name, rank FROM members) yON (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语句的功能如下图：

image.png

将members表中的记录合并到member_staging表。对于某条在members表中的记录，如果在member_staging表中能找到member_id相同的记录，就执行带条件的更新语句：用members表中记录的值作为新值更新到member_staging表中的对应记录。对于某条在members表中的记录，如果在member_staging表中找不到member_id相同的记录，就将该记录插入到member_staging表中。

oracle官网的例子

Merging into a Table: Example
The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM employees e, orders o   WHERE e.employee_id = o.sales_rep_id   GROUP BY e.employee_id); 

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100MERGE INTO bonuses D   USING (SELECT employee_id, salary, department_id FROM employees   WHERE department_id = 80) S   ON (D.employee_id = S.employee_id)   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)     VALUES (S.employee_id, S.salary*.01)     WHERE (S.salary <= 8000);SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        179        620
        173        610
        165        680
        166        640
        164        720
        172        730
        167        620
        171        740

这个例子的功能就是计算奖金，原来是想只要有销售记录的员工每人发100。后来人力决定，修改奖金计算的规则，只有薪水8000以下的员工能拿到奖金，并设置了新的奖金金额。通过上面的一个merge语句就实现了对奖金表数据的修正。

作者：SpaceCat
链接：https://www.jianshu.com/p/16af7dc89934