create or replace procedure DeleteLog
is
pragma autonomous_transaction;
bus_delete number:=0;
node_delete number:=0;
begin
--循環(huán)開(kāi)始
? while 1=1 loop?
--每次刪除1000tiao
? ? ? DELETE FROM 表1 t where t.RECORD_TIME < TRUNC(sysdate) - 90 and rownum <= 1000;
--如果沒(méi)有刪除數(shù)據(jù)蓝撇，則跳出循環(huán)
? ? IF SQL%NOTFOUND then
? ? ? ? ? ? ? exit;
? ? ? ? ? else
---將已刪除的數(shù)據(jù)賦值到變量
? ? ? ? ? ? ? bus_delete:=bus_delete + SQL%ROWCOUNT;
? ? ? ? ? end if;
--提交
? commit;
--循環(huán)結(jié)束
? end loop;
? commit;
? loop
? ? ? delete from 表2 t where t.RECORD_TIME < TRUNC(sysdate) - 90 and rownum <= 1000;
? ? if SQL%NOTFOUND then
? ? ? ? exit;
? ? ? else
? ? ? ? node_delete:=node_delete + SQL%ROWCOUNT;
? ? end if;
? commit;
? end loop;
? commit;
? DBMS_OUTPUT.put_line('HIP_LOG_BUSINESS:' + bus_delete);
? DBMS_OUTPUT.put_line('HIP_LOG_NODE:' + node_delete);
? EXCEPTION
? WHEN NO_DATA_FOUND
? THEN
? ? ? NULL;
? WHEN OTHERS
? THEN
? ? ? -- Consider logging the error and then re-raise
? ? ? RAISE;
end DeleteLog;
這種每次刪除指定數(shù)量數(shù)據(jù)妓柜，可以有效避免大數(shù)據(jù)量時(shí)，數(shù)據(jù)刪除失敗數(shù)據(jù)回滾揉阎，缺點(diǎn)是占用臨時(shí)表空間過(guò)大。
