最近项目上线了,数据量也比较大,表中数据基本上都在千万,有时要清理些数据.由于数据量较大,每次在清理时都比较耗费时间,特别是在几千万的表中直接delete数据,有时几个小时都删除不了,大大的耗费时间,工作效率不高.每每想起以前处理的方法,只是很久没有用比较生疏,有时就是记不起,因此趁现在记得,保留下方便以后用时查看:
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;