如何用LOOP实现PL/SQL存储过程分批处理3000条数据？

以下是满足需求的完整PL/SQL存储过程代码，核心通过分页查询实现分批取数，每次处理3000行，直到所有数据处理完毕：

CREATE OR REPLACE PROCEDURE PROC_BATCH_PROCESS_DATA
IS
    -- 定义分页参数：起始行号、每次取数行数
    v_start_row  NUMBER := 1;
    v_fetch_size CONSTANT NUMBER := 3000;
    
    -- 定义游标，用于动态分页查询（这里以EMP表为例，可替换为你的业务表）
    CURSOR c_data(p_start_row NUMBER, p_fetch_size NUMBER) IS
        SELECT empno, ename, job, sal
        FROM (
            SELECT empno, ename, job, sal,
                   ROWNUM rn  -- 生成行号用于分页
            FROM emp
            -- 这里可以添加你的业务过滤条件，比如WHERE deptno = 10
        )
        WHERE rn BETWEEN p_start_row AND p_start_row + p_fetch_size - 1;
    
    v_emp_rec c_data%ROWTYPE;  -- 游标记录类型变量，用于存储每行数据
    v_row_count NUMBER;        -- 记录当前批次取到的行数
BEGIN
    LOOP
        -- 打开游标并传入当前分页参数
        OPEN c_data(v_start_row, v_fetch_size);
        
        -- 初始化当前批次行数为0
        v_row_count := 0;
        
        LOOP
            -- 从游标中获取一行数据
            FETCH c_data INTO v_emp_rec;
            EXIT WHEN c_data%NOTFOUND;
            
            -- 这里添加你的业务处理逻辑，比如打印数据、插入到其他表等
            DBMS_OUTPUT.PUT_LINE('处理员工：' || v_emp_rec.ename || '，工号：' || v_emp_rec.empno);
            
            v_row_count := v_row_count + 1;
        END LOOP;
        
        -- 关闭游标
        CLOSE c_data;
        
        -- 如果当前批次取到的行数小于设定的取数大小，说明所有数据处理完毕，退出外层循环
        EXIT WHEN v_row_count < v_fetch_size;
        
        -- 更新起始行号，准备下一次循环取数
        v_start_row := v_start_row + v_fetch_size;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('所有数据处理完成');
EXCEPTION
    WHEN OTHERS THEN
        -- 异常处理：打印错误信息并关闭游标（如果游标处于打开状态）
        IF c_data%ISOPEN THEN
            CLOSE c_data;
        END IF;
        DBMS_OUTPUT.PUT_LINE('处理出错：' || SQLERRM);
        RAISE;
END PROC_BATCH_PROCESS_DATA;
/

关键逻辑说明 #

• 分页实现：通过嵌套查询生成ROWNUM行号，配合BETWEEN条件实现每次取指定范围的数据；也可以使用Oracle 12c+支持的OFFSET ... FETCH NEXT ... ROWS ONLY语法替代，写法更简洁：

  CURSOR c_data(p_start_row NUMBER, p_fetch_size NUMBER) IS
      SELECT empno, ename, job, sal
      FROM emp
      -- 添加业务过滤条件
      OFFSET (p_start_row - 1) ROWS FETCH NEXT p_fetch_size ROWS ONLY;
  

• 循环终止条件：当某次批次取到的行数小于3000时，说明已经没有剩余数据，退出外层循环；
• 异常处理：确保游标在异常时能被正确关闭，避免资源泄漏。

使用说明 #

1. 将代码中的emp表及查询字段替换为你的业务表和字段；
2. 在FETCH后的循环内添加实际的业务处理逻辑（比如数据清洗、插入目标表等）；
3. 可以通过EXEC PROC_BATCH_PROCESS_DATA;执行存储过程，或在PL/SQL块中调用。

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