19.大数据集

.large Data Sets 大数据集
Create Table t2 atablespace users As Select * From dba_objects  --用查询到的结果集创建表t2
Select Count(*) From t2
Insert Into t2 Select * From t2
--使用相关子查询结果进行更新数据
/*
Update PORTS PT
  Set(TOT_SHIPS_ASSIGNED,TOT_SHIPS_ASGN_CAP) =
    (Select Count(S.SHIP_ID) TOTAL_SHIPS,
            Sum(S.CAPACITY)  TOTAL_SHIP_CAPACITY
     From   SHIPS S
     Where  S.HOME_PORT_ID = PT.PORT_ID
     Group By S.HOME_PORT_ID);       
*/
--INSERT ALL多表插入
--不带条件的多表插入
Insert All
Into T_TAB Values(TABLE_NAME,TABLESPACE_NAME)
Into T_IDX Values(TABLE_NAME,TABLESPACE_NAME,INDEX_NAME)
Select * From DBA_INDEXES;        --表中含有1076条数据，分别对2个表插入1076条
--带条件的多表插入
Insert All
WHER(OBJECT_TYPE='TABLE') Then
Into T_TAB Values(Null,Null,OBJECT_NAME,OBJECT_TYPE)
WHER(OBJECT_TYPE='INDEX') Then
Into T_IDX Values(Null,Null,OBJECT_NAME,OBJECT_TYPE)
Else
Into T_OTHERS
Select ObjecT_NAME,OBJECT_TYPE From DBA_OBJECTS;    --根据判断条件进行插入
--统计下DBA_OBJECT表中的各类数据
Select object_type,Count(*) From
       (
        Select decode(object_type,'TABLE','TABLE','INDEX','INDEX','OTHERS') OBJECT_TYPE
        From DBA_OBJECTS
        )
        Group By Rollup(OBJECT_TYPE)
        ;
--insert first
Insert First
Where(OBJECT_NAME In ('T_TAB','T_IDX','T_OTHERS')) Then
Into T_TAB Values(OBJECT_NAME,Null,OBJECT_TYPE)
Where (OBJECT_NAME In ('T_TAB','T_IDX','T_OTHERS'))Then
Into T_IDX Values(Null,Null,OBJECT_NAME,OBJECT_TYPE)
Else
Into T_OTHERS
Select OBJECT_NAME,OBJECT_TYPE From DBA_OBJECTS;    --按顺序匹配条件，满足条件的插入第一个表T_TAB，虽然也满足第二个条件，但不再进行插入，不满足条件的插入T_OTHERS




        

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15810196/viewspace-1079770/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15810196/viewspace-1079770/