1、概念
Oracle 中insert all
是指把 同一批
数据插入到 不同的表
中。
假如,现在有个需求,把表 t
的中数据分别插入到 t1、t2
,如果你不知道 insert all
, 你可能会使用 insert 插入 2
次,例如:
INSERT INTO t1(object_id, object_name) SELECT * FROM t;
INSERT INTO t2(object_id, object_name) SELECT * FROM t;
COMMIT;
其实,以上这样的写法,不一定正确。在 两次 insert
过程中, 有可能 t
表的数据发生了改变,从而导致 t1、t2
表得到的数据不一样,正确的写法是用 insert all
INSERT ALL
INTO t1(object_id, object_name)
INTO t2(object_id, object_name)
SELECT * FROM t;
COMMIT;
1.1 思维导图
1.2 基础数据
DROP TABLE stu; -- if exists
CREATE TABLE stu (
s_id NUMBER,
s_xm VARCHAR2(30)
);
INSERT INTO stu(s_id, s_xm) VALUES (1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES (2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES (3, '小倩子');
COMMIT;
2、实例分析
2.1 无条件插入
CREATE TABLE stu1 AS SELECT * from stu WHERE 1 = 2;
CREATE TABLE stu2 AS SELECT * from stu WHERE 1 = 2;
INSERT ALL
INTO stu1(s_id, s_xm)
INTO stu2(s_id, s_xm)
SELECT * FROM stu;
COMMIT;
SELECT * FROM stu1;
SELECT * FROM stu2;
执行结果
2.2 有条件插入
2.2.1 insert first
对于每一行数据,只插入到 第一个when
条件成立的表,不继续检查其他条件。
DROP TABLE stu1; -- if exists
DROP TABLE stu2; -- if exists
CREATE TABLE stu1 AS SELECT * FROM stu WHERE 1 = 2;
CREATE TABLE stu2 AS SELECT * FROM stu WHERE 1 = 2;
INSERT FIRST
WHEN s_id <= 2 THEN
INTO stu1 (s_id, s_xm)
WHEN s_id >= 2 THEN -- 注意 2 是重复的哦
INTO stu2 (s_id, s_xm)
SELECT * FROM stu;
SELECT * FROM stu1;
SELECT * FROM stu2;
执行结果
2.2.2 insert all
对于每一行数据,对 每一个when
条件都进行检查,如果满足条件就执行插入操作。
-- 仅将上述 FIRST 改为 ALL
DROP TABLE stu1; -- if exists
DROP TABLE stu2; -- if exists
CREATE TABLE stu1 AS SELECT * FROM stu WHERE 1 = 2;
CREATE TABLE stu2 AS SELECT * FROM stu WHERE 1 = 2;
INSERT ALL
WHEN s_id <= 2 THEN
INTO stu1 (s_id, s_xm)
WHEN s_id >= 2 THEN -- 注意 2 是重复的哦
INTO stu2 (s_id, s_xm)
SELECT * FROM stu;
SELECT * FROM stu1;
SELECT * FROM stu2;
执行结果
3、其他操作
3.1 行转列插入
DROP TABLE stu1; -- if exists
DROP TABLE stu2; -- if exists
CREATE TABLE stu1 (
s_id NUMBER,
s_xm VARCHAR(30) -- 数据类型要一致哦
);
CREATE TABLE stu2 (
s_id NUMBER,
s_xm1 VARCHAR2(30),
s_xm2 VARCHAR2(30),
s_xm3 VARCHAR2(30)
);
INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (1, 'a1', 'b1', 'c1');
INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (2, 'a2', 'b2', 'c2');
INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (3, 'a3', 'b3', 'c3');
INSERT ALL
INTO stu1 VALUES (s_id, s_xm1)
INTO stu1 VALUES (s_id, s_xm2)
INTO stu1 VALUES (s_id, s_xm3)
SELECT * FROM stu2;
SELECT * FROM stu1;
执行结果