使用Insert Select實作同時向多個表插入記錄

使用Insert Select實作同時向多個表插入記錄

一、無條件 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一次。

二、條件 INSERT ALL

WHEN condition THEN insert_into_clause values_clause

[WHEN condition THEN] [insert_into_clause values_clause]

[ELSE] [insert_into_clause values_clause] 

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

1、指定 conditional_insert_clause 來執行一個條件多表插入；

2、Oracle 伺服器通過相應的 WHEN 條件過濾每一個 insert_into_clause，确定是否執行這個 insert_into_clause；

3、一個單個的多表插入語句可以包含最多 127 個 WHEN 子句。

三、條件 INSERT FIRST

INSERT FIRST

1、Oracle 伺服器對每一個出現在語句順序中的 WHEN 子句求值；

2、如果第一個 WHEN 子句的值為 true，Oracle 伺服器對于給定的行執行相應的 INTO 子句，并且跳過後面的 WHEN 子句(後面的when語句都不再考慮滿足第一個When子句的記錄，即使該記錄滿足when語句中的條件)。

注：多表 INSERT 語句上的限制

a、你隻能在表而不能在視圖上執行多表插入；

b、你不能執行一個多表插入到一個遠端表；

c、在執行一個多表插入時，你不能指定一個表集合表達式；

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

e、隻有當所有insert_into_clauses中的表資料都沒有發生更新時，Rollback才會起作用。

EG: 

   Tables: z_test(id int,name varchar2(10));

          z_test1(id int ,name varchar2(10));

          z_test2(id int);

          z_test3(name varchar2(10);

初始資料：z_test

Id    Name

10    133

5    184

1    18423

1    18445

1    18467

6    129

2    12923

2    12945

z_test1, z_test2,z_test3均為空。

測試一：無條件 INSERT ALL

    SQL 語句：

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

SQL> Insert All 

  2  Into z_test1(id,name) values (id,name)

  3  Into z_test2(id) values(id)

  4  Select id,name from z_test;

16 rows created.

測試結果：

SQL> select * from z_test1;

        ID NAME

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

        10 133

         5 184

         1 18423

         1 18445

         1 18467

         6 129

         2 12923

         2 12945

8 rows selected.

SQL> select * from z_test2;

        ID

----------

        10

         5

         1

         6

         2

測試二：條件 INSERT ALL

SQL> Insert All

  2  when id>5 then into z_test1(id, name) values(id,name)

  3  when id<>2 then into z_test2(id) values(id)

  4  else into z_test3 values(name)

  5  select id,name from z_test;

10 rows created.

6 rows selected.

SQL> select * from z_test3;

NAME

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

12923

12945

2 rows selected.

測試三：條件 INSERT FIRST

SQL> Insert First 

  2  when id=1 then into z_test1 values(id,name)

  3  when id>5 then into z_test2 values(id)

  5  select * from z_test;

8 rows created.

3 rows created.

2 rows created.

184