oracle merge和批量insert实操

create table small_customers
(customer_id	number,
 sum_orders		number)
;

create table medium_customers
(customer_id	number,
 sum_orders		number)
;

create table large_customers
(customer_id	number,
 sum_orders		number)
;

select * from small_customers ;

select * from medium_customers ;

select * from large_customers ;

insert all
when sum_orders < 10000 then
into small_customers
when sum_orders >= 10000 and sum_orders < 100000 then
into medium_customers
else
into large_customers
select customer_id, sum(order_total) sum_orders
from oe.orders
group by customer_id ;

select * from small_customers ;

select * from medium_customers ;

select * from large_customers ;

create table dept60_bonuses
(employee_id number
,bonus_amt number);

insert into dept60_bonuses values (103, 0);

insert into dept60_bonuses values (104, 100);

insert into dept60_bonuses values (105, 0);

commit;

select employee_id, last_name, salary
from hr.employees
where department_id = 60 ;

select * from dept60_bonuses;

merge into dept60_bonuses b
using (select employee_id, salary, department_id
         from hr.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);

select * from dept60_bonuses;

rollback;

create table subs(msid number(9),
                      ms_type char(1),
                      areacode number(3)
                      );

create table acct(msid number(9),
                      bill_month number(6),
                      areacode   number(3),
                       fee        number(8,2) default 0.00);

insert into subs values(905310001,0,531);
insert into subs values(905320001,1,532);
insert into subs values(905330001,2,533);
commit;

select * from subs;
select * from acct;

--  insert
 merge into acct a
     using subs b on (a.msid=b.msid)
   when MATCHED then
        update set a.areacode=b.areacode
   when NOT MATCHED then
        insert(msid,bill_month,areacode)
        values(b.msid,'200702',b.areacode);
  commit;

   select * from subs;
select * from acct;
  --update
  merge into acct a
     using subs b on (a.msid=b.msid)
   when MATCHED then
        update set a.areacode=b.areacode
        where b.ms_type=0;
  commit;
 select * from subs;
select * from acct;
delete from     subs;
delete from     acct;

merge into acct a
using subs b
on (a.msid = b.msid)
when MATCHED then
  update set a.areacode = b.areacode
when NOT MATCHED then
  insert
    (msid, bill_month, areacode)
  values
    (b.msid, '200702', b.areacode);
commit;
select * from subs;
select * from acct;

 merge into acct a
 using subs b
 on (a.msid = b.msid)
 when MATCHED then
   update set a.areacode = b.areacode
   delete where (b.ms_type != 0);
 commit;

 select * from subs;
select * from acct;