ocp 试题学习中 _转子查询返回的数据行是如何用来插入多个表中

yxxs123

http://www.itpub.net/thread-1802215-1-1.html  转

多表插入――有条件的insert all [复制链接]
子查询返回的数据行是如何用来插入多个表中
small_customers
medium_customers
large_customers
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);

insert all
when sum_orders < 10000 then
into small_customers
when sum_orders >= 10000 and sum_orders < 200000 then
into medium_customers
else
into large_customers
select customer_id,sum(order_total) sum_orders
from orders
group by customer_id;
commit;
SQL> select * from small_customers;
CUSTOMER_ID SUM_ORDERS
----------- ----------
1        600
SQL> select * from medium_customers;
CUSTOMER_ID SUM_ORDERS
----------- ----------
2     130000
SQL> select * from large_customers;
CUSTOMER_ID SUM_ORDERS
----------- ----------
3     202000