ORACLE insert
多表 INSERT 语句概述
:: INSERT...SELECT 语句能够作为单个的 DML 语句的一部分用于插入行到多表中 :: 多表
INSERT 语句能够被用在数据仓库系统中从一个或多个操作源转移数据到一组目的表中 ::
下面的方法提供重要的性能改进: - 单个 DML 与多 INSERT...SELECT 语句相对 - 单个
DML 与一个用 IF...THEN 语法做多插入的过程相对
Overview of Multitable INSERT Statements
In a multitable INSERT statement, you insert computed rows derived from the
rows returned from the evaluation of a subquery into one or more tables.
Multitable INSERT statements can play a very useful role in a data warehouse
scenario. You need to load your data warehouse regularly so that it can serve
its purpose of facilitating business analysis. To do this, data from one or
more operational systems needs to be extracted and copied into the warehouse.
The process of extracting data from the source system and bringing it into the
data warehouse is commonly called ETL, which stands for extraction,
transformation, and loading.
During extraction, the desired data has to be identified and extracted from
many different sources, such as database systems and applications. After
extraction, the data has to be physically transported to the target system or
an intermediate system for further processing. Depending on the chosen means
of transportation, some transformations can be done during this process. For
example, a SQL statement that directly accesses a remote target through a
gateway can concatenate two columns as part of the SELECT statement.
Once data is loaded into an Oracle9i, database, data transformations can be
executed using SQL operations. With Oracle9i multitable INSERT statements is
one of the techniques for implementing SQL data transformations.
Overview of Multitable Insert Statements (continued)
Multitable INSERTS statement offer the benefits of the INSERT ... SELECT
statement when multiple tables are involved as targets. Using functionality
prior to Oracle9i, you had to deal with n independent INSERT ... SELECT
statements, thus processing the same source data n times and increasing the
transformation workload n times.
As with the existing INSERT ... SELECT statement, the new statement can be
parallelized and used with the direct-load mechanism for faster performance.
(直接装载机制)
Each record from any input stream, such as a nonrelational database table, can
now be converted into multiple records for more relational database table
environment. To implement this functionality before Oracle9i, you had to write
multiple INSERT statements.
多表 INSERT 语句的类型
Oracle9i 引入下面的多表插入语句的类型: - 无条件 INSERT - 条件 ALL INSERT
- 条件 FIRST INSERT - 重点(Pivoting) INSERT
Types of Multitable INSERT Statements
Oracle 9i introduces the following types of multitable INSERT statements:
- Unconditional INSERT - Conditional ALL INSERT - Conditional FIRST INSERT
- Pivoting INSERT
You use different clauses to indicate the type of INSERT to be executed.
多表 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
The slide displays the generic format for multitable INSERT statements. There
are four types of 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.
Multitable INSERT Statements (continued)
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.
无条件 INSERT ALL
:: 从 EMPLOYEES 中选择 EMPLOYEE_ID 大于200的雇员 EMPLOYEE_ID, HIRE_DATE, SALARY,
和 MANAGER_ID 值 :: 用多表 INSERT 插入这些值到 SAL_HISTORY 和 MGR_HISTORY 表中
要先建立表
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行
Unconditional INSERT ALL
The example in the slide inserts rows into both the SAL_HISTORY and the
MGR_HISTORY tables.//插入到两个表中.
The SELECT statement retrieves the details of employee ID, hire date, salary,
and manager ID of those employees whose employee ID is greater than 200 from
the EMPLOYEES table. The details of the employee ID, hire date, and salary are
inserted into the SAL_HISTORY table. The details of employee ID, manager ID
and salary are inserted into the MGR_HISTORY table.
This INSERT statement is referred to as an unconditional INSERT, as no further
restriction is applied to the rows that are retrieved by the SELECT statement.
All the rows retrieved by the SELECT statement are inserted into the two
tables, SAL_HISTORY and MGR_HISTORY. The VALUES clause in the INSERT
statements specifies the columns from the SELECT statement that have to be
inserted into each of the tables. Each row returned by the SELECT statement
results in two insertions, one for the SAL_HISTORY table and one for the
MGR_HISTORY table.
The feedback 8 rows created can be interpreted to mean that a total of eight
insertions were performed on the base tables SAL_HISTORY and MGR_HISTORY.
条件INSERT ALL
:: 从 EMPLOYEES 表中选择 EMPLOYEE_ID 大于200的那些雇员的 EMPLOYEE_ID, HIRE_DATE,
SALARY 和 MANAGER_ID 值 :: 如果 SALARY 大于 $10,000,用一个条件多表
INSERT语句插入这些值到 SAL_HISTORY 表中 :: 如果 MANAGER_ID 大于 200,用一个多表
INSERT 语句插入这些值到 MGR_HISTORY 表中
Conditional INSERT ALL
The problem statement for a conditional INSERT ALL statement is specified in
the slide. The solution to the preceding problem is shown in the next page.
条件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.
Conditional INSERT ALL (continued)
The example in the slide is similar to the example on the previous slide as it
inserts rows into both the SAL_HISTORY and the MGR_HISTORY tables. The SELECT
statement retrieves the details of employee ID, hire date, salary, and manager
ID of those employees whose employee ID is greater than 200 from the EMPLOYEES
table. The details of employee ID, hire date, and salary are inserted into the
SAL_HISTORY table. The details of employee ID, manager ID, and salary are
inserted into the MGR_HISTORY table.
This INSERT statement is referred to as a conditional ALL INSERT, as a further
restriction is applied to the rows that are retrieved by the SELECT statement.
From the rows that are retrieved by the SELECT statement, only those rows in
which the value of the SAL column is more than 10000 are inserted in the
SAL_HISTORY table, and similarly only those rows where the value of the MGR
column is more than 200 are inserted in the MGR_HISTORY table.
Observe that unlike the previous example, where eight rows were inserted into
the tables, in this example only four rows are inserted.
The feedback 4 rows created can be interpreted to mean that a total of four
inserts were performed on the base tables, SAL_HISTORY and MGR_HISTORY.
条件FIRST INSERT
:: 从 EMPLOYEES 表中选择 DEPARTMENT_ID , SUM(SALARY) 和 MAX(HIRE_DATE) :: 如果
SUM(SALARY) 大于 $25,000 则用一个条件 FIRST 多表 INSERT 插入这些值到 SPECIAL_SAL
表中 :: 如果第一个 WHEN 子句的值为 true,则该行的后面的 WHEN 子句被跳过 ::
对于那些不满足第一个 WHEN 条件的行,用一个条件多表 INSERT 基于HIRE_DATE
列的值插入 HIREDATE_HISTORY_00, 或 HIREDATE_HISTORY_99, 或 HIREDATE_HISTORY
表,根据HIREDATE列的值.
Conditional FIRST INSERT
The problem statement for a conditional FIRST INSERT statement is specified in
the slide. The solution to the preceding problem is shown on the next page.
条件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.
Conditional FIRST INSERT (continued)
The example in the slide inserts rows into more than one table, using one
single INSERT statement. The SELECT statement retrieves the details of
department ID, total salary, and maximum hire date for every department in the
EMPLOYEES table.
This INSERT statement is referred to as a conditional FIRST INSERT, as an
exception is made for the departments whose total salary is more than $25,000.
The condition WHEN ALL > 25000 is evaluated first. If the total salary for a
department is more than $25,000, then the record is inserted into the
SPECIAL_SAL table irrespective of the hire
date(如果sum(salary)>25000,则将记录插入到SPECIAL_SAL中,不管hiredate). If this
first WHEN clause evaluates to true, the Oracle Server executes the
corresponding INTO clause and skips subsequent WHEN clauses for this row.
For the rows that do not satisfy the first WHEN condition (WHEN SAL > 25000),
the rest of the conditions are evaluated just as a conditional INSERT
statement, and the records retrieved by the SELECT statement are inserted into
the HIREDATE_HISTORY_00, or HIREDATE_HISTORY_99, or HIREDATE_HISTORY tables,
based on the value in the HIREDATE column.
The feedback 8 rows created can be interpreted to mean that a total of eight
INSERT statements were performed on the base tables SPECIAL_SAL
,HIREDATE_HISTORY_00, HIREDATE_HISTORY_99, and HIREDATE_HISTORY.
重点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,从非关系数据库表转换销售记录集到关系格式
././././
Pivoting INSERT
Pivoting is an operation in which you need to build a transformation such that
each record from any input stream, such as, a nonrelational database table,
must be converted into multiple records for a more relational database table
environment.
In order to solve the problem mentioned in the slide, you need to build a
transformation(转换) such that each record from the original nonrelational
database table, SALES_SOURCE_DATA, is converted into five records for the data
warehouse's SALES_INFO table. This operation is commonly referred to as
pivoting.
The problem statement for a pivoting INSERT statement is specified in the
slide. The solution to the preceding problem is shown in the next page.
重点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.
Pivoting INSERT (continued)
In the example in the slide, the sales data is received from the nonrelational
database table SALES_SOURCE_DATA, which is the details of the sales performed
by a sales representative on each day of a week, for a week with a particular
week ID.
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 176
6 4000 176 6 5000 176 6 6000
Observe in the preceding example that using a pivoting INSERT, one row from
the SALES_SOURCE_DATA table is converted into five records for the relational
table, SALES_INFO.
将表SALES_SOURCE_DATA的一行转换为表SALES_INFO的五条记录(关系表)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14661773/viewspace-703691/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14661773/viewspace-703691/