Oracle中INSERT ALL / FIRST语法详解，将多行插入多表中，全文四千字总结，建议收藏！

SQL数据库运维 2022-11-11

7608

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

1、使用 Oracle INSERT语句将数据插入到表中（插入一行数据）

将新行插入到数据表中，可以按照如下语法使用Oracle INSERT语句：

INSERT INTO table_name (column_1, column_2, column_3, ... column_n)
VALUES( value_1, value_2, value_3, ..., value_n);

在这个声明语句中：

首先，指定要插入的表的名称—table_name 。
其次，在圆括号内指定逗号分隔列名的列表。
最后，指定对应于列列表的逗号分隔值列表。

注意：如果值列表与表列具有相同的顺序，则可以跳过不指定列的列表，但并不推荐这样做（语法如下）：

INSERT INTO table_name
VALUES (value_1, value_2, value_3, ..., value_n);

如果从Oracle INSERT 语句中排除一列或多列，则必须指定出剩余需要插入数据的列名称，因为Oracle需要它与值列表中的值相匹配。

在INSERT语句中省略的列将使用缺省值(如果可用)或者如果列接受NULL值，则使用NULL值。

新建数据表，举例说明，建表语法如下，由于Oracle 12c与Oracle 11g创建自增ID语法有所不同，将分别列出（本文章举例均在Oracle 11g的环境下进行测试），供大家参考：

-- oracle 11g 创建表语法
--如果已存在discounts_seq，可先删除
--drop sequence discounts_seq;


create sequence discounts_seq
 increment by 1
 start with 1
 maxvalue 9999999999
 nocache;


CREATE TABLE insertceshi (
    discount_id NUMBER, -- discounts_seq.nextval
    discount_name VARCHAR2(255) NOT NULL,
    amount NUMBER(3,1) NOT NULL,
    start_date DATE NOT NULL,
    expired_date DATE NOT NULL
);


-- oracle 12c 创建表语法
CREATE TABLE insertceshi (
    discount_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    discount_name VARCHAR2(255) NOT NULL,
    amount NUMBER(3,1) NOT NULL,
    start_date DATE NOT NULL,
    expired_date DATE NOT NULL
);

在insertceshi 表中，discount_id 列是一个标识列，其默认值由系统自动生成，因此在Oracle 12c 中，不必在INSERT 语句中指定discount_id 列。其他列，如：discount_name，amount，start_date和expired_date是NOT NULL列，所以必须为它们提供值，不允许为空。

以下语句将一行新数据插入到insertceshi 表中：

-- oracle 11g语法
INSERT INTO insertceshi(discount_id, discount_name, amount, start_date, expired_date)
VALUES(discounts_seq.nextval, '双11电脑特价', 6.5, DATE '2022-11-11', DATE '2022-11-12');


-- Oracle 12c语法
INSERT INTO insertceshi(discount_name, amount, start_date, expired_date)
VALUES('双11电脑特价', 6.5, DATE '2022-11-11', DATE '2022-11-12');

再次举例，向insertceshi
表中插入一个新行，并使用CURRENT_DATE函数（返回当前会话时区中的当前日期）的结果指定start_date列的值：

-- Oracle 11g写法
INSERT INTO insertceshi(discount_id, discount_name, amount, expired_date, start_date)
VALUES(discounts_seq.nextval, '2022长期折扣',  9.5, DATE '2022-12-31', CURRENT_DATE);


-- Oracle 12c写法
INSERT INTO insertceshi(discount_name, amount, expired_date, start_date)
VALUES('2017长期折扣',  9.5, DATE '2022-12-31', CURRENT_DATE);

注意：start_date列这里是放到了最后一个位置，而它对应的值也放到最后一个位置。

查询结果：

SELECT * FROM insertceshi;

2、使用 Oracle INSERT INTO SELECT 语句将数据从SELECT语句的结果中插入到表中（选择性插入数据）

有时候，我们需要从其它表中选择数据，然后将其插入到另一个表中（需要两个表都是已经存在的）。要做到这一点，可使用OracleINSERT INTO SELECT语句，如下所示：

