oracle 表删除大量数据,oracle存储过程删除大量表数据

create or replace procedure DeleteLog

is

pragma autonomous_transaction;

bus_delete number:=0;

node_delete number:=0;

begin

--循环开始

while 1=1 loop

--每次删除1000tiao

DELETE FROM 表1 t where t.RECORD_TIME < TRUNC(sysdate) - 90 and rownum <= 1000;

--如果没有删除数据，则跳出循环

IF SQL%NOTFOUND then

exit;

else

---将已删除的数据赋值到变量

bus_delete:=bus_delete + SQL%ROWCOUNT;

end if;

--提交

commit;

--循环结束

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;