■
Oracle9i 对 DML 和 DDL 语句的扩展
■多表 INSERT 语句概述
:: INSERT...SELECT 语句能够作为单个的 DML 语句的一部分用于插入行到多表中
:: 多表 INSERT 语句能够被用在数据仓库系统中从一个或多个操作源转移数据到一组目的表中
:: 下面的方法提供重要的性能改进：
- 单个 DML 与多 INSERT...SELECT 语句相对
- 单个 DML 与一个用 IF...THEN 语法做多插入的过程相对
Oracle9i 引入下面的多表插入语句的类型：
- 无条件 INSERT
- 条件 ALL INSERT
- 条件 FIRST INSERT
- 重点(Pivoting) INSERT
多表 INSERT 语句
语法:
INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause] (subquery)
conditional_insert_clause
[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
Multitable INSERT Statements
･Unconditional INSERT
･Conditional ALL INSERT
･Conditional FIRST INSERT
･Pivoting INSERT
①Unconditional INSERT: ALL into_clause
Specify ALL followed by multiple insert_into_clauses to perform an unconditional multitable insert. The Oracle Server executes each insert_into_clause once for each row returned by the subquery.
②Conditional INSERT: conditional_insert_clause
Specify the conditional_insert_clause to perform a conditional multitable insert. The Oracle Server filters each insert_into_clause through the corresponding WHEN condition, which determines whether that insert_into_clause is executed. A single multitable insert statement can contain up to 127 WHEN clauses.
③Conditional INSERT: ALL
If you specify ALL, the Oracle Server evaluates each WHEN clause regardless of the results of the evaluation of any other WHEN clause. For each WHEN clause whose condition evaluates to true, the Oracle Server executes the corresponding INTO clause list.
④Conditional FIRST: INSERT
If you specify FIRST, the Oracle Server evaluates each WHEN clause in the order in which it appears in the statement. If the first WHEN clause evaluates to true, the Oracle Server executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row.
⑤Conditional INSERT: ELSE Clause
For a given row, if no WHEN clause evaluates to true:
If you have specified an ELSE, clause the Oracle Server executes the INTO clause list associated with the ELSE clause.
If you did not specify an ELSE clause, the Oracle Server takes no action for that row.
■Restrictions on Multitable INSERT Statements
You can perform multitable inserts only on tables, not on views or materialized views.
You cannot perform a multitable insert into a remote table.
You cannot specify a table collection expression when performing a multitable insert.
In a multitable insert, all of the insert_into_clauses cannot combine to specify more than 999 target columns.
eg:
①无条件 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;
8 rows created. //两个表一共插了8行
②条件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;
4 rows created.
③条件FIRST INSERT
或者是第一个WHEN,或者是后面的某一个when
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;
8 rows created.
④枢轴式(Pivoting) INSERT
:: 支持从非关系数据库表中接受一组销售记录， SALES_SOURCE_DATA 的格式如下：
EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRI
:: 你可能想要以一种典型的相关格式存储这些记录到 SALES_INFO 表中：
EMPLOYEE_ID, WEEK, SALES
::使用 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_source_data;
5 rows created.
DESC SALES_SOURCE_DATA
Name Null? Type
EMPLOYEE_ID NUMBER(6)
WEEK_ID NUMBER(2)
SALES_MON NUMBER(8,2)
SALES_TUE NUMBER(8,2)
SALES_WED NUMBER(8,2)
SALES_THUR NUMBER(8,2)
SALES_FRI NUMBER(8,2)
SELECT * FROM SALES_SOURCE_DATA;
EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES WED SALES THUR SALES_FRI
176 6 2000 3000 4000 5000 6000
DESC SALES_INFO
Name Null? Type
EMPLOYEE_ID NUMBER(6)
WEEK NUMBER(2)
SALES NUMBER(8,2)
SELECT * FROM sales_info;
EMPLOYEE_ID WEEK SALES
176 6 2000
176 6 3000
外部表