--指定列名称，进行一一对应
INSERT INTO target_table (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE condition;


--如果SELECT语句的结果具有与target_table表的列对应的值，则不需要在INSERT INTO子句中指定列名称
INSERT INTO target_table 
SELECT col1,
       col2,
       col3
FROM source_table
WHERE condition;

注意：Oracle INSERT INTO...SELECT
语句要求源表和目标表匹配数据类型。

Oracle扩展知识：如果需要备份数据，快速新建一个不存在的表，可以使用以下语法：

create table NEW_table
as SELECT * FROM OLD_table where 1=0;  INSERT ALL

where
子句中的条件是确保OLD_table
表中的数据不会被复制到NEW_table
表中。

3、使用 Oracle INSERT ALL 语句将多行数据插入到一个或多个表中

有时我们可能希望将多行插入到一个或多个表中。在这种情况下，就可以使用Oracle INSERT ALL语句，该语句也被称为多项式插入语句。

Oracle提供了两种类型的多项式插入语句：无条件的和有条件的。

★无条件的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必须引用由子查询的选择列表返回的列对应的值，Subquery是需要添加的条件。

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

SELECT * FROM dual;

示例演示如何将多行插入到表中如下（无条件）：

Oracle数据库官方SQL文档举例脚本如下：

--基础数据
SELECT * FROM sales_input_table;


PRODUCT_ID CUSTOMER_ID WEEKLY_ST  SALES_SUN  SALES_MON  SALES_TUE  SALES_WED SALES_THU  SALES_FRI  SALES_SAT
---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ----------
       111         222 01-OCT-00        100        200        300        400       500        600        700
       222         333 08-OCT-00        200        300        400        500       600        700        800
       333         444 15-OCT-00        300        400        500        600       700        800        900


--多表insert语句       
INSERT ALL
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date, sales_sun)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
   SELECT product_id, customer_id, weekly_start_date, sales_sun,
      sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
      FROM sales_input_table;
      
--执行结果如下
SELECT * FROM sales
   ORDER BY prod_id, cust_id, time_id;


   PROD_ID    CUST_ID TIME_ID   C   PROMO_ID QUANTITY_SOLD     AMOUNT       COST
---------- ---------- --------- - ---------- ------------- ---------- ----------
       111        222 01-OCT-00                                   100
       111        222 02-OCT-00                                   200
       111        222 03-OCT-00                                   300
       111        222 04-OCT-00                                   400
       111        222 05-OCT-00                                   500
       111        222 06-OCT-00                                   600
       111        222 07-OCT-00                                   700
       222        333 08-OCT-00                                   200
       222        333 09-OCT-00                                   300
       222        333 10-OCT-00                                   400
       222        333 11-OCT-00                                   500
       222        333 12-OCT-00                                   600
       222        333 13-OCT-00                                   700
       222        333 14-OCT-00                                   800
       333        444 15-OCT-00                                   300
       333        444 16-OCT-00                                   400
       333        444 17-OCT-00                                   500
       333        444 18-OCT-00                                   600
       333        444 19-OCT-00                                   700
       333        444 20-OCT-00                                   800
       333        444 21-OCT-00                                   900

在Oracle 11g环境下进行实际测试，首先，创建三个名为fruits_01、fruits_02、fruits_03的新表：

CREATE TABLE fruits_01 (
    fruit_name VARCHAR(100),
    color VARCHAR(100) NOT NULL
);


CREATE TABLE fruits_02 (
    fruit_name VARCHAR(100),
    color VARCHAR(100) NOT NULL
);


CREATE TABLE fruits_03 (
    fruit_name VARCHAR(100),
    color VARCHAR(100) NOT NULL
);

其次，使用Oracle INSERT ALL语句将行插入到表中：

--批量往fruits_01表插入数据
INSERT ALL 
    INTO fruits_01(fruit_name, color)
    VALUES ('苹果','红色') 


    INTO fruits_01(fruit_name, color)
    VALUES ('橙子','青色') 


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


--根据数据表fruits_01内的数据往fruits_02表插入数据
INSERT ALL 
    INTO fruits_02(fruit_name, color)
    INTO fruits_02(fruit_name, color)
    INTO fruits_02(fruit_name, color)
SELECT * FROM fruits_01;

最后，查询fruits_01及fruits_02表数据来验证插入结果：

SELECT * FROM fruits_01;
SELECT * FROM fruits_02;

执行上面查询语句，得到以下结果：

还可以将多行插入到多个表中

还可以使用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; --子条件查询
--继续以上数据表格举例
INSERT ALL 
    INTO fruits_02(fruit_name, color)
    INTO fruits_03(fruit_name, color)
SELECT * FROM fruits_01;

★有条件的Oracle INSERT ALL | FIRST语句

有条件多项插入语句允许根据指定的条件将行插入到表中。关键字ALL告诉数据库，检查WHEN后面的每一个条件。

