ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Multitable Insert

Multitable Insert

原创 Linux操作系统 作者:yyp2009 时间:2012-03-13 01:40:35 0 删除 编辑

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.

 

---------end-----------

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13750068/viewspace-718393/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Correlated Subqueries
请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    330
  • 访问量
    1025846