ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】【INSERT】一步到位式分表插入

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

原创 Linux操作系统 作者:逍遥三人 时间:2012-03-16 09:41:02 0 删除 编辑
转载:http://space.itpub.net/519536/viewspace-615545
通过这个实验,给大家展示一下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博客 ” ,链接:http://blog.itpub.net/21584437/viewspace-718716/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 多表INSERT语句
请登录后发表评论 登录
全部评论

注册时间:2009-05-07

  • 博文量
    73
  • 访问量
    110101