在Oracle数据库中处理大量数据的分页需求时，把分页逻辑封装进存储过程是一种常见且高效的做法。它既能减少网络传输量，又能利用数据库自身的优化器提升查询性能。下面通过一个完整实例，说明如何编写可复用的Oracle分页存储过程。
一、分页的基本原理与rownum机制
Oracle中没有像MySQL那样直接的limit语法（12c之前），而是依靠名为rownum的伪列来实现行号控制。rownum是在查询结果集返回时按顺序分配的一个序号，从1开始。需要注意的是，rownum必须先被查询出来，然后才能在外层对其进行大于某值的过滤，否则无法命中索引或得到空结果。
因此最常见的分页写法是三层嵌套：最内层做排序和查询，中间层用rownum起别名并限制上限，最外层再过滤下限。这样数据库只需扫描到所需页的数据量，而不必全表加载。理解这一点是写出正确存储过程的前提。
1.1 为什么不能直接用where rownum between
很多初学者会尝试写select * from emp where rownum between 10 and 20，但这样永远查不到数据。因为rownum是在记录被选出后才编号，当第一条不满足between时，后续记录的rownum不会递增填补。必须用子查询把rownum固化成普通列，再在外层判断。
示例错误与正确逻辑对比如下，错误写法返回零行，正确写法通过内层先赋列名rno来解决：
-- 错误示例：永远无结果
select * from emp where rownum between 10 and 20;
-- 正确示例：三层嵌套
select * from (
select e.*, rownum rno from (
select * from emp order by empno
) e where rownum <= 20
) where rno >= 10;
二、创建分页存储过程实例
我们创建一个名为proc_paginate_emp的存储过程，接收表名、排序字段、页码、页大小，并输出当前页数据和总记录数。为简化演示，这里固定对emp表操作，实际中可用动态SQL扩展。
存储过程使用sys_refcursor作为输出游标，调用方（如Java或PL/SQL块）可直接遍历。同时用count(*)计算总数，通过out参数返回，避免前端再发一次统计请求。
2.1 存储过程代码
create or replace procedure proc_paginate_emp(
p_page_no in number,
p_page_size in number,
p_total out number,
p_cursor out sys_refcursor
) as
v_start number;
v_end number;
begin
-- 计算起止行号
v_start := (p_page_no - 1) * p_page_size + 1;
v_end := p_page_no * p_page_size;
-- 查询总记录数
select count(*) into p_total from emp;
-- 打开游标返回当前页数据
open p_cursor for
select * from (
select e.*, rownum rno from (
select * from emp order by empno
) e where rownum <= v_end
) where rno >= v_start;
end proc_paginate_emp;
2.2 在PL/SQL中调用测试
下面代码演示如何声明变量并调用上述过程，打印出第二页每页五条的数据以及总数：
declare
v_total number;
v_cur sys_refcursor;
v_emp emp%rowtype;
begin
proc_paginate_emp(2, 5, v_total, v_cur);
dbms_output.put_line('总记录数:' || v_total);
loop
fetch v_cur into v_emp;
exit when v_cur%notfound;
dbms_output.put_line(v_emp.empno || ' ' || v_emp.ename);
end loop;
close v_cur;
end;
三、Oracle 12c及以后版本的offset写法
从Oracle 12c开始，官方支持了ANSI标准的offset fetch语法，让分页变得更直观。如果数据库版本允许，可以改写游标部分，无需rownum嵌套。
这种写法可读性更好，优化器也能更容易地生成执行计划，但在旧版本中不可用。在存储过程中可通过条件编译或分开的过程来兼容。
3.1 使用offset fetch的游标
open p_cursor for select * from emp order by empno offset (p_page_no - 1) * p_page_size rows fetch next p_page_size rows only;
3.2 两种方案对比
| 方案 | 适用版本 | 可读性 | 性能特点 |
|---|---|---|---|
| rownum三层嵌套 | 所有版本 | 一般 | 稳定，依赖内层排序索引 |
| offset fetch | 12c及以上 | 好 | 语义清晰，计划易优化 |
四、注意事项与优化建议
在真实项目中，排序字段应当有索引，否则内层查询会触发全表排序，分页优势尽失。另外，如果数据实时变化剧烈，count(*)得到的总数与当前页可能不一致，业务上需容忍或加快照。
对于超大数据表，可考虑利用主键范围分段代替通用分页，或者把总数缓存起来。存储过程里尽量使用绑定变量，防止硬解析过多。通过合理设计，Oracle存储过程分页能支撑高并发后台查询场景。