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;

一, 無條件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才會起作用。