【转载】多表插入INSERT ALL…

在数据仓库中的转换和装载过程中,可能会使用INSERT ALL语句,这篇文章简单介绍一下INSERT ALL语句。但在ETL工具中很多组件也有类似的功能,实现逻辑雷同。

虽然INSERT ALL是9i新增的语法,它扩充了原有的INSERT语句,使得INSERT语句从原来的只能插入到一张表发展到可以同时插入多张表,还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。

1、不带条件的INSERT ALL用法:

SQL> CREATE TABLE TABLE_STORAGE
  (
  TABLE_NAME VARCHAR2(30),
  TABLESPACE_NAME VARCHAR2(30),
  PCT_FREE NUMBER,
  PCT_USED NUMBER,
  INI_TRANS NUMBER,
  MAX_TRANS NUMBER,
  INITIAL_EXTENT NUMBER,
 10  NEXT_EXTENT NUMBER,
 11  MIN_EXTENTS NUMBER,
 12  MAX_EXTENTS NUMBER,
 13  PCT_INCREASE NUMBER,
 14  FREELISTS NUMBER,
 15  FREELIST_GROUPS NUMBER
 16  );

表已创建。

SQL> CREATE TABLE TABLE_STAT
  (
  TABLE_NAME VARCHAR2(30),
  NUM_ROWS NUMBER,
  BLOCKS NUMBER,
  EMPTY_BLOCKS NUMBER,
  AVG_SPACE NUMBER,
  CHAIN_CNT NUMBER,
  AVG_ROW_LEN NUMBER
 10  );

表已创建。

SQL> INSERT ALL
  INTO TABLE_STORAGE VALUES (TABLE_NAME, TABLESPACE_NAME, PCT_FREE, PCT_USED,
  INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,PCT_INCREASE, FREELISTS, FREELIST_GROUPS)
  INTO TABLE_STAT VALUES (TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN)
  SELECT * FROM USER_TABLES;

 

已创建54行。

SQL> SELECT COUNT(*) FROM TABLE_STORAGE;

  COUNT(*)
----------
        12

SQL> SELECT COUNT(*) FROM TABLE_STAT;

  COUNT(*)
----------
        12

SQL> SELECT COUNT(*) FROM USER_TABLES;

  COUNT(*)
----------
        12

SQL> DROP TABLE TABLE_STAT;

表已丢弃。

SQL> DROP TABLE TABLE_STORAGE;

表已丢弃。


2、带条件的INSERT ALL用法:


SQL> CREATE TABLE TABLE_ALL (TABLE_NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE INDEX_ALL (INDEX_NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE OBJECT_OTHER (OBJECT_NAME VARCHAR2(30), OBJECT_TYPE VARCHAR2(30));

表已创建。

SQL> INSERT ALL 
  WHEN (object_type = 'TABLE') THEN INTO table_all VALUES  (object_name) 
  WHEN (object_type = 'INDEX') THEN INTO index_all VALUES  (object_name) 
  ELSE INTO object_other (OBJECT_NAME, OBJECT_TYPE)
  SELECT object_name, object_type FROM user_objects;

已创建91行。

SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';

  COUNT(*)
----------
        27

SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX';

  COUNT(*)
----------
        14

SQL> SELECT COUNT(*) FROM USER_OBJECTS
  WHERE OBJECT_TYPE NOT IN ('TABLE', 'INDEX');

  COUNT(*)
----------
        50

SQL> SELECT COUNT(*) FROM TABLE_ALL;

  COUNT(*)
----------
        27

SQL> SELECT COUNT(*) FROM INDEX_ALL;

  COUNT(*)
----------
        14

SQL> SELECT COUNT(*) FROM OBJECT_OTHER;

  COUNT(*)
----------
        50

SQL> DROP TABLE TABLE_ALL;

表已丢弃。

SQL> DROP TABLE INDEX_ALL;

表已丢弃。

SQL> DROP TABLE OBJECT_OTHER;

表已丢弃。


3、INSERT ALL和INSERT FIRST的区别:

 


SQL> CREATE TABLE TABLESPACE_USERS (NAME VARCHAR2(30), TYPE VARCHAR2(30));

表已创建。

SQL> CREATE TABLE TABLE_ALL (TABLE_NAME VARCHAR2(30), TYPE VARCHAR2(30));

表已创建。

SQL> INSERT ALL 
  WHEN (segment_type = 'TABLE') THEN INTO table_all VALUES  (segment_name, segment_type)
  WHEN (tablespace_name = 'USERS') THEN INTO tablespace_users VALUES (segment_name, segment_type)
  SELECT segment_name, segment_type, tablespace_name FROM user_segments;

已创建69行。

SQL> SELECT COUNT(*) FROM TABLE_ALL;

  COUNT(*)
----------
        21

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;

  COUNT(*)
----------
        48

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';

  COUNT(*)
----------
        18

SQL> TRUNCATE TABLE TABLE_ALL;

表已截掉。

SQL> TRUNCATE TABLE TABLESPACE_USERS;

表已截掉。

SQL> INSERT FIRST 
  WHEN (segment_type = 'TABLE') THEN INTO table_all VALUES  (segment_name, segment_type)
  WHEN (tablespace_name = 'USERS') THEN INTO tablespace_users VALUES (segment_name, segment_type)
  SELECT segment_name, segment_type, tablespace_name FROM user_segments;


已创建51行。

SQL> SELECT COUNT(*) FROM TABLE_ALL;

  COUNT(*)
----------
        21

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;

  COUNT(*)
----------
        30

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';

  COUNT(*)
----------
         0

        

最后看一下多表插入语句的限制条件:

1、只能对表执行多表插入语句,不能对视图或物化视图执行;

2、不能对远端表执行多表插入语句;

3、不能使用表集合表达式;

4、不能超过999个目标列;

5、在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时,多表插入语句不能并行执行;

6、多表插入语句不支持执行计划稳定性;

7、多表插入语句中的子查询不能使用序列。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

科技改变未来

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值