在数据仓库中的转换和装载过程中,可能会使用INSERT ALL语句,这篇文章简单介绍一下INSERT ALL语句。但在ETL工具中很多组件也有类似的功能,实现逻辑雷同。
虽然INSERT ALL是9i新增的语法,它扩充了原有的INSERT语句,使得INSERT语句从原来的只能插入到一张表发展到可以同时插入多张表,还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。
1、不带条件的INSERT ALL用法:
SQL> CREATE TABLE TABLE_STORAGE
表已创建。
SQL> CREATE TABLE TABLE_STAT
表已创建。
SQL>
已创建54行。
SQL> SELECT COUNT(*) FROM TABLE_STORAGE;
----------
SQL> SELECT COUNT(*) FROM TABLE_STAT;
----------
SQL> SELECT COUNT(*) FROM USER_TABLES;
----------
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>
已创建91行。
SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
----------
SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX';
----------
SQL> SELECT COUNT(*) FROM USER_OBJECTS
----------
SQL> SELECT COUNT(*) FROM TABLE_ALL;
----------
SQL> SELECT COUNT(*) FROM INDEX_ALL;
----------
SQL> SELECT COUNT(*) FROM OBJECT_OTHER;
----------
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>
已创建69行。
SQL> SELECT COUNT(*) FROM TABLE_ALL;
----------
SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;
----------
SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';
----------
SQL> TRUNCATE TABLE TABLE_ALL;
表已截掉。
SQL> TRUNCATE TABLE TABLESPACE_USERS;
表已截掉。
SQL>
已创建51行。
SQL> SELECT COUNT(*) FROM TABLE_ALL;
----------
SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;
----------
SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';
----------
最后看一下多表插入语句的限制条件:
1、只能对表执行多表插入语句,不能对视图或物化视图执行;
2、不能对远端表执行多表插入语句;
3、不能使用表集合表达式;
4、不能超过999个目标列;
5、在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时,多表插入语句不能并行执行;
6、多表插入语句不支持执行计划稳定性;
7、多表插入语句中的子查询不能使用序列。