Oracle 19C学习 - 19. MERGE语句

Merge语句的作用

Merge语句可以根据不同条件，获取插入、更新、删除数据表中的行，然后从一个或者多个数据源头对表进行更新或向表中插入行。

Merge语句的语法

MERGE INTO 表名
USING 表名/视图/子查询 ON 匹配条件
WHEN MATCHED THEN
更新、删除操作(UPDATE / DELETE)
WHEN NOT MATCHED THEN
更新、删除、插入操作(UPDATE / DELETE / INSERT)

语句结束后，根据需要确定事务情况：
COMMIT 或者 ROLLBACK

CREATE TABLE DEPTS_EMP
(
    employee_id NUMBER,
    bonus NUMBER
);

INSERT INTO DEPTS_EMP VALUES (103, 0);
INSERT INTO DEPTS_EMP VALUES (104, 100);
INSERT INTO DEPTS_EMP VALUES (105, 0);

SELECT EMPLOYEE_ID, LAST_NAME, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;

MERGE INTO depts_emp d
USING (SELECT EMPLOYEE_ID, LAST_NAME, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 60) e
ON (d.employee_id = e.employee_id)

WHEN MATCHED THEN
    -- 将depts_emp的bonus为0的记录，更新为employees的salary * 0.2
    UPDATE 
    SET d.bonus = e.salary * 0.2
    WHERE d.bonus = 0
    
    -- depts_emp中工资大于7500的记录删除
    DELETE WHERE (e.salary > 7500)
    
WHEN NOT MATCHED THEN 
    -- 将employees中salary小于等于7500的记录插入到表depts_emp
    INSERT (d.employee_id, d.bonus) VALUES (e.employee_id, e.salary * 0.1 ) WHERE (e.salary <= 7500);
    
COMMIT;