[每日一题] OCP1z0-047 :2013-07-28多表插入――pivoting insert（旋转插入）
[每日一题] OCP1z0-047 :2013-07-28多表插入――pivoting insert（旋转插入）
这道题目的知识点是要了解Oracle 中的Insert用法
A、pivoting insert（旋转插入）
1、创建表marks_details
[html]
gyj@OCM> create table marks_details (
2 student_id number(4) not null,
3 subject_id1 number(2),
4 marks_english number(3),
5 subject_id2 number(2),
6 marks_math number(3),
7 subject_id3 number(2),
8 marks_physics number(3),
9 subject_id4 number(2),
10 marks_chemistry number(3),
11 subject_id5 number(2),
12 marks_biology number(3)
13 );
Table created.
2、向表marks_details中插入一行数据
[html]
gyj@OCM> insert into marks_details values (1001,01,90,02,80,03,85,04,95,05,75);
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> select * from marks_details;
STUDENT_ID SUBJECT_ID1 MARKS_ENGLISH SUBJECT_ID2 MARKS_MATH SUBJECT_ID3 MARKS_PHYSICS SUBJECT_ID4 MARKS_CHEMISTRY SUBJECT_ID5 MARKS_BIOLOGY
---------- ----------- ------------- ----------- ---------- ----------- ------------- ----------- --------------- ----------- -------------
1001 1 90 2 80 3 85 4 95 5 75
3、创建表marks
[html]
gyj@OCM> create table marks (
2 studnet_id number(4) not null,
3 subject_id number(2),
4 marks number(3)
5 );
Table created.
4、现在要将marks_details表的数据插入到marks表中
[html]
gyj@OCM> insert all
2 into marks values(student_id,subject_id1,marks_english)
3 into marks values(student_id,subject_id2,marks_math)
4 into marks values(student_id,subject_id3,marks_physics)
5 into marks values(student_id,subject_id4,marks_chemistry)
6 into marks values(student_id,subject_id5,marks_biology)
7 select student_id,subject_id1,marks_english,subject_id2,marks_math,subject_id3,
8 marks_physics,subject_id4,marks_chemistry,subject_id5,marks_biology
9 from marks_details;
5 rows created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> select * from marks;
STUDNET_ID SUBJECT_ID MARKS
---------- ---------- ----------
1001 1 90
1001 2 80
1001 3 85
1001 4 95
1001 5 75
这道题目就是考什么是pivoting insert？
B、Unconditional INSERT(无条件Insert all多表多行插入)
接着上面的题，继续创建表
[html]
gyj@OCM> create table marks_english (
2 studnet_id number(4) not null,
3 subject_id number(2),
4 marks number(3)
5 );
Table created.
gyj@OCM> create table marks_math (
2 studnet_id number(4) not null,
3 subject_id number(2),
4 marks number(3)
5 );
create table marks_physics (
Table created.
gyj@OCM> gyj@OCM> 2 studnet_id number(4) not null,
3 subject_id number(2),
4 marks number(3)
5 );
Table created.
gyj@OCM> gyj@OCM> create table marks_chemistry (
2 studnet_id number(4) not null,
3 subject_id number(2),
4 marks number(3)
5 );
Table created.
gyj@OCM> gyj@OCM> create table marks_biology (
2 studnet_id number(4) not null,
3 subject_id number(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_ORDERS
----------- ----------
2 130000
gyj@OCM> select * from large_customers;
CUSTOMER_ID SUM_ORDERS
----------- ----------
3 202000
注意Insert关键字后面用ALL还是FIRST,视具体情况而定。
当使用ALL关键字时,oracle会从上至下判断每一个条件,当条件满足时就执行后面的into语句
当使用FIRST关键字时,oracle会从上至下判断每一个条件,当遇到第一个满足时就执行后面的into语句。
正确答案：A
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。