扩展DDL和DML语句

　　1 背景和目标

　　本章内容所用的实例表是第5 章—层次查询所用到的s_emp表。结构如下：

　　Name Type Nullable Default Comments

　　-------------- ------------- -------- ------- --------

　　ID NUMBER(7)

　　LAST_NAME VARCHAR2(25)

　　FIRST_NAME VARCHAR2(25) Y

　　USERID VARCHAR2(8) Y

　　START_DATE DATE Y

　　COMMENTS VARCHAR2(255) Y

　　MANAGER_ID NUMBER(7) Y

　　TITLE VARCHAR2(25) Y

　　DEPT_ID NUMBER(7) Y

　　SALARY NUMBER(11,2) Y

　　COMMISSION_PCT NUMBER(4,2) Y

　　本章目标：

　　1.能够描述多表insert的特性，并能够明确其使用场合。

　　2.掌握和使用不同类型的多表insert操作：

　　2-1.无条件的多表insert all

　　2-2.带条件的多表insert all

　　2-3.带条件的多表insert first

　　2-4．Pivoting insert

　　3.能够建立和使用external tables

　　关键字：conditional,unconditional,pivote,external,dictionary等。

　　背景描述：

　　本章内容主要讨论，如何使用一条insert语句，向一个目标表插入多行记录，或者向多个目标表中插入多条记录，这些数据源可以来自于一个表或多个表，同过select 语句获得数据源记录。常用于一些比较复杂的需求，如数据仓库的ETL 中。多表insert 在oracle9i中被加入。

　　2 经常使用的扩展

　　1.一般的insert 操作。

　　使用语法insert into table_name[(column[,column...])] values (value[,value…])的insert语句，每条insert只能插入到目标表中一条指定的数据。如果有很多行需要插入，而且这些数据来源于别的表或多个表之间提取的数据，那么这就不能满足要求了。因此oracle提供了一个扩展，见下面描述。

　　2.insert into table_name[(column[,column…])] select_statement

　　Oracle提供了一个扩展，将通过select语句查询的记录插入到指定表的所有列或者指定列中。每次只能插入到一个表中，但是性能已经比写很多条insert语句要高了。另外oracle还提供了直接create table table_name as select….(as不可以少！)，实现直接创建表并且插入数据，依赖于后面的select语句的结果，比如只复制表结构，约束不赋值，那么可以使用Create table new_table as select * from source_table where 1=0; --1=0不查询记录，只复制基本结构。

　　比如有个需求：表mxt 新增了一个列time，那么这个新增的列在oracle 中会被放在所有列的后面，现在想把这个time列放在第2 个位置，那么可以使用这个操作：

　　下面具体说明oracle 对insert 操作的其他扩展，实现目标，一条insert 操作，插入到多个表中，每个表插入多条记录。

　　3向多个目标insert

　　这里有四种类型：

　　1. 无条件的insert

　　2. 带条件的insert all

　　3. 带条件的insert first

　　4. pivoting insert

　　多表insert使用限制：

　　1. 只能对table使用多表insert，不能对视图或物化视图使用。

　　2. 不能对远程表进行这个插入操作。

　　3. 在做多表insert操作，不能指定一个表的集合表达式。

　　4. 多表insert中的的into目标表加在一起的列数不能超过999 个。

　　语法：

　　注：如果没有conditional_insert_clause,必须有all，即insert all 开头。如果有conditional_insert_clause,则all和first只能取其一。

　　3.1 无条件的insert操作

　　这里所说的无条件的insert操作有两种，一种就是单表insert into dest_table select..，一种是向多个目标中insert，那么使用insert all into dest_table1,dest table2…select…

　　目标表只有一个：insert into dest_table[colum(,column…)] select …

　　目标表有多个: insert all --可以替代上面的操作

　　Into dest_table1[column(,column…)] [values(…)]

　　Into dest_table2[column(,column…)] [values(…)]

　　…

　　Select_statement—values中指定的是select结果中的列

