亲宝软件园·资讯

展开

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

不剪发的Tony老师 人气:0
想了解Oracle 数据仓库ETL技术之多表插入语句的示例详解的相关内容吗,不剪发的Tony老师在本文为您仔细讲解Oracle 多表插入的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:Oracle,多表插入,Oracle,数据仓库ETL技术,Oracle,数据仓库,下面大家一起来学习吧。

dwh

大家好!我是只谈技术不剪发的 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 多表插入语句存在以下限制:

加载全部内容

相关教程
猜你喜欢
用户评论