INSERT ALL&FIRST子句语法
功能描述
Insert All&First语句允许用户在一条Insert语句中往不同的表同时插入数据，也可以实现有条件的插入。
Insert All表示所有满足条件的插入语句都会被执行，并且INSERT All支持针对指定字段批量插入数据。
Insert First在执行第一个满足条件的插入语句后就会结束。
语法格式
Insert [ First | All ]
[ WHEN { Condition } THEN INTO table [ [ AS ] alias ] [(column_name[,...])] Values (v1, ...), ...]
{ Subquery }
参数说明
WHEN { Condition }
任意一个判断表达式。
表达式中可以使用常量或引用查询中的表字段。
alias
表的别名。
column_name[,…]
指定字段的名称序列。
指定字段功能即column_name选项仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。
指定字段功能暂不支持视图操作、自增列和with语法。
Subquery
子查询语句。
示例
示例1： 实现有条件的往不同的表批量插入数据。
1、创建测试表并插入数据。
CREATE TABLE small_orders
( order_id NUMERIC NOT NULL,
customer_id NUMERIC NOT NULL,
order_total NUMERIC );
CREATE TABLE medium_orders AS SELECT * FROM small_orders;
CREATE TABLE large_orders AS SELECT * FROM small_orders;
CREATE TABLE special_orders
( order_id NUMERIC NOT NULL,
customer_id NUMERIC NOT NULL,
order_total NUMERIC,
other NUMERIC
);
CREATE TABLE orders
( order_id NUMERIC NOT NULL,
customer_id NUMERIC NOT NULL,
order_total NUMERIC ,
other NUMERIC );
INSERT INTO orders SELECT 1, 1, 10000, 1 ;
INSERT INTO orders SELECT 1, 1, 20000, 1 ;
INSERT INTO orders SELECT 1, 1, 30000, 1 ;
INSERT INTO orders SELECT 1, 1, 210000, 1 ;
INSERT INTO orders SELECT 1, 1, 220000, 1 ;
INSERT INTO orders SELECT 1, 1, 110000, 1 ;
INSERT INTO orders SELECT 1, 1, 120000, 1 ;
INSERT INTO orders SELECT 1, 1, 130000, 1 ;
INSERT INTO orders SELECT 1, 1, 140000, 1 ;
INSERT INTO orders SELECT 1, 1, 140000, 1 ;
INSERT INTO orders SELECT 1, 1, 340000, 1 ;
2、使用INSERT ALL向不同表中插入数据。
INSERT ALL
WHEN ottl < 100000 THEN
INTO small_orders
VALUES(oid, ottl, cid)
WHEN ottl > 100000 and ottl < 200000 THEN
INTO medium_orders
VALUES(oid, ottl, cid)
WHEN ottl > 200000 THEN
into large_orders
VALUES(oid, ottl, cid)
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.other
FROM orders o;
3、查询INSERT ALL结果。
select * from small_orders;
select * from medium_orders;
select * from large_orders;
select * from special_orders;
返回结果依次如下，则表示插入数据成功:
order_id | customer_id | order_total
----------+-------------+-------------
1 | 10000 | 1
1 | 20000 | 1
1 | 30000 | 1
(3 rows)
order_id | customer_id | order_total
----------+-------------+-------------
1 | 110000 | 1
1 | 120000 | 1
1 | 130000 | 1
1 | 140000 | 1
1 | 140000 | 1
(5 rows)
order_id | customer_id | order_total
----------+-------------+-------------
1 | 210000 | 1
1 | 220000 | 1
1 | 340000 | 1
(3 rows)
order_id | customer_id | order_total | other
----------+-------------+-------------+-------
(0 rows)
4、使用INSERT FIRST向不同表中插入数据。
INSERT First
WHEN ottl > 10000 THEN
INTO small_orders
VALUES(oid, ottl, cid)
INTO small_orders
VALUES(oid, ottl, cid)
INTO small_orders
VALUES(oid, ottl, cid)
WHEN ottl > 100000 THEN
INTO medium_orders
VALUES(oid, ottl, cid)
WHEN ottl > 200000 THEN
into large_orders
VALUES(oid, ottl, cid)
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.other
FROM orders o;
5、查询INSERT First结果。
small_orders表
select * from small_orders;返回结果如下，则表示插入数据成功:
order_id | customer_id | order_total ----------+-------------+------------- 1 | 10000 | 1 1 | 20000 | 1 1 | 30000 | 1 1 | 20000 | 1 1 | 20000 | 1 1 | 20000 | 1 1 | 30000 | 1 1 | 30000 | 1 1 | 30000 | 1 1 | 210000 | 1 1 | 210000 | 1 1 | 210000 | 1 1 | 220000 | 1 1 | 220000 | 1 1 | 220000 | 1 1 | 110000 | 1 1 | 110000 | 1 1 | 110000 | 1 1 | 120000 | 1 1 | 120000 | 1 1 | 120000 | 1 1 | 130000 | 1 1 | 130000 | 1 1 | 130000 | 1 1 | 140000 | 1 1 | 140000 | 1 1 | 140000 | 1 1 | 140000 | 1 1 | 140000 | 1 1 | 140000 | 1 1 | 340000 | 1 1 | 340000 | 1 1 | 340000 | 1 (33 rows)medium_orders表
select * from medium_orders;返回结果如下（与第三步查询结果相同时），则表示语句执行成功：
order_id | customer_id | order_total ----------+-------------+------------- 1 | 110000 | 1 1 | 120000 | 1 1 | 130000 | 1 1 | 140000 | 1 1 | 140000 | 1 (5 rows)large_orders表
select * from large_orders;返回结果如下（与第三步查询结果相同时），则表示语句执行成功：
order_id | customer_id | order_total ----------+-------------+------------- 1 | 210000 | 1 1 | 220000 | 1 1 | 340000 | 1 (3 rows)special_orders表
select * from special_orders;返回结果如下（与第三步查询结果相同时），则表示语句执行成功：
order_id | customer_id | order_total | other ----------+-------------+-------------+------- (0 rows)
示例2： 使用insert all向不同的表批量指定字段插入数据。
1、创建测试表1、表2。
create table test1
(id int,
sign_in date,
name varchar2(128),
last_id int,
last_name varchar2(128)
);
create table test2
(id int,
sign_in date,
name varchar2(128),
last_id int,
last_name varchar2(128)
);
2、指定字段批量插入数据。
INSERT ALL INTO test1 as TODO (id,sign_in,name,last_id,last_name)
VALUES (1,sysdate,'a',3,'c')
INTO test1 (id,sign_in,name)
VALUES (2,sysdate,'b')
INTO test2 (id,sign_in,name)
VALUES (1,sysdate,'a')
INTO test2 (id,sign_in,name,last_id,last_name)
VALUES (1,sysdate,'a',2,'b')
select 1 from dual;
3、查看插入数据结果。
select * from test1;
select * from test2;
显示结果为：
id | sign_in | name | last_id | last_name
----+---------------+------+---------+-----------
1 | 2022-10-14 14:23:31 | a | 3 | c
2 | 2022-10-14 14:23:31 | b | |
(2 rows)
id | sign_in | name | last_id | last_name
----+---------------+------+---------+-----------
1 | 2022-10-14 14:23:31 | a | |
1 | 2022-10-14 14:23:31 | a | 2 | b
(2 rows)