　　目标表只有一个的，那么插入目标表中后面可以跟指定的列，依赖于查询出来的数目以及类型，没有values指定。如果不指定列，那么表示目标表的列类型和顺序与select语句查询的结果完全一致。

　　对于目标表有多个的必须使用insert all,all 不能省略。与带条件的不同，带条件的all 可以省略，后面讲解。这里的每个目标表可以不指定列，依赖于values 中指定的select语句查询出来的结果中的列。如果目标表不指定列，那么表示目标表的列的顺序类型和values 中指定的一致，而values 中的列则依赖于select 语句查询出的结果。当然也可以省略values,那么表示目标表中的列顺序和类型和select语句查询出来的结果完全一致，否则报错。Insert all可以替代目标表只有一个的操作。

　　建议不要省略目标表中的列和values，增强可读性。

　　例如：

　　这里总共插入了75行记录，select语句查询的记录为25 行，因为是无条件的插入，那么对每个目标表都插入了25行记录，所以总共插入75 行记录。

　　3.2 带条件的insert all

　　带条件的insert all与不带条件的insert all相比可以省略all，但是不建议省略。另外的区别就是插入到目标表之前，有条件判断，从查询的结果行中筛选出满足条件的记录，然后向目标表中插入，而不在是无条件的insert all中的全量插入。

　　语法是：

　　Insert all

　　When 条件 then

　　Into dest[指定列] values(查询中的列)

　　When 条件 then

　　…

　　[else]

　　Into dest[指定列] values(查询中的列)

　　Select语句.

　　是否可以省略目标表中的指定列和values 那么与无条件的insert all 一致。条件中的列那么必须与查询的结果列名保持一致，如果select中使用别名，那么条件中也必须使用别名，和values中的规则一样。Else可以指定，如果不指定else，则按照条件筛选，如果指定else，则表示与前面全不匹配的插入到else后面的目标表中。带条件的insert all与insert first的区别在于只按条件对查询的结果进行筛选，不会不考虑前面已经匹配过的记录，每次都是全量匹配，而insert first是后面的when条件insert操作会对前面已经匹配过的记录不予考虑。

　　例如：

　　总共插入26行，而查询结果为25行，emp_2中插入了一条与emp_1中重复的记录。

　　3.3 带条件的insert first

　　Insert first只有带条件的，没有不带条件的。

　　语法只要将insert all中的all改为first就可以了。这里的first不可以省略。省略那么默认就是all。

　　它的插入规则是如果前面的when 匹配过的，下一个when 会自动不考虑上面已经匹配过的行记录。比如将上面16.3.2中的例子改为：

　　插入25行，因为when s_last_name like 'M%'不考虑前面已经匹配的行，所以emp_2中就没有那条与插入emp_1中的重复行。

　　3.4 pivoting insert（旋转插入）

　　这节主要讨论使用pivoting insert 实现将非关系性表记录转换为关系型表中存储。Pivot 旋转是OLAP 中的一个基本改变，提供多维度数据分析。比如一个表为sales_source_data,里面的列为

　　employee_id number,week_id number,sales_mon number,sales_tue number,sales_wed number,sales_thur number,sales_fri number 员工id，周id，其他的是工作日的每天的销售记录。

　　现在要把数据转到sales_info表中，这个表的列为employee_id,week_id,sales。

　　要实现这个转换可以使用pivoting insert 操作。和insert all类似，只不过这里的多表都是同一个表。

　　insert all

　　into sales_info values(employee_id,week_id,sales_mon) --分别按每个工作日插入

　　into sales_info values(employee_id,week_id,sales_tue)

　　into sales_info values(employee_id,week_id,sales_wed)

　　into sales_info values(employee_id,week_id,sales_thur)

　　into sales_info values(employee_id,week_id,sales_fri)

　　select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri

　　from sales_source_data;

　　比如原来的sales_source_data 中只有一条记录，那么插入到sales_info 中就变成了5 条记录。

　　select * from sales_source_data;

select * from sales_info;