oracle 循环修改数据库,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、beginfor 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;/