以下显示了条件多项插入语句的语法：

--解释
1. insert first: 仅对 '第一个' 匹配成功项进行插入
2. 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，则数据库将评估每个WHEN子句，而不考虑任何其他WHEN子句的评估结果。对于条件评估为true的每个WHEN子句，数据库执行相应的INTO子句列表。

但是，如果指定FIRST关键字时，则数据库将按照每个WHEN子句在语句中出现的顺序对其进行计算。对于计算结果为true的第一个WHEN子句，数据库将执行相应的INTO子句，并跳过后续WHEN给定行的子句。

请注意，单条件多项式插入语句最多可以有127个WHEN子句。

举例：
接下来的示例演示插入到多个表中。假设您想向销售代表提供一些不同规模订单的信息。以下示例为小订单、中订单、大订单和特殊订单创建表：

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


CREATE TABLE medium_orders AS SELECT * FROM small_orders;


CREATE TABLE large_orders AS SELECT * FROM small_orders;


CREATE TABLE special_orders 
   (order_id       NUMBER(12)    NOT NULL,
    customer_id    NUMBER(6)     NOT NULL,
    order_total    NUMBER(8,2),
    sales_rep_id   NUMBER(6),
    credit_limit   NUMBER(9,2),
    cust_email     VARCHAR2(40)
   );

使用INSERT ALL将orders表内的数据按order_total的订单总数划分并插入多表填充小、中、大订单的表:

-- 对 '每个' 匹配成功项都进行插入
INSERT ALL
   WHEN order_total <= 100000 THEN
      INTO small_orders
   WHEN order_total > 1000000 AND order_total <= 200000 THEN
      INTO medium_orders
   WHEN order_total > 200000 THEN
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

通过使用ELSE子句插入到large_orders表中，这样也可以达到相同的结果，脚本如下：

-- 对 '每个' 匹配成功项都进行插入
INSERT ALL
   WHEN order_total <= 100000 THEN
      INTO small_orders
   WHEN order_total > 100000 AND order_total <= 200000 THEN
      INTO medium_orders
   ELSE
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

此示例，向小型、中型和大型表中插入数据，并将大于290,000的订单放入special_orders
数据表中。此表还显示了如何使用列别名来简化语句：

-- 对 '每个' 匹配成功项都进行插入
INSERT ALL
   WHEN ottl <= 100000 THEN
      INTO small_orders
         VALUES(oid, ottl, sid, cid)
   WHEN ottl > 100000 and ottl <= 200000 THEN
      INTO medium_orders 
         VALUES(oid, ottl, sid, cid)
   WHEN ottl > 200000 THEN
      into large_orders
         VALUES(oid, ottl, sid, cid)
   WHEN ottl > 290000 THEN
      INTO special_orders
   SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
      o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
      FROM orders o, customers c
      WHERE o.customer_id = c.customer_id;

有条件的Oracle INSERT FIRST示例，‍考虑下面的例子，如果第一个 WHEN 子句的值为 true，Oracle 服务器对于给定的行执行相应的 INTO 子句，并且跳过后面的 WHEN 子句(后面的WHEN 语句都不再考虑满足第一个WHEN 子句的记录，即使该记录满足WHEN 语句中的条件)：

-- 仅对 '第一个' 匹配成功项进行插入
INSERT FIRST
   WHEN ottl <= 100000 THEN
      INTO small_orders
         VALUES(oid, ottl, sid, cid)
   WHEN ottl > 100000 and ottl <= 200000 THEN
      INTO medium_orders
         VALUES(oid, ottl, sid, cid)
   WHEN ottl > 290000 THEN
      INTO special_orders
   WHEN ottl > 200000 THEN
      INTO large_orders
         VALUES(oid, ottl, sid, cid)
   SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
      o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
      FROM orders o, customers c
      WHERE o.customer_id = c.customer_id;

最后，需注意：Oracle多表插入语句受以下主要限制：

只能对表执行多表插入，不能对视图或物化视图执行多表插入。
不能在远程表中执行多表插入。
一个表集合表达式不能在一个多表插入语句中使用。
多表insert语句不支持计划稳定性。
不能在多表insert语句的任何部分指定序列。多表插入被认为是一条SQL语句。

文章参考资料：https://docs.oracle.com/database/121/SQLRF/statements_9015.htm#SQLRF01604

点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。

动动小手点击加关注呦☟☟☟

文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle中INSERT ALL / FIRST语法详解，将多行插入多表中，全文四千字总结，建议收藏！

评论