oracle数据库update循环执行语句

--由于数据量比较大每次执行一个小时时报回滚段已满

--这里有两种方法来分批处理

1、declare
  maxrows number default 5000;
  p_id_table dbms_sql.Varchar2_Table;
  cursor acnt_first_cur is
    select t2.cert_number
          from tb_pnt_cust_551 t,party_centification t2
         where t.pty_id = t2.party_id
           and t2.cert_type = 1
           and t2.latn_id = 551
           and rownum < 2;
begin
  open acnt_first_cur;
  loop
    exit when acnt_first_cur%notfound;
    fetch acnt_first_cur bulk collect
      into p_id_table limit maxrows;
    forall i in 1 .. p_id_table.count
      update tb_pnt_cust_551 t3
         set t3.id_card = p_id_table(i);
    commit;
  end loop;
end;
/

 --------------------------------------------------------------------------------------------------------------

2、begin
for i in 1..1000 loop
update tb_pnt_cust_551 t
   set t.id_card =  (select cert_number from
       (select t2.party_id,max(t2.cert_number) cert_number
          from party_centification t2
         where t2.cert_type = 1
           and t2.latn_id = 551
         group by t2.party_id)
         where t.pty_id = t2.party_id ) t1
  where rownum < 1000
    and t.id_card is null；end loop;
end;
/