我们来看下面这个例子看一下一个子查询返回的数据行是如何被用来插入多个表中的，好我们来建三个表分别是：small_customers、medium_customers、large_customers。我们想要按照每位消费者所下订单的总金额来将数据分别插入这些表。子查询将每一位消费者的order_total列求和来确定刻消费者的消费金额是小（所有订单的累加金额小于10000）、中等（介于10000与99999.99）还是大(大于等于100000),然后按照条件将这些行插入对应的表中。
gyj@OCM> create tablesmall_customers(customer_id number,sum_orders number);
Table created.
gyj@OCM> create tablemedium_customers(customer_id number,sum_orders number);
Table created.
gyj@OCM> create tablelarge_customers(customer_id number,sum_orders number);
Table created.
gyj@OCM> create tableorders(customer_id number,order_total number);
Table created.
gyj@OCM> insert into ordersvalues(1,200);
gyj@OCM> insert into ordersvalues(1,400);
gyj@OCM> insert into ordersvalues(2,50000);
gyj@OCM> insert into ordersvalues(2,80000);
gyj@OCM> insert into ordersvalues(3,200000);
gyj@OCM> insert into ordersvalues(3,2000);
gyj@OCM> commit;
gyj@OCM> insert all
2 when sum_orders < 10000then
3 into small_customers
4 when sum_orders >=10000 and sum_orders < 200000 then
5 into medium_customers
6 else
7 into large_customers
8 selectcustomer_id,sum(order_total) sum_orders
9 from orders
10 group by customer_id;
commit;
3 rows created.
gyj@OCM>
Commit complete.
gyj@OCM> select * from small_customers;
CUSTOMER_ID SUM_ORDERS
----------- ----------
1 600
gyj@OCM> select * from medium_customers;
CUSTOMER_ID SUM_ORDERS
----------- ----------
2 130000
gyj@OCM> select * from large_customers;
CUSTOMER_ID SUM_ORDERS
----------- ----------
3 202000
注意Insert关键字后面用ALL还是FIRST,视具体情况而定。
答案是： C
查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/