Oracle中的insert/insert all/insert first

insert all官方文件解釋：

/*

多表插入語句的限制條件:

 1. 只能對錶執行多表插入語句，不能對檢視或物化檢視執行；

 2. 不能對遠端表執行多表插入語句；

 3. 不能使用表集合表示式；

 4. 不能超過999個目標列；

 5. 在RAC環境中或目標表是索引組織表或目標表上建有BITMAP索引時，多表插入語句不能並行執行；

 6. 多表插入語句不支援執行計劃穩定性；

 7. 多表插入語句中的子查詢不能使用序列。

*/

第一步：準備資料

CREATE TABLE t1(product_id NUMBER, product_name VARCHAR2(80),MONTH NUMBER);  

INSERT INTO t1 VALUES(111, '蘋果',1);  

INSERT INTO t1 VALUES(222, '橘子',1);  

INSERT INTO t1 VALUES(333, '香蕉',1);  

第二部：insert all

INSERT ALL  

    INTO t2   

    VALUES (product_id, product_name,MONTH)  

    INTO t2   

    VALUES (product_id, product_name,MONTH+1)  

    INTO t2   

    VALUES (product_id, product_name,MONTH+2)  

    INTO t2   

    VALUES (product_id, product_name,MONTH+3)   

SELECT product_id, product_name, MONTH  

FROM t1;  

這裡的表t2的資料下面會用到

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

第三步：有條件的insert all

insert all 

when month=1 then

into t3

when month=2 then 

into t4

else 

into t5

select product_id,product_name,month from t2;

commit;

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

第四部：insert first （需要講解下）

insert first 

when month=1 then

into t3 VALUES(product_id,product_name,month)  

when product_id=111 then 

into t4 VALUES(product_id,product_name,month)  

else 

into t5 VALUES(product_id,product_name,month)  

select product_id,product_name,month from t2;

commit;

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

根據第二部，t2表的資料做測試。

查詢t4表發現:少了一條 111 蘋果 1

111 蘋果2

111 蘋果 3

111 蘋果 4

總結：insert first 當資料滿足第一when 條件的時候，不會作用在下面的when條件了，

簡而言之:當一條資料滿足條件後，即使滿足後面的條件也不會插入資料庫。

正常情況：如果資料滿足所有的when條件，每個表都會插入該條資料，insert first 資料只會插入一個表。

無條件的插入

Oracle中的insert all是指把同一批資料插入到不同的表中，假如如今有個需求：把t表中的資料分別插入t1,t2，假設你不知道insert all，你可能會使用insert插入2次，例如以下所看到的：

insert  into t1(object_name,object_id) select * from t;
insert  into t2(object_name,object_id) select * from t;
commit;

其實，以上這樣的寫法是錯誤的，由於在兩次insert的過程中，t表的資料有可能已經發生了變化，也就是說，t1,t2表得到的資料有可能不一樣，正確的寫法應該是採用insert all：

insert all
into t1(object_name,object_id)
into t2(object_name,object_id)
select * from t;
commit;

有條件的插入

看以下的樣例：

--insert first
--前面等於1的條件被<=5含在內，FIRST就表示前面插入了，後面不會再插入了。
insert first
when object_id = 1 then
into t1(object_name,object_id)
when object_id <=5 then                      
into t2(object_name,object_id)
select * from t;
commit;
select * from t1;
OBJECT_NAME                OBJECT_ID
--------------------------------- ---
ICOL$                              1
select * from t2;
OBJECT_NAME                OBJECT_ID
--------------------------------- ---
I_USER1                            2
CON$                               3
UNDO$                              4
C_COBJ#                            5
--insert all
insert all
when object_id = 1 then
into t1(object_name,object_id)
when object_id <=5 then                      
into t2(object_name,object_id)
select * from t;
commit;
SQL> select * from t1;
OBJECT_NAME                OBJECT_ID
--------------------------------- ---
ICOL$                              1
SQL> select * from t2;
OBJECT_NAME                OBJECT_ID
--------------------------------- ---
ICOL$                              1
I_USER1                            2
CON$                               3
UNDO$                              4
C_COBJ#                            5

行轉列插入

select * from sales_source_data;
EMPLOYEE_ID    WEEK_ID  SALES_MON  SALES_TUE  SALES_WED SALES_THUR  SALES_FRI
----------- ---------- ---------- ---------- ---------- ---------- ----------
        176          6       2000       3000       4000       5000       6000
insert all
into sales_info values(employee_id,week_id,sales_mon)
into sales_info values(employee_id,week_id,sales_tue)
into sales_info values(employee_id,week_id,sales_wed)
into sales_info values(employee_id,week_id,sales_thur)
into sales_info values(employee_id,week_id,sales_fri)
select employee_id,week_id,sales_mon,sales_tue,
sales_wed,sales_thur,sales_fri
from sales_source_data;
select * from sales_info;
EMPLOYEE_ID       WEEK      SALES
----------- ---------- ----------
        176          6       2000
        176          6       3000
        176          6       4000
        176          6       5000
        176          6       6000

About Me