Multitable Insert

最新推荐文章于 2019-01-02 21:20:19 发布

cppg67031

最新推荐文章于 2019-01-02 21:20:19 发布

阅读量155

点赞数

文章标签：数据库

First of all,In a multitable insert, you insert computed rows derived from the rows returned from the evaluation of a subquery into one or more tables.
classified index as follows:

1 unconditional multitable insert
syntax:
insert all
into_clause [values_clause] subquery；

for example:

SQL>
SQL> show user;
USER 为 "SCOTT"
SQL> desc emp;
名称
--------------------------------------------------------------------------------------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

SQL> create table sal_hist as select ename,hiredate,mgr,sal from emp where 1=0;

表已创建。

SQL> create table mgr_hist as select ename,hiredate,mgr,sal from emp where 1=0;

表已创建。

SQL> insert all
2 into sal_hist(ename,hiredate,sal) values(ename,hiredate,sal)
3 into mgr_hist(ename,mgr,sal) values(ename,mgr,sal)
4 select ename,hiredate,mgr,sal from emp;

已创建28行。

2 conditional multitable insert
syntax:
insert all
[when condition then] [into_clause values_clause]
[else] [into_clause values_clause] subquery；

for example:

SQL> insert all
2 when sal>1000 then
3 into sal_hist(ename,hiredate,sal) values(ename,hiredate,sal)
4 when mgr>7700 then
5 into mgr_hist(ename,mgr,sal) values(ename,mgr,sal)
6 select ename,hiredate,mgr,sal from emp;

已创建18行。

3 FIRST insert
syntax:
insert all
[when condition then] [into_clause values_clause]
[else] [into_clause values_clause] subquery；

for example:

SQL> insert first
2 when sal>3000 then
3 into sal_hist(ename,hiredate,sal) values(ename,hiredate,sal)
4 when sal>1000 then
5 into mgr_hist(ename,mgr,sal) values(ename,mgr,sal)
6 select ename,hiredate,mgr,sal from emp;

已创建12行。

attention:For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row.

reference:

1 reference oracle 10g r2 document as follows:
Many times, external data sources have to be segregated based on logical attributes for insertion into different target objects. It is also frequent in data warehouse environments to fan out the same source data into several target objects. Multitable inserts provide a new SQL statement for these kinds of transformations, where data can either end up in several or exactly one target, depending on the business transformation rules. This insertion can be done conditionally based on business rules or unconditionally.

2 Restrictions on Multitable Inserts

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.
Multitable inserts are not parallelized if any target table is index organized or if any target table has a bitmap index defined on it.
Plan stability is not supported for multitable insert statements.
The subquery of the multitable insert statement cannot use a sequence.