demo04.sql

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

create table t1 as select * from all_objects;
exec dbms_stats.gather_table_stats( user, 'T1' );
create table t2 as select * from t1;
exec dbms_stats.gather_table_stats( user, 'T2' );
set timing on
update t1 set object_name = lower(object_name);
begin
   for x in ( select rowid rid, object_name, rownum r
                from t2 )
   loop
        update t2
           set object_name = lower(x.object_name)
         where rowid = x.rid;
        if ( mod(x.r,100) = 0 ) then
           commit;
        end if;
   end loop;
   commit;
end;
/
declare
    type ridArray is table of rowid;
    type vcArray is table of t2.object_name%type;

    l_rids  ridArray;
    l_names vcArray;

    cursor c is select rowid, object_name from t2;
begin
    open c;
    loop
        fetch c bulk collect into l_rids, l_names LIMIT 100;
        forall i in 1 .. l_rids.count
            update t2
               set object_name = lower(l_names(i))
             where rowid = l_rids(i);
        commit;
        exit when c%notfound;
    end loop;
    close c;
end;
/