----批量逐條資料處理模版-----
create or replace procedure proc_test is
batch_size constant pls_integer := 1000;--批量處理數量
--定義結果集數組
type data_result is record(
tn_column1 tablename.column1%type,
tn_column2 tablename.column4%%type,
tn_column3 varchar2(4000));
type data_result is varray(1000) of data_result;--數組大小
results data_result;
--定義遊标
cursor cur_curname is
select column1, column2, column3, column14
from (select t.column1,
t.column2,
t.column3,
wm_concat(t.column4) column4 --列轉行
from tablename t
group by t.column1, t.column2, t.column3)
where length(column4) > 2;
begin
open cur_curname; --打開遊标
loop
fetch cur_curname bulk collect
into results limit batch_size;
for i in 1 .. results.count loop --逐條處理
--業務邏輯處理
dbms_output.put_line(results.count);
end loop;
exit when cur_curname%notfound;
end loop;
--關閉遊标
if cur_curname%isopen then
close cur_curname;
end if;
commit;
exception
when others then
dbms_output.put_line('error : ' || sqlcode || '--' || sqlerrm);
rollback;
end;