--方法1:
declare
i int;--定义变量
v_count int;--定义变量
v_loop int;--定义变量
begin
select count(*) into v_count from test;--计算表内数据总数
select ceil(v_count/10) into v_loop from dual;--计算需要循环次数
i:=1;--为i赋值
while i<=v_loop loop--循环退出条件
update test set begintime=sysdate where begintime is null and rownum<=10;--执行更新
commit;--提交
i:=i+1;--i依次加1
end loop;--结束循环
end;
--方法2:
declare
v_count number := 0;
begin
for i in (select rowid
from MMS_SYSTEMUSER u
where u.systemname not in ('abc')) loop
update MMS_SYSTEMUSER t
set t.password =
('8888888888888888888888' || t.systemid)
where t.rowid = i.rowid; --根据rowid逐条更新记录
v_count := v_count + 1; --计数器
if mod(v_count, 1000) = 0 then
--每更新1000条提交一次
commit;
end if;
end loop;
commit;
end;
/
--方法3:
declare
cursor mycursor is select rowid from tmp_wrh_test t where 1=1 and t.id not in (1,10,100) order by rowid;type rowid_table_type is table of urowid index by pls_integer;
v_rowid rowid_table_type;
begin
open mycursor;
loop
fetch mycursor bulk collect into v_rowid limit 1000; --每次处理1000行,也就是每1000行一提交
exit when v_rowid.count = 0;
forall i in v_rowid.first..v_rowid.last
update tmp_wrh_test t set t.name2 = t.name1 where rowid = v_rowid(i);
commit;
end loop;
close mycursor;
end;
/