Oracle 数据仓库 ETL 技术之多表插入语句

大家好！我是只谈技术不剪发的 Tony 老师。

ETL（提取、转换、加载）是指从源系统中提取数据并将其放入数据仓库的过程。Oracle 数据库为 ETL 流程提供了丰富的功能，今天我们就给大家介绍一下 Oracle 多表插入语句，也就是 INSERT ALL 语句。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁

创建示例表

我们首先创建一个源数据表和三个目标表：

CREATE TABLE src_table(
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR2(10) NOT NULL
);
INSERT INTO src_table VALUES (1, '张三');
INSERT INTO src_table VALUES (2, '李四');
INSERT INTO src_table VALUES (3, '王五');

CREATE TABLE tgt_t1 AS
SELECT * FROM src_table WHERE 1=0;

CREATE TABLE tgt_t2 AS
SELECT * FROM src_table WHERE 1=0;

CREATE TABLE tgt_t3 AS
SELECT * FROM src_table WHERE 1=0;

无条件的 INSERT ALL 语句

INSERT ALL 语句可以用于将多行输入插入一个或者多个表中，因此也被称为多表插入语句。第一种形式的 INSERT ALL 语句是无条件的插入语句，源数据中的每一行数据都会被插入到每个目标表中。例如：

INSERT ALL
  INTO tgt_t1(id, name) VALUES(id, name)
  INTO tgt_t2(id, name) VALUES(id, name)
  INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

SELECT * FROM tgt_t2;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

SELECT * FROM tgt_t3;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

执行以上多表插入语句之后，三个目标表中都生成了 3 条记录。

我们也可以多次插入相同的表，实现一个插入语句插入多行数据的效果。例如：

TRUNCATE TABLE tgt_t1;

INSERT ALL
  INTO tgt_t1(id, name) VALUES(4, '赵六')
  INTO tgt_t1(id, name) VALUES(5, '孙七')
  INTO tgt_t1(id, name) VALUES(6, '周八')
SELECT 1 FROM dual;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 4|赵六  |
 5|孙七  |
 6|周八  |

在以上插入语句中，tgt_t1 出现了三次，最终在该表中插入了 3 条记录。这种语法和其他数据库中的以下多行插入语句效果相同：

-- MySQL、SQL Server、PostgreSQL以及SQLite
INSERT INTO tgt_t1(id, name)
VALUES(4, '赵六'), (5, '孙七'), (6, '周八');

另外，这种无条件的 INSERT ALL 语句还可以实现列转行（PIVOT）的功能。例如：

CREATE TABLE src_pivot(
  id INTEGER NOT NULL PRIMARY KEY,
  name1 VARCHAR2(10) NOT NULL,
  name2 VARCHAR2(10) NOT NULL,
  name3 VARCHAR2(10) NOT NULL
);
INSERT INTO src_pivot VALUES (1, '张三', '李四', '王五');

TRUNCATE TABLE tgt_t1;

INSERT ALL
  INTO tgt_t1(id, name) VALUES(id, name1)
  INTO tgt_t1(id, name) VALUES(id, name2)
  INTO tgt_t1(id, name) VALUES(id, name3)
SELECT * FROM src_pivot;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1|张三  |
 1|李四  |
 1|王五  |

src_pivot 表中包含了 3 个名字字段，我们通过 INSERT ALL 语句将其转换 3 行记录。

有条件的 INSERT ALL 语句

第一种形式的 INSERT ALL 语句是有条件的插入语句，可以将满足不同条件的数据插入不同的表中。例如：

TRUNCATE TABLE tgt_t1;
TRUNCATE TABLE tgt_t2;
TRUNCATE TABLE tgt_t3;

INSERT ALL
  WHEN id <= 1 THEN
    INTO tgt_t1(id, name) VALUES(id, name)
  WHEN id BETWEEN 1 AND 2 THEN
    INTO tgt_t2(id, name) VALUES(id, name)
  ELSE
    INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1|张三  |
 
SELECT * FROM tgt_t2;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |

SELECT * FROM tgt_t3;
ID|NAME  |
--|------|
 3|王五  |

tgt_t1 中插入了 1 条数据，因为 id 小于等于 1 的记录只有 1 个。tgt_t2 中插入了 2 条数据，包括 id 等于 1 的记录。也就是说，前面的 WHEN 子句不会影响后续的条件判断，每个条件都会单独进行判断。tgt_t3 中插入了 1 条数据，ELSE 分支只会插入不满足前面所有条件的数据。

📝有条件的多表插入语句最多支持 127 个 WHEN 子句。

有条件的 INSERT FIRST 语句

有条件的 INSERT FIRST 的原理和 CASE 表达式类似，只会执行第一个满足条件的插入语句，然后继续处理源数据中的其他记录。例如：

TRUNCATE TABLE tgt_t1;
TRUNCATE TABLE tgt_t2;
TRUNCATE TABLE tgt_t3;

INSERT FIRST
  WHEN id <= 1 THEN
    INTO tgt_t1(id, name) VALUES(id, name)
  WHEN id BETWEEN 1 AND 2 THEN
    INTO tgt_t2(id, name) VALUES(id, name)
  ELSE
    INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1|张三  |
 
SELECT * FROM tgt_t2;
ID|NAME  |
--|------|
 2|李四  |

SELECT * FROM tgt_t3;
ID|NAME  |
--|------|
 3|王五  |

以上语句和上一个示例的差别在于源数据中的每个记录只会插入一次，tgt_t2 中不会插入 id 等于 1 的数据。

多表插入语句的限制

Oracle 多表插入语句存在以下限制：

• 多表插入只能针对表执行插入操作，不支持视图或者物化视图。
• 多表插入语句不能通过 DB Link 针对远程表执行插入操作。
• 多表插入语句不能通针对嵌套表执行插入操作。
• 所有 INSERT INTO 子句中的字段总数量不能超过 999 个。
• 多表插入语句中不能使用序列。多表插入语句被看作是单个语句，因此只会产生一个序列值并且用于所有的数据行，这样会导致数据问题。
• 多表插入语句不能和执行计划稳定性功能一起使用。
• 如果任何目标并使用了 PARALLEL 提示，整个语句都会被并行化处理。如果没有目标表使用 PARALLEL 提示，只有定义了 PARALLEL 属性的目标表才会被并行化处理。
• 如果多表插入语句中的任何表是索引组织表，或者定义了位图索引，都不会进行并行化处理。