目次
- 创建示例表
- 无条件的 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
- 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 个名字字段,我们通过复制代码 语句将其转换 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] |