Oracle插入多行到多表

在本教學中將學習如何使用Oracle INSERT ALL語句將多行插入到一個或多個表中。

在之前的教學中，我們已經學習了如何在表中插入一行。但是，有時我們可能希望將多行插入到一個或多個表中。在這種情況下，就可以使用Oracle INSERT ALL語句，該語句也被稱為多項式插入語句。

Oracle提供了兩種型別的多項式插入語句：無條件的和有條件的。

1. 無條件的Oracle INSERT ALL語句

將多行插入到表中，要將多行插入到表中，請使用以下Oracle INSERT ALL語句：

INSERT ALL
    INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;

在這個語句中，每個值表示式值：val1，val2或val3必須參照由子查詢的選擇列表返回的列對應的值。

如果要使用文字值而不是子查詢返回的值，請使用以下子查詢：

SELECT * FROM dual;

以下範例演示如何將多行插入到表中。

首先，建立一個名為fruits的新表：

CREATE TABLE fruits (
    fruit_name VARCHAR(100) PRIMARY KEY,
    color VARCHAR(100) NOT NULL
);

其次，使用Oracle INSERT ALL語句將行插入到fruits表中：

INSERT ALL 
    INTO fruits(fruit_name, color)
    VALUES ('蘋果','紅色') 

    INTO fruits(fruit_name, color)
    VALUES ('橙子','橙色') 

    INTO fruits(fruit_name, color)
    VALUES ('香蕉','黃色')
SELECT 1 FROM dual;

第三，查詢fruits表資料來驗證插入結果：

SELECT 
    *
FROM 
    fruits;

執行上面查詢語句，得到以下結果 -

如上結果所見，按預期插入三行。

將多行插入到多個表中

還可以使用INSERT ALL語句將行插入到多個表中，如下所示。

INSERT ALL
    INTO table_name1(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name2(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name3(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;

2. 有條件的Oracle INSERT ALL語句

條件多項插入語句允許根據指定的條件將行插入到表中。

以下顯示了條件多項插入語句的語法：

INSERT [ ALL | FIRST ]
    WHEN condition1 THEN
        INTO table_1 (column_list ) VALUES (value_list)
    WHEN condition2 THEN 
        INTO table_2(column_list ) VALUES (value_list)
    ELSE
        INTO table_3(column_list ) VALUES (value_list)
Subquery

如果指定了ALL關鍵字，則Oracle將在WHEN子句中評估每個條件。如果條件評估/計算為true，則Oracle執行相應的INTO子句。

但是，當指定FIRST關鍵字時，對於由子查詢返回的每一行，Oracle都會從WHEN子句的上下方向評估每個條件。如果Oracle發現條件的計算結果為true，則執行相應的INTO子句並跳過給定行的後續WHEN子句。

請注意，單條件多項式插入語句最多可以有127個WHEN子句。

有條件的Oracle INSERT ALL範例

以下CREATE TABLE語句建立三個表：small_orders，medium_orders和big_orders，它們具有相同的結構：

CREATE TABLE small_orders (
    order_id NUMBER(12) NOT NULL,
    customer_id NUMBER(6) NOT NULL,
    amount NUMBER(8,2) 
);

CREATE TABLE medium_orders AS
SELECT *
FROM small_orders;

CREATE TABLE big_orders AS
SELECT *
FROM small_orders;

以下條件Oracle INSERT ALL語句根據訂單金額將訂單資料插入到三個表：small_orders，medium_orders和big_orders之中：

INSERT ALL
   WHEN amount < 1000000 THEN
      INTO small_orders
   WHEN amount  
      INTO medium_orders
   WHEN amount > 30000 THEN
      INTO big_orders
 SELECT order_id,
        customer_id,
        (quantity * unit_price) amount
 FROM orders
 INNER JOIN order_items USING(order_id);

通過使用ELSE子句插入到big_orders表中，這樣也可以達到相同的結果，如下所示：

INSERT ALL
   WHEN amount < 1000000 THEN
      INTO small_orders
   WHEN amount  
      INTO medium_orders
   ELSE
      INTO big_orders
  SELECT order_id,
         customer_id,
         (quantity * unit_price) amount
  FROM orders
  INNER JOIN order_items USING(order_id);

有條件的Oracle INSERT FIRST範例

考慮下面的例子。

INSERT FIRST
    WHEN (amount > 30000) THEN
        INTO big_orders
    WHEN (amount 
        INTO medium_orders
    ELSE
        INTO small_orders
 SELECT order_id,
         customer_id,
         (quantity * unit_price) amount
 FROM orders
 INNER JOIN order_items USING(order_id);

這個語句對INSERT ALL沒有任何意義，因為amount大於30000的訂單將被插入到三個表中。

但是，對於INSERT FIRST，對於由子查詢返回的每一行，Oracle將從上到下評估每個WHEN條件：

首先，如果訂單amount大於30000，則Oracle將資料插入到big_tables中，並忽略隨後的WHEN條件。
接下來，如果第一次評估/計算為false，且金額大於或等於10000，則Oracle將資料插入到medium_tables中，並跳過評估/計算ELSE子句塊。
最後，如果前兩個WHEN條件評估/計算為false，則Oracle在ELSE子句中執行INTO子句，將資料插入到small_orders表中。

3. Oracle INSERT ALL限制

Oracle多表插入語句受以下主要限制：

它可以用來將資料只插入到表中，而不是檢視或物化檢視。
它不能用來將資料插入到遠端表中。
所有INSERT INTO子句中的列總和不得超過999。
一個表集合表示式不能在一個多表插入語句中使用。
多表插入語句的子查詢不能使用序列。

在本教學中，您學習了如何使用Oracle INSERT ALL語句將多個行插入一個或多個表中。