Oracle存储过程里怎么准确搞定分页，实操讲解和技巧分享

行，直接上干货，在Oracle存储过程里搞分页，说白了就是写一段SQL，让它只返回你指定的那一页的数据，比如第10页，每页20条，这样做主要是为了性能，不然动不动查几万条数据,数据库和网络都吃不消。

最核心、最常用的方法，就是利用 ROWNUM 这个Oracle的伪列，或者从12c版本开始用的 OFFSET-FETCH 语法，下面我分开讲，重点讲用ROWNUM的玩法,因为现在用11g及以下版本的还不少。

用ROWNUM搞分页（通用、经典方法）

ROWNUM是个啥？就是Oracle给查询结果每行临时加的一个序号，从1开始，但这里有个巨坑，你必须知道：ROWNUM是在数据被筛选（WHERE）和排序（ORDER BY）的过程中一步步分配的，而且总是从1开始生成。

如果你直接写 SELECT * FROM table WHERE ROWNUM > 10 AND ROWNUM <= 20;，你会得到零条结果，因为第一条数据过来，ROWNUM被赋值为1，1不大于10，所以被筛掉了；然后第二条数据过来，Oracle又试图给它赋值为1（因为上一条没要），结果还是1，又筛掉……死循环,永远没有满足条件的。

正确姿势是搞一个嵌套查询，口诀是：先排序，再编号，最后筛选。

标准三步走模板：

SELECT *
FROM (
    SELECT t.*, ROWNUM AS rn -- 第二步：给排好序的数据加上一个连续的序号rn
    FROM (
        SELECT * FROM 你的表名 
        WHERE 你的查询条件 -- 可选的筛选条件
        ORDER BY 你的排序字段 -- 第一步：先把想要的数据顺序定下来
    ) t
    WHERE ROWNUM <= 页大小 * 页码 -- 截止到当前页的最后一条
)
WHERE rn > 页大小 * (页码 - 1); -- 从当前页的第一条开始取

实操举例： 假设有个员工表emp，要按工资sal从高到低排，查第3页的数据,每页5条。

• 页大小：5
• 页码：3
• 起始行：5 * (3-1) + 1 = 11
• 结束行：5 * 3 = 15

存储过程里的SQL就该这么写：

CREATE OR REPLACE PROCEDURE get_emp_page(p_page_num IN NUMBER, p_page_size IN NUMBER) IS
BEGIN
    FOR rec IN (
        SELECT empno, ename, sal
        FROM (
            SELECT empno, ename, sal, ROWNUM AS rn
            FROM (
                SELECT empno, ename, sal
                FROM emp
                ORDER BY sal DESC -- 1. 先按工资倒序排
            )
            WHERE ROWNUM <= p_page_size * p_page_num -- 3. 限制到最大行（第3页末尾是15条）
        )
        WHERE rn > p_page_size * (p_page_num - 1) -- 4. 从第11条开始取
    ) LOOP
        -- 这里处理每一行数据，比如用DBMS_OUTPUT打印出来
        DBMS_OUTPUT.PUT_LINE('员工号：' || rec.empno || ', 姓名：' || rec.ename || ', 工资：' || rec.sal);
    END LOOP;
END;
/

调用这个存储过程： EXEC get_emp_page(3, 5); 它就会输出工资排名第11到第15位的员工信息。

技巧分享：

1. 性能关键点：最内层的子查询只包含必要的排序列和查询列，别用SELECT *,减少排序的数据量。
2. 带WHERE条件：如果你有搜索条件，比如找部门10的员工，就把 WHERE deptno = 10 放在最内层的SELECT语句里，这样Oracle会先过滤掉不相关的数据，再排序和编号,效率最高。
3. 为什么叫“三步走”：这个过程可以理解为：
   • 内层查询：确定业务逻辑（怎么排，筛选哪些）。
   • 中层查询：应用ROWNUM上限,生成一个带连续序号的结果集。
   • 外层查询：根据序号下限,切出最终需要的那一页。

用OFFSET-FETCH语法（Oracle 12c及以上版本）

如果你的数据库版本是12c或更高，那就简单多了，语法非常直观,和MySQL的LIMIT有点像。

语法模板：

SELECT 列名
FROM 表名
WHERE 条件
ORDER BY 排序字段
OFFSET 跳过的行数 ROWS
FETCH NEXT 要取出的行数 ROWS ONLY;

还拿上面的例子说事，查第3页，每页5条：

• 跳过的行数：5 * (3-1) = 10
• 要取出的行数：5

存储过程里可以这么写：

CREATE OR REPLACE PROCEDURE get_emp_page_new(p_page_num IN NUMBER, p_page_size IN NUMBER) IS
BEGIN
    FOR rec IN (
        SELECT empno, ename, sal
        FROM emp
        ORDER BY sal DESC
        OFFSET p_page_size * (p_page_num - 1) ROWS -- 跳过前10条
        FETCH NEXT p_page_size ROWS ONLY -- 只取接下来的5条
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('员工号：' || rec.empno || ', 姓名：' || rec.ename || ', 工资：' || rec.sal);
    END LOOP;
END;
/

这个方法的好处是：

• 写法简单,一眼就能看懂逻辑。
• Oracle内部可能会做优化,性能在某些场景下比ROWNUM方式更好。

需要注意的点：

• 兼容性！一定要确认你的数据库版本是12c或以上。

总结与选择

• 如果你不确定数据库版本，或者版本较低（11g及以下）：老老实实用ROWNUM三层嵌套的方法，这是最稳妥、最通用的方案，先排序，再编号，最后筛选”的口诀。
• 如果你的数据库是12c或更新版本：果断使用 OFFSET-FETCH，代码更简洁,可读性更强。
• 核心思想都一样：避免一次性传输全部数据，通过计算偏移量来精准获取“那一页”的内容,从而提升响应速度和减轻系统负担。

无论用哪种方法，在排序字段上建立合适的索引都能极大地提升分页查询的性能,尤其是在数据量大的表中。