• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Oracle 数据仓库ETL技术之多表插入语句的示例详解

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

    到此这篇关于Oracle 数据仓库 ETL 技术之多表插入语句的示例详解的文章就介绍到这了,更多相关Oracle 多表插入内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

    您可能感兴趣的文章:
    • Oracle中多表关联批量插入批量更新与批量删除操作
    • Oracle数据仓库的分层管理器解决方案开发者网络Oracle
    • Oracle数据库恢复教程之resetlogs操作
    上一篇:SQL PLUS基本命令的使用方法示例
    下一篇:oracle表分区的概念及操作
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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