批量提交

create or replace procedure clean_alarminfo_histroy
  --批量提交：为节省执行时间和效率增加了删除批量提交
as
pragma autonomous_transaction;
n_delete number:=0;
p_count number:=3000;
begin
  insert into alarminfohistory
    select *
      from alarminfo b
     where b.iscleared=1
       and b.arrivetime <to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
      /* and not exists (select alarmnumber
              from alarminfohistory
             where b.alarmnumber = alarmnumber)*/;
  commit;
while 1=1 loop
EXECUTE IMMEDIATE
'delete from alarminfo where iscleared=1 and arrivetime<to_date(to_char(sysdate,'||'''||yyyy-mm-dd||'''||'),'||'''||yyyy-mm-dd||'''||') and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end clean_alarminfo_histroy;