[每日一题] OCP1z0-047 :2013-07-12 多表插入
我们来看下面这个例子看一下一个子查询返回的数据行是如何被用来插入多个表中的，好我们来建三个表分别是：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
2when sum_orders < 10000then
3into small_customers
4when sum_orders >=10000 and sum_orders < 200000 then
5into medium_customers
6else
7into large_customers
8selectcustomer_id,sum(order_total) sum_orders
9from orders
10group 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
----------- ----------
2130000
gyj@OCM> select * from large_customers;
CUSTOMER_ID SUM_ORDERS
----------- ----------
3202000
注意Insert关键字后面用ALL还是FIRST,视具体情况而定。
答案是： C
**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name:guoyJoe
QQ: 252803295
Email:oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM:http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
DSI&Core Search Ⅰ 群：127149411（技术:已满）
DSI&Core Search Ⅱ 群：177089463（技术:未满）
DSI&Core Search Ⅲ 群：284596437（技术:未满）
DSI&Core Search Ⅳ 群：192136702（技术:未满）
DSI&Core Search Ⅴ 群：285030382（扯淡:未满）
请勿重复加群， 加群验证信息回复:from csdn
页:
[1]