Extensions to DML and DDL Statements（pivoting insert）

多表 INSERT 语句概述
•INSERT...SELECT 语句能够作为单个DML语句的一部分，用于插入行到多表中
•多表 INSERT 语句能够被用在数据仓库系统中，从一个或多个操作源转移数据到一组目的表中
•下面的方法提供重要的性能改进：
–单个 DML 与多 INSERT...SELECT 语句相对
–单个 DML 与一个用 IF...THEN 语法做多插入的过程相对

•Oracle9i 引入下面的多表插入语句的类型
–无条件 INSERT
–条件 ALL INSERT
–条件 FIRST INSERT
–旋转 INSERT(Pivoting INSERT)

2.多表 INSERT 语句
•多表 INSERT 语法1(无条件insert)：
 INSERT ALL 
   INTO子句 VALUES子句 
   [INTO子句 VALUES子句 ...] 
  子查询 ;
•多表 INSERT 语法2(条件insert)：
 INSERT [ALL|FIRST] 
   WHEN 条件 THEN INTO子句 VALUES子句  
   [WHEN..THEN.. ...]
   [ELSE INTO子句 VALUES子句] 
 子查询 ;
注1：在一个单个多表insert语句中，最多可以包含127个when..then..子句
注2：如果在条件insert语句中指定all，则对于每个when子句，只要条件为真，就执行相应的into子句
注3：如果指定first，则按顺序计算每个when子句，当遇到第一个条件为真的when子句就执行相应的into子句，而跳过其后的其它when子句
注3：对于子查询结果集中的每一行，如果没有when子句为真，则Oracle不插入该行。如果指定了else子句，则Oracle会执行else子句相关的插入操作(else子句相应的into子句)

•多表insert的限制
–只能在表上执行多表insert，而不能在视图、物理视图上执行
–不能在一个远程表上执行多表插入操作
–在一个多表insert中，所有指定的列的总数不能超过999列
–在执行一个多表insert时，不能指定一个表集合表达式(table collection expression)

3.无条件insert all
•示例：
INSERT ALL
 INTO sal_history VALUES(EMPID,HIREDATE,SAL)
 INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,salary SAL, manager_id MGR
FROM employees 
WHERE employee_id > 200;
•脚本中的子查询返回的结果集只有4行，但这4行分别插入两张表中，Oracle会提示有8行被创建
•两条values子句中的列名列表并不要求一样，但都必须是在子查询选择列表中出现过的列名(列别名)

4.条件insert all
•示例：
INSERT ALL
  WHEN SAL > 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  WHEN MGR > 200   THEN INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;
•对于子查询返回结果集中的每一行，顺序计算每个when子句，如果满足条件就插入该行到相应的表中
•这个例子中的两个when子句的条件恰好互斥，所以只有4行被插入，每个表有2行
 如果将第二个when子句的条件改为MGR > 100，则Oracle将插入5行

