[每日一题] OCP1z0-047 :2013-07-28 多表插入――pivoting insert（旋转插入）....................17

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

Oracle 中的Insert用法

A丶pivoting insert（旋转插入）

1丶创建表marks_details

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.

丶向表marks_details中插入一行数据

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

丶创建表marks

gyj@OCM> create table marks (
  2   studnet_id number(4) not null,
  3   subject_id number(2),
  4   marks number(3)
  5   );

Table created.

丶现在要将marks_details表的数据插入到marks表中

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多表多行插入)

接着上面的题，继续创建表

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)

对于有条件的多表插入可考参我的博客，有测试例子，具体如下。
http://blog.csdn.net/guoyjoe/article/details/8607848

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

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：252803295

学习交流QQ群：
DSI&Core Search  Ⅰ 群：127149411（技术:已满）
DSI&Core Search  Ⅱ 群：177089463（技术:未满）
DSI&Core Search  Ⅲ 群：284596437（技术:未满）
DSI&Core Search  Ⅳ 群：192136702（技术:未满）
DSI&Core Search  Ⅴ 群：285030382（闲聊:未满）

MAIL：[e-mail]

BLOG： http://blog.csdn.net/guoyjoe

WEIBO：

ITPUB： http://www.itpub.net/space-uid-28460966.html

OCM：