Oracle 批量操作脚本解析：动态执行与分批次删除

一、脚本功能概述

本文分享两段 Oracle PL/SQL 脚本，分别实现动态 SQL 执行和大表分批次删除功能，适用于数据清洗、批量操作优化等场景。通过实际案例演示语法逻辑与使用场景。

二、脚本一：动态 SQL 执行与数据清理

1. 核心逻辑

• 从临时表tmp_dsql中读取 SQL 语句（txt字段），并对特殊字符（=和;）进行转义处理
• 逐行执行动态 SQL 语句（execute immediate）
• 执行后删除已处理的记录并提交事务

2. 使用场景

• 批量执行非标准 SQL 语句（如动态生成的 DDL/DML）
• 数据清洗中需要动态处理不同格式的语句
• 避免硬编码 SQL 带来的维护成本

3. 示例演示

（1）建表语句

create table tmp_dsql (
    id number primary key,
    txt varchar2(500)
);

（2）插入测试数据sql

insert into tmp_dsql values (1, 'update emp set salary = salary + 100 where deptno = 10');
insert into tmp_dsql values (2, 'delete from order_log where create_time < sysdate - 30');
commit;

（3）执行脚本

declare 
    v_sql varchar2(500);
begin
    for cur_row1 in (
        select id, 
               replace(replace(txt, '=', '='''), ';', '''') txt  -- 转义处理，避免SQL注入风险
        from tmp_dsql
    ) loop
        v_sql := cur_row1.txt;
        execute immediate v_sql;  -- 动态执行SQL
        commit;
        
        delete from tmp_dsql where id = cur_row1.id;  -- 删除已处理记录
        commit;
    end loop;
end;
/

（4）执行效果

• 逐条执行txt字段中的 SQL 语句
• 执行后tmp_dsql中数据被清空
• 适用于需要动态拼接 SQL 的场景（如从配置表读取操作语句）

三、脚本二：大表分批次删除

1. 核心逻辑

• 设置批次大小v_limit（示例为 2000 条）
• 计算总记录数，按批次循环删除
• 使用rownum分页特性分批删除数据
• 每次删除后提交事务，避免锁表

2. 使用场景

• 删除大表中大量数据（避免全表锁和事务积压）
• 优化delete操作性能，减少对业务的影响
• 处理需要跨事务的批量删除任务

3. 示例演示

（1）建表语句（模拟日志表）

create table sys_log (
    log_id number primary key,
    bzz001 varchar2(100),
    log_time date
);

create table tmp_dsql (
    log_id number
);

（2）插入测试数据（模拟需要删除的日志 ID）

insert into tmp_dsql (log_id) 
select level from dual connect by level <= 10000;  -- 插入10000条待删除ID
commit;

（3）执行脚本

declare 
    v_sql varchar2(500);
    v_count number;
    v_limit number := 2000;  -- 批次大小
begin
    select count(1) into v_count from tmp_dsql;  -- 获取总记录数
    
    for a in 1 .. (v_count / v_limit) loop  -- 计算循环次数
        -- 批次删除关联表数据
        delete from sys_log 
        where bzz001 in (
            select log_id 
            from tmp_dsql 
            where log_id is not null 
            and rownum < v_limit  -- 利用rownum获取前n条
        );
        
        -- 删除临时表已处理数据
        delete from tmp_dsql 
        where rownum < v_limit;
        
        commit;  -- 提交事务，释放锁
    end loop;
end;
/

（4）执行效果

• 分 5 次（10000/2000）删除数据，每次处理 2000 条
• 避免一次性删除大量数据导致的性能问题
• 适用于sys_log等日志表的定期清理

四、注意事项与优化建议

1. SQL 注入风险

   • 脚本一中的replace转义仅为示例，实际生产环境需使用dbms_assert等安全函数处理动态 SQL

2. 事务控制

   • 分批次提交（commit）可减少锁持有时间，但需平衡数据一致性与性能

3. 性能优化

   • 为tmp_dsql.log_id添加索引，提升分批查询效率
   • 避免在循环中使用select count重复计算总数（可预先计算）

4. 异常处理

   • 建议添加exception块捕获执行错误，避免批量操作中断

五、总结

• 脚本一通过动态 SQL 实现灵活操作，适用于配置化数据处理场景
• 脚本二利用分批次删除优化大表操作性能，减少对业务的影响
• 实际使用中需根据数据量、表结构和业务需求调整参数（如批次大小v_limit），并做好测试与监控。