如何用Oracle存储过程按90天批次批量删除数据

原存储过程存在逐行处理、日期区间逻辑错误、循环结构混乱等问题，以下是符合需求的修改版本，核心是按90天日期区间批量删除，而非逐行操作：

修改后的存储过程代码 #

CREATE OR REPLACE PROCEDURE pr_batch_delete_90d
AS
    v_min_date DATE;
    v_max_date DATE;
    v_start_date DATE;
    v_end_date DATE;
    v_delete_count NUMBER;
BEGIN
    -- 获取2022年数据的最小和最大注册日期
    SELECT MIN(regDate), MAX(regDate)
    INTO v_min_date, v_max_date
    FROM tbl1
    WHERE regDate BETWEEN DATE '2022-01-01' AND DATE '2022-12-01';

    -- 初始化批次起始日期
    v_start_date := v_min_date;

    -- 循环处理每个90天批次
    WHILE v_start_date <= v_max_date LOOP
        -- 计算当前批次结束日期（最多90天，不超年度最大日期）
        v_end_date := LEAST(v_start_date + INTERVAL '90' DAY, v_max_date);
        
        -- 批量删除tbl2中对应批次的数据
        DELETE FROM tbl2
        WHERE nameid IN (SELECT nameid FROM tbl1 WHERE regDate BETWEEN v_start_date AND v_end_date)
           OR id IN (SELECT nameid FROM tbl1 WHERE regDate BETWEEN v_start_date AND v_end_date);
        v_delete_count := SQL%ROWCOUNT;

        -- 批量删除tbl1中对应批次的数据
        DELETE FROM tbl1
        WHERE regDate BETWEEN v_start_date AND v_end_date;
        v_delete_count := v_delete_count + SQL%ROWCOUNT;

        -- 提交当前批次操作
        COMMIT;

        -- 记录批次删除日志
        INSERT INTO log(log_t)
        VALUES ('批次[' || TO_CHAR(v_start_date, 'YYYY-MM-DD') || '至' || TO_CHAR(v_end_date, 'YYYY-MM-DD') || '] 共删除' || v_delete_count || '条数据');

        -- 推进到下一批次起始日期
        v_start_date := v_end_date + INTERVAL '1' DAY;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        -- 异常回滚并记录错误
        ROLLBACK;
        INSERT INTO log(log_t)
        VALUES ('删除失败：' || SQLERRM || '，批次起始日期' || TO_CHAR(v_start_date, 'YYYY-MM-DD'));
        RAISE;
END pr_batch_delete_90d;
/

关键修改说明 #

• 批量区间删除：不再逐行处理，直接通过日期范围锁定整个批次数据，一次性完成删除操作，大幅提升执行效率
• 动态批次计算：用LEAST函数确保最后一个批次不会超出年度数据的最大日期，避免逻辑漏洞
• 清晰的关联删除逻辑：先处理关联表tbl2的删除，再删除主表tbl1的数据，保证数据一致性
• 异常处理机制：捕获异常时自动回滚当前批次，并记录错误信息，避免部分提交导致的数据混乱
• 规范日期格式：使用DATE 'YYYY-MM-DD'替代字符串日期，避免因会话日期格式差异引发的错误

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