在本教程中,您将学习如何使用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;

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

（1）创建一个名为fruits的新表:

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

（2）使用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条件:

（1）如果订单amount大于30000,则Oracle将数据插入到big_tables中,并忽略随后的WHEN条件。

（2）如果第一次评估/计算为false,且金额大于或等于10000,则Oracle将数据插入到medium_tables中,并跳过评估/计算ELSE子句块。

（3）如果前两个WHEN条件评估/计算为false,则Oracle在ELSE子句中执行INTO子句,将数据插入到small_orders表中。

3. Oracle INSERT ALL限制

Oracle多表插入语句受以下主要限制:

（1）它可以用来将数据只插入到表中,而不是视图或物化视图。

（2）它不能用来将数据插入到远程表中。

（3）所有INSERT INTO子句中的列总和不得超过999。

（4）一个表集合表达式不能在一个多表插入语句中使用。

（5）多表插入语句的子查询不能使用序列。

 您阅读本篇文章共花了：