Oracle存储过程中删除特定行前如何备份待删数据？

1. 创建备份表 #

首先需要创建与Car表结构匹配的备份表，建议新增备份时间、操作人字段用于追溯操作信息：

CREATE TABLE Car_Backup AS
SELECT c.*,
       SYSDATE AS backup_time,
       USER AS backup_user
FROM Car c
WHERE 1=0; -- 仅复制表结构，不导入原有数据

2. 编写存储过程 #

以下存储过程接收逗号分隔的Id字符串作为参数，先将待删除的Car表行插入备份表，再执行删除操作，通过事务控制保证备份与删除的原子性：

CREATE OR REPLACE PROCEDURE Delete_Car_With_Backup(p_id_list IN VARCHAR2)
IS
    v_error_msg VARCHAR2(2000);
BEGIN
    SAVEPOINT sp_before_operations;

    -- 备份待删除行到备份表
    INSERT INTO Car_Backup
    SELECT c.*, SYSDATE, USER
    FROM Car c
    WHERE c.Vin IN (
        SELECT cd.Vin
        FROM CarDetails cd
        WHERE cd.Id IN (
            -- 拆分传入的逗号分隔Id字符串
            SELECT REGEXP_SUBSTR(p_id_list, '[^,]+', 1, LEVEL)
            FROM dual
            CONNECT BY REGEXP_SUBSTR(p_id_list, '[^,]+', 1, LEVEL) IS NOT NULL
        )
    );

    -- 执行删除操作
    DELETE Car
    WHERE Vin IN (
        SELECT cd.Vin
        FROM CarDetails cd
        WHERE cd.Id IN (
            SELECT REGEXP_SUBSTR(p_id_list, '[^,]+', 1, LEVEL)
            FROM dual
            CONNECT BY REGEXP_SUBSTR(p_id_list, '[^,]+', 1, LEVEL) IS NOT NULL
        )
    );

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('操作完成：已备份并删除 ' || SQL%ROWCOUNT || ' 行');

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO sp_before_operations;
        v_error_msg := '操作失败：' || SQLERRM;
        DBMS_OUTPUT.PUT_LINE(v_error_msg);
        RAISE;
END Delete_Car_With_Backup;
/

3. 调用存储过程 #

传入逗号分隔的Id列表即可执行操作：

-- 示例：删除Id为JH4KA826000000000、JH4KA826000000001的对应车辆数据
EXEC Delete_Car_With_Backup('JH4KA826000000000,JH4KA826000000001');

4. 恢复备份数据 #

若后续需要恢复，可直接从备份表将数据插回原表：

-- 示例：恢复指定日期的备份数据
INSERT INTO Car
SELECT * EXCEPT (backup_time, backup_user) -- 排除备份表新增的字段
FROM Car_Backup
WHERE backup_time BETWEEN TO_DATE('2024-05-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                      AND TO_DATE('2024-05-20 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

注意事项 #

• 若CarDetails.Id为数值类型，需将拆分后的字符串转为对应数值，例如TO_NUMBER(REGEXP_SUBSTR(p_id_list, '[^,]+', 1, LEVEL))
• 可根据需求扩展备份表字段，如添加操作备注等
• 事务控制确保了备份与删除操作的一致性，避免出现数据不一致的情况

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