Oracle 数据仓库 ETL 技术之多表插入语句_不剪发的Tony老师的博客

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
创建示例表

我们首先创建一个源数据表和三个目标表：
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)
    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)
    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 属性的目标表才会被并行化处理。
如果多表插入语句中的任何表是索引组织表，或者定义了位图索引，都不会进行并行化处理。
                                    方案理念--四化理念数据规格化处理自动化信息集中化操作人性化架构通过视图实现来至两个不同数据库的表的结构完全一致, 在结构完全相同的两个表之间进行数据同步, 问题变得相当简单. 同步代码如下.ETL---初始同步delete from ods_table;insert into v_tableselect * from db_table;commit;---新增同步insert into v_t...
                                    多表插入语句用于一次性有选择的将数据分别插入到多张表中。多表插入包含以下几种：无条件insert all有条件insert all有条件insert first以下用Oracle内置的示例用户scott和表emp来测试其用法：sqlplus scott/tigerselect * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO------...
                                    前言【IT168 专稿】经常听到客户抱怨一些企业级的数据仓库软件许可证过于昂贵；也曾经见到过一次性的Access到 Oracle的数据迁移就使用了DataStage(这是真的！在发现 Server版本不能连接Access后，还专门购买了DataStage for ODBC --__--!)。实际上一些简单的ETL流程完全可以使用Oracle内建的功能完成。本文的主旨在于说明这一思想，文中代码未必是...
2.并行：merge
3.统计函数：Rank,rollup, cube
4.move data:TTS tablespace,merge/exchage partition,external table
5.不用cursor;用view, global temp table
6.MQT,可以有效加快统计类报表的速度
HW建议：
1.使用游标来处理逻辑，用游
        基于图形框架，自主开发实现的一款图形化ETL工具，支持Greenplum、Oracle等数据库（可以根据需求添加数据库驱动）。主要分为DataFlow Designer，DataFlow Director，DataFlow Manager 三个主要功能模块。支持灵活高效的ETL任务配置，任务调度监控，权限管理等。
DataFlow Designer功能界面：
                                    ETL，Extraction-Transformation-Loading的缩写，中文名称为数据抽取、转换和加载。ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。ETL是数据仓库中的非常重要的一环。它是承前启后的必要的一步。相对于关系数据库，数据仓库技术没有严格的数学理论基础，...
                                    如何在sql查询中传递java字符串变量.我已经完成了所有的JDBC连接.我的sql数据库查询是sql = "Select *from production AS custINNER JOIN location AS compON cust.location_id = comp.location_idwhere comp.name = locationnames AND crop_id =1";它...
                                    《ETL数据抽取方案简介》由会员分享，可在线阅读，更多相关《ETL数据抽取方案简介(5页珍藏版)》请在人人文库网上搜索。1、目目录录 ETLETL 简介简介 .4 ETLETL 抽取方案抽取方案.4 1.数据抽取.4 2.数据转换和加工.7 3.数据装载.7 4.时间戳方式 .7 5.全量删除插入方式.8 6.全量比对方式.8 . . ETL 简介 数据集成是把不同来源、格式和特点的数据在逻辑上或...