Oracle insert all 讲解

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;

执行结果