INSERT ALL和INSERT FIRST语法

导读：

　　在数据仓库中的转换和装载过程中，可能会使用INSERT ALL语句，这篇文章简单介绍一下INSERT ALL语句。

　　

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

　　下面看几个简单的例子：

　　SQL> CREATE TABLE TABLE_STORAGE

　　2 (

　　3 TABLE_NAME VARCHAR2(30),

　　4 TABLESPACE_NAME VARCHAR2(30),

　　5 PCT_FREE NUMBER,

　　6 PCT_USED NUMBER,

　　7 INI_TRANS NUMBER,

　　8 MAX_TRANS NUMBER,

　　9 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

　　2 (

　　3 TABLE_NAME VARCHAR2(30),

　　4 NUM_ROWS NUMBER,

　　5 BLOCKS NUMBER,

　　6 EMPTY_BLOCKS NUMBER,

　　7 AVG_SPACE NUMBER,

　　8 CHAIN_CNT NUMBER,

　　9 AVG_ROW_LEN NUMBER

　　10 );

　　表已创建。

　　SQL> INSERT ALL

　　2 INTO TABLE_STORAGE VALUES (TABLE_NAME, TABLESPACE_NAME, PCT_FREE, PCT_USED,

　　3 INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,

　　4 PCT_INCREASE, FREELISTS, FREELIST_GROUPS)

　　5 INTO TABLE_STAT VALUES (TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,

　　6 CHAIN_CNT, AVG_ROW_LEN)

　　7 SELECT * FROM USER_TABLES;

　　已创建54行。

　　SQL> SELECT COUNT(*) FROM TABLE_STORAGE;

　　COUNT(*)

　　----------

　　27

　　SQL> SELECT COUNT(*) FROM TABLE_STAT;

　　COUNT(*)

　　----------

　　27

　　SQL> SELECT COUNT(*) FROM USER_TABLES;

　　COUNT(*)

　　----------

　　27

　　SQL> DROP TABLE TABLE_STAT;

　　表已丢弃。

　　SQL> DROP TABLE TABLE_STORAGE;

　　表已丢弃。

　　上面是最简单的INSERT ALL语句的实现，下面看看带条件的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

　　2 WHEN (OBJECT_TYPE = 'TABLE') THEN

　　3 INTO TABLE_ALL VALUES (OBJECT_NAME)

　　4 WHEN (OBJECT_TYPE = 'INDEX') THEN

　　5 INTO INDEX_ALL VALUES (OBJECT_NAME)

　　6 ELSE

　　7 INTO OBJECT_OTHER

　　8 SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS

　　9 ;

　　已创建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

　　2 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;

　　表已丢弃。

　　下面看一下INSERT ALL和INSERT FIRST的区别：

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

　　表已创建。

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

　　表已创建。

　　SQL> INSERT ALL

　　2 WHEN (SEGMENT_TYPE = 'TABLE') THEN

　　3 INTO TABLE_ALL VALUES (SEGMENT_NAME)

　　4 WHEN (TABLESPACE_NAME = 'USERS') THEN

　　5 INTO TABLESPACE_USERS VALUES (SEGMENT_NAME, SEGMENT_TYPE)

　　6 SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME

　　7 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

　　2 WHEN (SEGMENT_TYPE = 'TABLE') THEN

　　3 INTO TABLE_ALL VALUES (SEGMENT_NAME)

　　4 WHEN (TABLESPACE_NAME = 'USERS') THEN

　　5 INTO TABLESPACE_USERS VALUES (SEGMENT_NAME, SEGMENT_TYPE)

　　6 SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME

　　7 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

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

　　只能对表执行多表插入语句，不能对视图或物化视图执行；

　　不能对远端表执行多表插入语句；

　　不能使用表集合表达式；

　　不能超过999个目标列；

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

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

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

　　



本文转自

http://yangtingkun.itpub.net/post/468/27038