OCP1z0-047 : 多表插入――pivoting insert（旋转插入）

这道题目的知识点是要了解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.

2、向表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

3、创建表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.

4、现在要将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)

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索pivot
， number
， values
， student插入存储过程
， ACM杭电1001
marked
pivoting insert、mysql ocp 1z0 883、1z0 047、ocp 047 解析、ocp047和051的区别，以便于您获取更多的相关知识。