• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
123456881 显示全部楼层 发表于 2021-8-14 14:36:32 |阅读模式 打印 上一主题 下一主题
目次


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

各人好!我是只谈技能不剃头的 Tony 老师。
ETL(提取、转换、加载)是指从源系统中提取数据并将其放入数据仓库的过程。Oracle 数据库为 ETL 流程提供了丰富的功能,本日我们就给各人介绍一下 Oracle 多表插入语句,也就是INSERT ALL 语句。

创建示例表

我们首先创建一个源数据表和三个目的表:
  1. CREATE TABLE src_table(
  2.   id INTEGER NOT NULL PRIMARY KEY,
  3.   name VARCHAR2(10) NOT NULL
  4. );
  5. INSERT INTO src_table VALUES (1, '张三');
  6. INSERT INTO src_table VALUES (2, '李四');
  7. INSERT INTO src_table VALUES (3, '王五');
  8. CREATE TABLE tgt_t1 AS
  9. SELECT * FROM src_table WHERE 1=0;
  10. CREATE TABLE tgt_t2 AS
  11. SELECT * FROM src_table WHERE 1=0;
  12. CREATE TABLE tgt_t3 AS
  13. SELECT * FROM src_table WHERE 1=0;
复制代码
无条件的 INSERT ALL 语句

INSERT ALL 语句可以用于将多行输入插入一个或者多个表中,因此也被称为多表插入语句。第一种形式的
  1. INSERT ALL
复制代码
语句是无条件的插入语句,源数据中的每一行数据都会被插入到每个目的表中。比方:
  1. INSERT ALL
  2.   INTO tgt_t1(id, name) VALUES(id, name)
  3.   INTO tgt_t2(id, name) VALUES(id, name)
  4.   INTO tgt_t3(id, name) VALUES(id, name)
  5. SELECT * FROM src_table;
  6. SELECT * FROM tgt_t1;
  7. ID|NAME  |
  8. --|------|
  9. 1|张三  |
  10. 2|李四  |
  11. 3|王五  |
  12. SELECT * FROM tgt_t2;
  13. ID|NAME  |
  14. --|------|
  15. 1|张三  |
  16. 2|李四  |
  17. 3|王五  |
  18. SELECT * FROM tgt_t3;
  19. ID|NAME  |
  20. --|------|
  21. 1|张三  |
  22. 2|李四  |
  23. 3|王五  |
复制代码
执行以上多表插入语句之后,三个目的表中都天生了 3 条记录。
我们也可以多次插入相同的表,实现一个插入语句插入多行数据的结果。比方:
  1. TRUNCATE TABLE tgt_t1;
  2. INSERT ALL
  3.   INTO tgt_t1(id, name) VALUES(4, '赵六')
  4.   INTO tgt_t1(id, name) VALUES(5, '孙七')
  5.   INTO tgt_t1(id, name) VALUES(6, '周八')
  6. SELECT 1 FROM dual;
  7. SELECT * FROM tgt_t1;
  8. ID|NAME  |
  9. --|------|
  10. 4|赵六  |
  11. 5|孙七  |
  12. 6|周八  |
复制代码
在以上插入语句中,tgt_t1 出现了三次,最终在该表中插入了 3 条记录。这种语法和其他数据库中的以下多行插入语句结果相同:
  1. -- MySQL、SQL Server、PostgreSQL以及SQLite
  2. INSERT INTO tgt_t1(id, name)
  3. VALUES(4, '赵六'), (5, '孙七'), (6, '周八');
复制代码
别的,这种无条件的 INSERT ALL 语句还可以实现列转行(PIVOT)的功能。比方:
  1. CREATE TABLE src_pivot(
  2.   id INTEGER NOT NULL PRIMARY KEY,
  3.   name1 VARCHAR2(10) NOT NULL,
  4.   name2 VARCHAR2(10) NOT NULL,
  5.   name3 VARCHAR2(10) NOT NULL
  6. );
  7. INSERT INTO src_pivot VALUES (1, '张三', '李四', '王五');
  8. TRUNCATE TABLE tgt_t1;
  9. INSERT ALL
  10.   INTO tgt_t1(id, name) VALUES(id, name1)
  11.   INTO tgt_t1(id, name) VALUES(id, name2)
  12.   INTO tgt_t1(id, name) VALUES(id, name3)
  13. SELECT * FROM src_pivot;
  14. SELECT * FROM tgt_t1;
  15. ID|NAME  |
  16. --|------|
  17. 1|张三  |
  18. 1|李四  |
  19. 1|王五  |
复制代码
src_pivot 表中包含了 3 个名字字段,我们通过
  1. INSERT ALL
复制代码
语句将其转换 3 行记录。

有条件的 INSERT ALL 语句

第一种形式的 INSERT ALL 语句是有条件的插入语句,可以将满足不同条件的数据插入不同的表中。比方:
  1. TRUNCATE TABLE tgt_t1;
  2. TRUNCATE TABLE tgt_t2;
  3. TRUNCATE TABLE tgt_t3;
  4. INSERT ALL
  5.   WHEN id <= 1 THEN
  6.     INTO tgt_t1(id, name) VALUES(id, name)
  7.   WHEN id BETWEEN 1 AND 2 THEN
  8.     INTO tgt_t2(id, name) VALUES(id, name)
  9.   ELSE
  10.     INTO tgt_t3(id, name) VALUES(id, name)
  11. SELECT * FROM src_table;
  12. SELECT * FROM tgt_t1;
  13. ID|NAME  |
  14. --|------|
  15. 1|张三  |
  16. SELECT * FROM tgt_t2;
  17. ID|NAME  |
  18. --|------|
  19. 1|张三  |
  20. 2|李四  |
  21. SELECT * FROM tgt_t3;
  22. ID|NAME  |
  23. --|------|
  24. 3|王五  |
复制代码
tgt_t1 中插入了 1 条数据,由于 id 小于即是 1 的记录只有 1 个。tgt_t2 中插入了 2 条数据,包括 id 即是 1 的记录。也就是说,前面的 WHEN 子句不会影响后续的条件判断,每个条件都会单独进行判断。tgt_t3 中插入了 1 条数据,ELSE 分支只会插入不满足前面全部条件的数据。
[code]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作