oracle数据库insert all 和 insert first用法和区别

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);//大型客户表
create table orders(customer_id number,order_total number);//客户订单表
 insert into orders values(1,200);
 insert into orders values(1,400);
  insert into orders values(2,50000);
  insert into orders values(2,80000);
 insert into orders values(3,200000);
  insert into orders values(3,2000);//订单表添加数据
  select customer_id,sum(order_total) from orders group by customer_id//返回每个客户的消费总额
 
insert  first
when sum_orders<=150000 then into small_customers
when sum_orders between 10000 and 200000 then into medium_customers
else into large_customers
select customer_id,sum(order_total) sum_orders from orders group by customer_id

//根据每个客户消费金额的区间分别区分改客户是小/中/大型客户分别插入到小型/中型/大型客户表里面

(first首先检查前一个条件是否满足，如果第一个条件满足则不会插入第一个表里面重复的数据)

insert  all
when sum_orders<=150000 then into small_customers
when sum_orders between 10000 and 200000 then into medium_customers
else into large_customers
select customer_id,sum(order_total) sum_orders from orders group by customer_id

(不分先后顺序只要满足条件就执行操作)

select * from small_customers
delete from small_customers
select * from medium_customers
delete from medium_customers
select * from large_customers
delete from large_customers