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

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/9314815

我们来看下面这个例子看一下一个子查询返回的数据行是如何被用来插入多个表中的，好我们来建三个表分别是：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

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
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