Oracle插入多行到多表

精华

小牛编辑

165浏览

2023-03-14

在本教程中将学习如何使用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语句将多个行插入一个或多个表中。