批量游标处理大数据

--批量游标处理大数据
declare
  type t_rid is table of rowid index by binary_integer;
  l_t_rid t_rid;


  cursor cur_rid is(
    select rowid from bigtable where owner = 'SCOTT');


  ln_rowcnt number := 0;
begin
  open cur_rid;
  loop
    fetch cur_rid bulk collect
      into l_t_rid limit 5000;
  
    ln_rowcnt := ln_rowcnt + l_t_rid.count;
  
    forall i in 1 .. l_t_rid.count
      update bigtable
         set object_name = 'HL' || object_name
       where rowid = l_t_rid(i);
    commit;
    exit when cur_rid%notfound or cur_rid%notfound is null;
  end loop;
  close cur_rid;


  dbms_output.put_line(ln_rowcnt);


exception
  when others then
    if cur_rid%isopen then
      close cur_rid;
    end if;
end;