1. 建一个type,同时建一个游标
type temp_type is record(
temp_rowid varchar2(128),
temp_count integer);
type imw_type is table of temp_type;
imw_types imw_type;
cursor temp_cursor is
select a.rowid, count(b.userid)
from im_ecusermsginfo_bak b, im_ecuserstatus a
where b.userid = a.userid
and b.imstatus <> '2'
group by a.rowid;
2.将游标打开,每1000条存进一次type中。再从type中获取数据,执行其他操作。
open temp_cursor;
loop
fetch temp_cursor bulk collect
into imw_types limit 1000;
exit when imw_types.count = 0;
forall i in 1 .. imw_types.count
update im_ecuserstatus a
set a.expmsgcnt = imw_types(i).temp_count
where a.rowid = imw_types(i).temp_rowid;
commit;
end loop;
close temp_cursor;
3.exception要关游标
exceptionwhen others then
if temp_cursor%isopen then
close temp_cursor;
end if;