[每日一题] OCP1z0-047 :2013-07-28多表插入??pivoting insert（旋转插入）(二)
mber(2),
4 marks number(3)
5 );
Table created.
gyj@OCM> insert all
2 into marks_english values(student_id,subject_id1,MARKS_ENGLISH)
3 into marks_math values(student_id,subject_id2,MARKS_MATH)
4 into marks_physics values(student_id,subject_id3,MARKS_PHYSICS)
5 into marks_chemistry values(student_id,subject_id4,MARKS_CHEMISTRY)
6 into marks_biology values(student_id,subject_id5,MARKS_BIOLOGY)
7 select STUDENT_ID,SUBJECT_ID1,MARKS_ENGLISH,SUBJECT_ID2,MARKS_MATH,SUBJECT_ID3,MARKS_PHYSICS,SUBJECT_ID4,MARKS_CHEMISTRY,SUBJECT_ID5,MARKS_BIOLOGY
8 from marks_details;
5 rows created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> select * from marks_english;
STUDNET_ID SUBJECT_ID MARKS
---------- ---------- ----------
1001 1 90
gyj@OCM> select * from marks_math;
STUDNET_ID SUBJECT_ID MARKS
---------- ---------- ----------
1001 2 80
gyj@OCM> select * from marks_physics;
STUDNET_ID SUBJECT_ID MARKS
---------- ---------- ----------
1001 3 85
gyj@OCM> select * from marks_chemistry;
STUDNET_ID SUBJECT_ID MARKS
---------- ---------- ----------
1001 4 95
gyj@OCM> select * from marks_biology;
STUDNET_ID SUBJECT_ID MARKS
---------- ---------- ----------
1001 5 75
C、ConditionalALL (INSERT有条件的insertall)
D、Conditional FIRST (INSERT有条件的insertall)
我们来看下面这个例子看一下一个子查询返回的数据行是如何被用来插入多个表中的，好我们来建三个表分别是：small_customers、medium_customers、large_customers。我们想要按照每位消费者所下订单的总金额来将数据分别插入这些表。子查询将每一位消费者的order_total列求和来确定刻消费者的消费金额是小（所有订单的累加金额小于10000）、中等（介于10000与99999.99）还是大(大于等于100000),然后按照条件将这些行插入对应的表中。
[html]
gyj@OCM> create table small_customers(customer_id number,sum_orders number);
Table created.
gyj@OCM> create table medium_customers(customer_id number,sum_orders number);
Table created.
gyj@OCM> create table large_customers(customer_id number,sum_orders number);
Table created.
gyj@OCM> create table orders(customer_id number,order_total number);
Table created.
gyj@OCM> insert into orders values(1,200);
gyj@OCM> insert into orders values(1,400);
gyj@OCM> insert into orders values(2,50000);
gyj@OCM> insert into orders values(2,80000);
gyj@OCM> insert into orders values(3,200000);
gyj@OCM> insert into orders values(3,2000);
gyj@OCM> commit;
gyj@OCM> insert all
2 when sum_orders < 10000 then
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 select customer_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_ORDER