create or replace procedure p_del_bigtab
as
v_count number := 0; --记录行数
v_max number := 100000; --最大赋值
v_id dbms_sql.Varchar2_Table; --数组
v_no dbms_sql.Varchar2_Table;
v_pid dbms_sql.Varchar2_Table;
cursor cur_acc_customer --游标
is
select v_acc,v_customer_no,v_product_id
from tb_acc_customer t
where exists (select 1
from tb_cert t1
where t1.v_acc = t.v_acc
and t1.v_customer_no = t.v_customer_no
and t1.v_product_id = t.v_product_id
and t1.n_end_date is not null);
begin
open cur_acc_customer; --打开游标
loop --循环取游标数,将其赋值给数组
fetch cur_acc_customer bulk collect into v_id,v_no,v_pid limit v_max;
forall i in 1 .. v_id.count --使用forall删除数据
delete from tb_acc_customer
where v_acc = v_id(i)
and v_customer_no = v_no(i)
and v_product_id = v_pid(i);
v_count := sql%rowcount + v_count; --记录行数
commit;
exit when cur_acc_customer%notfound; --取完游标后退出
end loop;
dbms_output.put_line(v_count);
commit;
close cur_acc_customer; --关闭游标
end;