oralce insert 多表

在一般操作中,我们可能会碰到一些相同或者雷同的数据源要插入到不同的数据表中的情况. 在oracle9i之前的版本中, 我们必须执行多次insert语句, 因此, 为了填充不同的表, 就在源表上添加了一些不必要的IO操作. Oracle9i引入了多表insert操作, 采用以下三个形式.

Unconditional(无条件的): 没有任何限制地向多个表中插入给定表的数据;

Pivoting: 用一个非规范化的结构把数据插入到一个或多个表中.

Conditional(有条件的): 根据已有的特定条件对每个约束进行控制.

测试环境建立

create table z_test(id integer, name varchar2(100));
create table z_test1(id integer, name varchar2(100));
create table z_test2(id integer);
create table z_test3( name varchar2(100));

insert into z_test
select rownum, object_name
from dba_objects do
where rownum < 11;
commit;
/*
drop table z_test;
drop table z_test1;
drop table z_test2;
drop table z_test3;

*/

一, 无条件insert all

INSERT ALL

insert_into_clause values_clause_1

[insert_into_clause values_clause_2]

……

Subquery；

---------------------------------------------------------------------

1、指定所有跟随着的多表 insert_into_clauses 执行无条件的多表插入；

2、对于每个由子查询返回的行， Oracle 服务器执行每一个 insert_into_clause一次。

 

truncate table z_test1;
truncate table z_test3;
truncate table z_test2;

insert all
into z_test1(id,name) values(id,name)
into z_test2(id) values(id)
into z_test3(name) values(name)
select * from z_test;
commit;

select * from z_test1;

ID

NAME

1

ACCESS$

2

AGGXMLIMP

3

AGGXMLINPUTTYPE

4

ALL_ALL_TABLES

5

ALL_APPLY

6

ALL_APPLY_CONFLICT_COLUMNS

7

ALL_APPLY_DML_HANDLERS

8

ALL_APPLY_ERROR

9

ALL_APPLY_KEY_COLUMNS

10

ALL_APPLY_PARAMETERS

select * from z_test2;

ID

1

2

3

4

5

6

7

8

9

10

select * from z_test3;

NAME

ACCESS$

AGGXMLIMP

AGGXMLINPUTTYPE

ALL_ALL_TABLES

ALL_APPLY

ALL_APPLY_CONFLICT_COLUMNS

ALL_APPLY_DML_HANDLERS

ALL_APPLY_ERROR

ALL_APPLY_KEY_COLUMNS

ALL_APPLY_PARAMETERS

二, 条件insert all

INSERT ALL

WHEN condition THEN insert_into_clause values_clause

 [WHEN condition THEN] [insert_into_clause values_clause]

……

 [ELSE] [insert_into_clause values_clause]

Subquery；

1、指定 conditional_insert_clause 来执行一个条件多表插入；

2、Oracle 服务器通过相应的 WHEN 条件过滤每一个 insert_into_clause，确定是否执行这个 insert_into_clause；

3、一个单个的多表插入语句可以包含最多 127 个 WHEN 子句。

truncate table z_test1;
truncate table z_test3;
truncate table z_test2;

insert all
when mod(id,3) = 1 then into z_test1(id,name) values(id,name)
when mod(id,2) = 1 then into z_test2(id) values(id)
when mod(id,3) = 0 then into z_test3(name) values(name)
select * from z_test;
commit;

select * from z_test1;

ID

NAME

1

ACCESS$

4

ALL_ALL_TABLES

7

ALL_APPLY_DML_HANDLERS

10

ALL_APPLY_PARAMETERS

select * from z_test2;

ID

1

3

5

7

9

select * from z_test3;

NAME

AGGXMLINPUTTYPE

ALL_APPLY_CONFLICT_COLUMNS

ALL_APPLY_KEY_COLUMNS

三, 条件insert first

INSERT FIRST

WHEN condition THEN insert_into_clause values_clause

 [WHEN condition THEN] [insert_into_clause values_clause]

……

 [ELSE] [insert_into_clause values_clause]

Subquery；

-----------------------

1、Oracle 服务器对每一个出现在语句顺序中的 WHEN 子句求值；

2、如果第一个 WHEN 子句的值为 true，Oracle 服务器对于给定的行执行相应的 INTO 子句，并且跳过后面的 WHEN 子句(后面的when语句都不再考虑满足第一个When子句的记录，即使该记录满足when语句中的条件)。

truncate table z_test1;
truncate table z_test3;
truncate table z_test2;

insert first
when mod(id,3) = 1 then into z_test1(id,name) values(id,name)
when mod(id,2) = 1 then into z_test2(id) values(id)
when mod(id,3) = 0 then into z_test3(name) values(name)
select * from z_test;
commit;

select * from z_test1;

ID

NAME

1

ACCESS$

4

ALL_ALL_TABLES

7

ALL_APPLY_DML_HANDLERS

10

ALL_APPLY_PARAMETERS

select * from z_test2;

ID

3

5

9

select * from z_test3;

NAME

ALL_APPLY_CONFLICT_COLUMNS

四, 小结

a、你只能在表而不能在视图上执行多表插入；

b、你不能执行一个多表插入到一个远程表；

c、在执行一个多表插入时，你不能指定一个表集合表达式；

d、在一个多表插入中，所有的 insert_into_clauses 不能组合指定多于 999 个目列；

e、只有当所有insert_into_clauses中的表数据都没有发生更新时，Rollback才会起作用。