5.条件insert first
•示例：
INSERT FIRST
  WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID, SAL)
  WHEN HIREDATE like ('%00%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
  WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
  ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;
•对于子查询返回结果集中的每一行，顺序计算每个when子句，只要遇到第一个条件满足的when子句，就立即将该行插入相应的表中，然后忽略后面的when子句
•如果子查询结果集有n行的话，条件insert first语句最多能插入的行数不会超过n行
•在条件insert语句中，可以有多个when子句，但最多只能有一个else子句

6.旋转插入(Pivoting INSERT)
•使用 pivoting INSERT，可以从非关系数据库表转换记录集到关系格式
•使用pivoting insert操作，可以构建一个转换，将来自任意输入流的记录(比如非关系数据库)转换成关系数据库表中的多条记录

•示例：
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_data;
•在这个示例中，数据源sales_data表明显不是第三范式，经过这样的转换，sales_data表中一行记录将变成sale_info表中的五行记录(sale_info表满足第三范式)
•旋转insert的不同之处在于所有的into子句都使用同一张表

7.外部表
•外部表是只读表，在外部表中数据被存储在数据库外面的文件中
•用 CREATE TABLE 语句创建外部表的元数据，并将外部表的元数据保存在数据库
•借助外部表的帮助，Oracle 数据能够被作为文件存储或卸载
•外部表的数据能够用SQL查询，但不能用DML(UPDATE,INSERT,DELETE)，并且不能创建索引

8.创建外部表
•使用 CREATE TABLE 语法和 external_table_clause 子句来创建外部表
•指定 ORGANIZATION 作为 EXTERNAL 来指出表是位于数据库之外的
 使用organization external子句，并不是真的创建一个表，而是在数据字典中创建外部表的元数据

•external_table_clause 子句由访问驱动类型, external_data_properties子句, 和REJECT LIMIT组成

•访问驱动类型表示Oracle如何访问外部表，可以分为两类：
–ORACLE_LOADER，即使用Oracle loader技术来访问外部表。此为缺省的访问驱动类型
–ORACLE_INTERNAL，即Oracle import/export技术

•external_data_properties 由下面的部分组成：
–默认目录(DEFAULT DIRECTORY)
  可以指定一个或多个缺省directory对象，用来指示外部数据源的存放位置
  一个directory对象对应一个数据库服务器的文件系统中的一个目录
–访问参数(ACCESS PARAMETERS)
  可以为访问驱动指定参数的值，Oracle并不处理该子句，而是由访问驱动来解析这些信息
–位置(LOCATION)
  用来为外部数据源指定一个外部定位器(external locator)，由访问驱动来解析它

•REJECT LIMIT子句
 reject limit子句用于指定在查询外部表时，最多允许发生多少次转换错误。超过此数，Oracle将返回错误并停止查询。缺省值为 0

9.创建外部表的例子
•创建一个 DIRECTORY 对象，它指出外部数据源所在的文件系统目录
 CREATE [OR REPLACE] directory对象名 AS '文件路径名';

•示例：
 CREATE DIRECTORY emp_dir AS 'D:/oracle/oradata' ; --创建一个directory对象emp_dir
 CREATE TABLE oldemp (empno NUMBER, empname CHAR(20), birthdate DATE)
 ORGANIZATION EXTERNAL  --创建外部表oldemp，有3个列
 (TYPE ORACLE_LOADER    --使用访问驱动类型为ORACLE_LOADER 
  DEFAULT DIRECTORY emp_dir --缺省目录为emp_dir
  ACCESS PARAMETERS (  --设置访问参数
 RECORDS DELIMITED BY NEWLINE   --外部文件中，以换行作为记录分隔符
 BADFILE 'bad_emp' LOGFILE 'log_emp' --指定错误文件、日志文件等辅助文件
  FIELDS TERMINATED BY ','   --以逗号作为记录中的域分隔符
 (empno CHAR,empname CHAR,  --为记录中的域指定数据类型和格式
 birthdate CHAR date_format date mask "dd-mon-yyyy"))
 LOCATION ('emp1.txt'))  --外部表对应的文件是 D:/oracle/oradata/emp1.txt
 PARALLEL 5   --指示外部表的最大并行度
 REJECT LIMIT 200;  --表示查询该外部表时，最多允许出现200个转换错误

10.查询外部表
•外部表不描述存储在数据库中的任何数据，它也不描述外部数据源中的数据是如何存储的
 外部表描述了外部表层(external table layer)是如何向数据库展现数据的
•将外部文件中的数据转换为符合外部表定义的格式是访问驱动和外部表层的职责
 当数据库访问外部表时，数据库调用访问驱动获取外部源的数据并转换成数据库期望的格式
•外部数据源的数据描述与外部表的数据定义是不同的，这一点很重要
 外部数据源文件中记录的字段数可以与外部表的字段数不一样，外部数据源文件中字段的数据类型与外部表中字段的类型也可以不同。
 由访问驱动确保外部数据源中的数据被转换成符合外部表定义的数据

11.用 CREATE TABLE 语句创建索引
•示例：
 CREATE TABLE NEW_EMP(
 employee_id NUMBER(6) PRIMARY KEY USING INDEX
        (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)),
 first_name VARCHAR2(20),
 last_name VARCHAR2(25) );
•这时Oracle9i的增强之一，你可以在创建主键时指定索引名，而不是由Oracle自动生成一个索引名