demo04.sql

Expert Oracle Database Architecture 9i and 10g sql源码

create table t tablespace assm
as
select 0 id, a.*
  from all_objects a
 where 1=0;
                                      
alter table t
add constraint t_pk
primary key (id)
using index (create index t_pk on t(id) &indexType tablespace assm);

create sequence s cache 1000;



create or replace procedure do_sql
as
begin
    for x in ( select rownum r, all_objects.* from all_objects )
    loop
        insert into t
        ( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
          OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
          LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
          GENERATED, SECONDARY )
        values
        ( s.nextval, x.OWNER, x.OBJECT_NAME, x.SUBOBJECT_NAME,
          x.OBJECT_ID, x.DATA_OBJECT_ID, x.OBJECT_TYPE, x.CREATED,
          x.LAST_DDL_TIME, x.TIMESTAMP, x.STATUS, x.TEMPORARY,
          x.GENERATED, x.SECONDARY );
        if ( mod(x.r,100) = 0 )
        then
            commit;
        end if;
    end loop;
    commit;
end;
/
    



	exec sql declare c cursor for select * from all_objects;
    exec sql open c;
    exec sql whenever notfound do break;
    for(;;)
    {
        exec sql
        fetch c into :owner:owner_i,
        :object_name:object_name_i, :subobject_name:subobject_name_i,
        :object_id:object_id_i, :data_object_id:data_object_id_i,
        :object_type:object_type_i, :created:created_i,
        :last_ddl_time:last_ddl_time_i, :timestamp:timestamp_i,
        :status:status_i, :temporary:temporary_i,
        :generated:generated_i, :secondary:secondary_i;

        exec sql
        insert into t
        ( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
          OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
          LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
          GENERATED, SECONDARY )
        values
        ( s.nextval, :owner:owner_i, :object_name:object_name_i,
          :subobject_name:subobject_name_i, :object_id:object_id_i,
          :data_object_id:data_object_id_i, :object_type:object_type_i,
          :created:created_i, :last_ddl_time:last_ddl_time_i,
          :timestamp:timestamp_i, :status:status_i,
          :temporary:temporary_i, :generated:generated_i,
          :secondary:secondary_i );
        if ( (++cnt%100) == 0 )
        {
            exec sql commit;
        }
    }
    exec sql whenever notfound continue;
    exec sql commit;
    exec sql close c;