[每日一题] 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;
