通过这个实验，给大家展示一下INSERT语句的强大之处：按条件同时插入多张表。
利用这个功能可以实现很多具体的需求。大家慢慢体会。

实验目的：使用一条SQL语句完成用户下数据库对象的分类存放。

1.创建测试表
sec@ora10g> create table t_1 as select OBJECT_NAME,OBJECT_TYPE from user_objects where 0 = 1;

Table created.

sec@ora10g> create table t_2 as select OBJECT_NAME,OBJECT_TYPE from user_objects where 0 = 1;

Table created.

sec@ora10g> create table t_3 as select OBJECT_NAME,OBJECT_TYPE from user_objects where 0 = 1;

Table created.

2.确认实验中会用到的数据表信息
sec@ora10g> select count(*) from user_objects;

COUNT(*)
----------
       317

sec@ora10g> select count(*) from t_1;

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

sec@ora10g> select count(*) from t_2;

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

sec@ora10g> select count(*) from t_3;

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

3.真实的核心技术在此
sec@ora10g> INSERT   ALL
2     WHEN (object_type IN ('TABLE'))
3     THEN
4             INTO   t_1 (OBJECT_NAME, OBJECT_TYPE)
5           VALUES       (OBJECT_NAME, OBJECT_TYPE)
6     WHEN (object_type IN ('SEQUENCE'))
7     THEN
8             INTO   t_2 (OBJECT_NAME, OBJECT_TYPE)
9           VALUES       (OBJECT_NAME, OBJECT_TYPE)
10     ELSE
11             INTO   t_3 (OBJECT_NAME, OBJECT_TYPE)
12           VALUES       (OBJECT_NAME, OBJECT_TYPE)
13     SELECT   OBJECT_NAME, OBJECT_TYPE FROM user_objects
14 /

317 rows created.

4.最后，我们来验证一下实验的效果：用户下的数据库对象分类存放
sec@ora10g> select count(*) from t_1;

COUNT(*)
----------
       110

sec@ora10g> select count(*) from t_2;

COUNT(*)
----------
       100

sec@ora10g> select count(*) from t_3;

COUNT(*)
----------
       107

sec@ora10g> col object_name for a30
sec@ora10g> col object_type for a30
sec@ora10g> select * from t_1 where rownum<11;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
T_MSS_TST_LOG                  TABLE
T_MSS_TSTINFO                  TABLE
T_MSS_TSTM_RRL_CONTACT         TABLE
T_MSS_SEND_CONTACT_LOG         TABLE
T_MSS_ATTACH_FILE              TABLE
T_MSS_CHANGE_REASON            TABLE
T_MSS_CODE                     TABLE
T_MSS_DOCUMENT                 TABLE
T_MSS_FILE_TST                 TABLE
T_MSS_IMPORT_ERROR_LOG         TABLE

10 rows selected.

sec@ora10g> select * from t_2 where rownum<11;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
S_MSS_TST_LOG                  SEQUENCE
S_MSS_TSTM_SEND_CONTACT        SEQUENCE
S_MSS_SEND_CONTACT_LOG         SEQUENCE
S_MSS_ATTACH_FILE              SEQUENCE
S_MSS_CHANGE_REASON            SEQUENCE
S_MSS_CODE                     SEQUENCE
S_MSS_DOCUMENT                 SEQUENCE
S_MSS_DPC_RESULT               SEQUENCE
S_MSS_FILE_TST                 SEQUENCE
S_MSS_IMPORT_ERROR_LOG         SEQUENCE

10 rows selected.

sec@ora10g> select * from t_3 where rownum<11;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
PK_MSS_SST_LOG                 INDEX
PK_MSS_TSTINFO                 INDEX
PK_MSS_TSTM_RRL_CONTACT        INDEX
PK_SEND_CONTACT_LOG            INDEX
PK_MSS_FILE                    INDEX
PK_MSS_CHANGE_REASON           INDEX
PK_MSS_CODE                    INDEX
PK_MSS_DOCUMENT                INDEX
PK_MSS_FILE_TST                INDEX
PK_MSS_INPUT_ERROR_LOG         INDEX

10 rows selected.

5.小结
Oracle有很多的特色的东西值得大家去深入探索，也许某一天，仅仅这一个小小的技巧就会解决实际中的大麻烦。

-- The End --

ITPub博客

【实验】【INSERT】一步到位式分表插入

最新文章