Oracle插入多行到多表

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语句将多个行插入一个或多个表中。