Oracle存储过程分批删除大表数据时如何将进度写入带时间戳的状态表

前置准备 #

先创建固定结构的进度日志表，仅需执行一次：

CREATE TABLE delete_progress_log (
    remain_delete_cnt NUMBER,  -- 剩余待删记录数字段
    operate_ts TIMESTAMP DEFAULT SYSTIMESTAMP  -- 操作时间戳字段，默认取当前系统时间
);

实现逻辑说明 #

1. 首先要修正原存储过程里几处会导致运行报错/逻辑异常的问题，不然即使加了日志也没法正常跑：
   • 原删除SQL拼接时重复写了where关键字，直接执行会触发语法报错
   • 原退出条件EXIT WHEN nCount = 0存在逻辑漏洞：当最后一批剩余待删数小于单批删除量nrows时，计算出的剩余数会出现负数，永远触发不了等于0的退出条件，导致死循环
   • 原逻辑直接用nCount减去固定nrows计数不严谨，最后一批实际删除的行数往往小于nrows，会导致剩余数统计不准
2. 日志写入的节点选在两个位置：
   • 任务启动时，先统计出全部待删的总记录数，写入第一条初始进度日志
   • 每完成一轮单批删除，计算出当前真实剩余待删量，立即写入一条进度日志，提交事务

修正后带日志写入的完整存储过程 #

CREATE OR REPLACE PROCEDURE delete_tab (tablename IN VARCHAR2, nrows IN NUMBER ) IS
    sSQL1 VARCHAR2(2000);
    sSQL2 VARCHAR2(2000);
    nCount NUMBER;
BEGIN
    DBMS_OUTPUT.enable (100000);

    -- 统计全量待删除的初始总记录数
    sSQL2 := 'select count(ROWID) from ' || tablename ||
             ' where cast(time_stamp as date) < sysdate - 467';
    EXECUTE IMMEDIATE sSQL2 INTO nCount;

    -- 修正原删除SQL的语法错误
    sSQL1 := 'delete from '|| tablename ||
             ' where ROWNUM <= ' || nrows || ' and cast(time_stamp as date) < sysdate - 467';

    -- 写入任务启动的初始进度日志
    EXECUTE IMMEDIATE 'INSERT INTO delete_progress_log (remain_delete_cnt, operate_ts) VALUES (:1, SYSTIMESTAMP)' 
    USING nCount;
    COMMIT;

    LOOP
        -- 执行单批删除
        EXECUTE IMMEDIATE sSQL1;
        -- 取本次实际删除的行数，计算真实剩余待删量，避免固定减nrows的计数偏差
        nCount := nCount - SQL%ROWCOUNT;

        DBMS_OUTPUT.PUT_LINE('Existing records to be deleted: ' || to_char(nCount));

        -- 核心逻辑：单批删除完成后，写入当前进度日志
        EXECUTE IMMEDIATE 'INSERT INTO delete_progress_log (remain_delete_cnt, operate_ts) VALUES (:1, SYSTIMESTAMP)' 
        USING nCount;
        
        COMMIT;
        -- 修正退出条件：剩余待删数小于等于0时直接退出循环
        EXIT WHEN nCount <= 0;
    END LOOP;
END delete_tab;
/

注意事项 #

• 日志表名可以根据实际规范调整，只要对应修改INSERT语句里的表名即可
• 用绑定变量传剩余计数的方式写INSERT，避免SQL注入风险，也减少硬解析开销
• 每轮日志写入和删除操作一起提交，不会因为日志写入单独占事务导致锁表

内容的提问来源于stack exchange，提问作者jgdev