Oracle带限制的批量收集和For All未正确处理所有记录

Question

我需要通过存储过程处理Oracle表的近60k条记录。处理过程是，对于每一行，我需要删除和更新第二个表中的一行，并在第三个表中插入一行。

使用光标循环，这个过程大约需要6-8个小时才能完成。如果我切换到带限制的批量收集，执行时间会减少，但处理不正确。以下是程序的批量收集版本

create or replace procedure myproc()
is
cursor c1 is select col1,col2,col3 from tab1 where col4=3;
type t1 is table of c1%rowtype;
v_t1 t1;
begin
open c1;
loop
fetch c1 bulk collect into v_t1 limit 1000;
exit when v_t1.count=0;
forall i in 1..v_t1.count
delete from tab2 where tab2.col1=v_t1(i).col1;
commit;
forall i in 1..v_t1.count
update tab2 set tab2.col1=v_t1(i).col1 where tab2.col2=v_t1(i).col2;
commit;
forall i in 1..v_t1.count
insert into tab3 values(v_t1(i).col1,v_t1(i).col2,v_t1(i).col3);
commit;
end loop;
close c2;
end;

对于其中约20k条记录，第一次删除操作得到了正确处理，但随后的更新和插入操作没有得到处理。对于剩余的40k条记录，所有三项操作都得到了正确处理。

我是不是错过了什么？此外，我可以在Bulk Collect中使用的最大LIMIT值是多少？

Answer 1

您应该尝试使用FORALL的SAVE EXCEPTIONS子句，类似于(未测试)：

create or replace procedure myproc
as
cursor c1 is select col1,col2,col3 from tab1 where col4=3;
type t1 is table of c1%rowtype;
v_t1 t1;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
l_errors number;
l_errno    number;
l_msg    varchar2(4000);
l_idx    number;
begin
open c1;
loop
fetch c1 bulk collect into v_t1 limit 1000;
-- process v_t1 data
BEGIN
forall i in 1..v_t1.count SAVE EXCEPTIONS
delete from tab2 where tab2.col1=v_t1(i).col1;
commit;
EXCEPTION
when DML_ERRORS then
l_errors := sql%bulk_exceptions.count;
for i in 1 .. l_errors
loop
l_errno := sql%bulk_exceptions(i).error_code;
l_msg   := sqlerrm(-l_errno);
l_idx   := sql%bulk_exceptions(i).error_index;
-- log these to a table maybe, or just output 
end loop;
END;
exit when c1%notfound;
end loop;
close c2;
end;