Oracle-merge语句学习

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.测试数据

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