1.语法格式
MERGE <hint>
INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];
2.测试数据
MERGE <hint>
INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];
2.测试数据
create table dept_bonuses
(employee_id number,
bonus_amt number);
insert into dept_bonuses values(103,0);
insert into dept_bonuses values(104,100);
insert into dept_bonuses values(105,0);
commit;
create table employees
(employee_id number,
department_id number,
last_name varchar2(20),
salary number);
insert into employees values(103,60,'A',9000);
insert into employees values(104,60,'B',6000);
insert into employees values(105,60,'C',4800);
insert into employees values(106,60,'D',9800);
insert into employees values(107,60,'E',4200);
commit;
kin@KIN>select * from dept_bonuses;
EMPLOYEE_ID BONUS_AMT
----------- ----------
103 0
104 100
105 0
kin@KIN>select * from employees;
EMPLOYEE_ID DEPARTMENT_ID LAST_NAME SALARY
----------- ------------- -------------------- ----------
103 60 A 9000
104 60 B 6000
105 60 C 4800
106 60 D 9800
3.测试merge语句
merge into dept_bonuses b
using (
select employee_id,salary,department_id
from employees
where department_id = 60) e
on (b.employee_id = e.employee_id)
when matched then
update set b.bonus_amt = e.salary * 0.2
where b.bonus_amt = 0
delete where (e.salary > 7500)
when not matched then
insert (b.employee_id,b.bonus_amt)
values (e.employee_id,e.salary * 0.1)
where (e.salary < 7500);
kin@KIN>select * from dept_bonuses;
EMPLOYEE_ID BONUS_AMT
----------- ----------
104 100
105 960
107 420