[每日一题]OCP1z0-047 :2013-07-12多表插入

[每日一题]OCP1z0-047 :2013-07-12多表插入

[每日一题]OCP1z0-047 :2013-07-12多表插入

我们来看下面这个例子看一下一个子查询返回的数据行是如何被用来插入多个表中的,好我们来建三个表分别是:small_customers、medium_customers、large_customers。我们想要按照每位消费者所下订单的总金额来将数据分别插入这些表。子查询将每一位消费者的order_total列求和来确定刻消费者的消费金额是小(所有订单的累加金额小于10000)、中等(介于10000与99999.99)还是大(大于等于100000),然后按照条件将这些行插入对应的表中。

[email protected]> create tablesmall_customers(customer_id number,sum_orders number); Table created. [email protected]> create tablemedium_customers(customer_id number,sum_orders number); Table created. [email protected]> create tablelarge_customers(customer_id number,sum_orders number); Table created. [email protected]> create tableorders(customer_id number,order_total number); Table created. [email protected]> insert into ordersvalues(1,200); [email protected]> insert into ordersvalues(1,400); [email protected]> insert into ordersvalues(2,50000); [email protected]> insert into ordersvalues(2,80000); [email protected]> insert into ordersvalues(3,200000); [email protected]> insert into ordersvalues(3,2000); [email protected]> commit; [email protected]> 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. [email protected]> Commit complete. [email protected]> select * from small_customers; CUSTOMER_ID SUM_ORDERS ----------- ---------- 1 600 gy[email protected]> select * from medium_customers; CUSTOMER_ID SUM_ORDERS ----------- ---------- 2 130000 [email protected]> select * from large_customers; CUSTOMER_ID SUM_ORDERS ----------- ---------- 3 202000

注意Insert关键字后面用ALL还是FIRST,视具体情况而定。

答案是: C

分类:默认分类 时间:2015-03-05 人气:1
本文关键词:
分享到:

相关文章

Copyright (C) quwantang.com, All Rights Reserved.

趣玩堂 版权所有 京ICP备15002868号

processed in 0.081 (s). 10 q(s)