【转载】多表插入INSERT ALL…

来源：互联网发布：三维模型查看软件编辑：程序博客网时间：2024/06/05 19:18

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

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

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

SQL> CREATE TABLE TABLE_STORAGE
2 (
3 TABLE_NAMEVARCHAR2(30),
4 TABLESPACE_NAMEVARCHAR2(30),
5 PCT_FREE NUMBER,
6 PCT_USED NUMBER,
7 INI_TRANS NUMBER,
8 MAX_TRANS NUMBER,
9 INITIAL_EXTENTNUMBER,
10 NEXT_EXTENTNUMBER,
11 MIN_EXTENTSNUMBER,
12 MAX_EXTENTSNUMBER,
13 PCT_INCREASENUMBER,
14 FREELISTS NUMBER,
15 FREELIST_GROUPSNUMBER
16 );

表已创建。

SQL> CREATE TABLE TABLE_STAT
2 (
3 TABLE_NAMEVARCHAR2(30),
4 NUM_ROWS NUMBER,
5 BLOCKS NUMBER,
6 EMPTY_BLOCKSNUMBER,
7 AVG_SPACE NUMBER,
8 CHAIN_CNT NUMBER,
9 AVG_ROW_LEN NUMBER
10 );

表已创建。

SQL> INSERTALL
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,PCT_INCREASE,FREELISTS, FREELIST_GROUPS)
4 INTO TABLE_STAT VALUES (TABLE_NAME,NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN)
5 SELECT * FROMUSER_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> INSERTALL
2  WHEN (object_type= 'TABLE') THENINTO table_all VALUES (object_name)
3  WHEN (object_type= 'INDEX') THENINTO index_all VALUES (object_name)
4  ELSEINTO object_other (OBJECT_NAME,OBJECT_TYPE)
5 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
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;

表已丢弃。

3、INSERT ALL和INSERT FIRST的区别：

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

表已创建。

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

表已创建。

SQL> INSERTALL
2 WHEN (segment_type= 'TABLE') THENINTO table_all VALUES (segment_name, segment_type)
3 WHEN (tablespace_name= 'USERS') THENINTO tablespace_users VALUES (segment_name,segment_type)
4 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> INSERTFIRST
2 WHEN (segment_type= 'TABLE') THENINTO table_all VALUES (segment_name, segment_type)
3 WHEN (tablespace_name= 'USERS') THENINTO tablespace_users VALUES (segment_name,segment_type)
4 SELECT segment_name,segment_type, tablespace_name FROM user_segments;