INSERT ALL和INSERT FIRST语法

转自：http://yangtingkun.itpub.net/post/468/27038

 

INSERT ALL和INSERT FIRST语法

===========================================================

作者: yangtingkun(http://yangtingkun.itpub.net)
发表于: 2005.04.19 23:57
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/27038
---------------------------------------------------------------

在数据仓库中的转换和装载过程中，可能会使用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索引时，多表插入语句不能并行执行；

 

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

 

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

 

 

INSERT ALL和INSERT FIRST语句的差别：

其实你对比INSERT ALL和INSERT FIRST语句的例子仔细观察，就会明白这个例子的含义，两个SQL语句唯一的差别就是ALL和FIRST的差别，其余部分完成一样。
对于INSERT ALL，插入第一张表的数据，如果满足第二张表的条件，也会插入。
而INSERT FIRST不然，满足第一条插入条件的数据是不会进行随后的判断的，所以在INSERT FIRST执行后，在TABLESPACE_USERS表中看不到SEGMENT_TYPE为TABLE的记录