Oracle 数据仓库ETL技能之多表插入语句的示例详解

显示全部楼层

创建示例表
无条件的 INSERT ALL 语句
有条件的 INSERT ALL 语句
有条件的 INSERT FIRST 语句
多表插入语句的限制

各人好！我是只谈技能不剃头的 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 分支只会插入不满足前面全部条件的数据。
[code]

工作时间

工作时间

Oracle 数据仓库ETL技能之多表插入语句的示例详解

本帖子中包含更多资源

火星云矿 | 预约S19Pro，享500抵1000！