在Oracle数据库中，当业务系统需要展示海量数据的列表时，把分页逻辑写在存储过程里是一种非常务实的做法。它既能隐藏复杂SQL，又能借助数据库端的游标和绑定变量降低应用服务器压力。Oracle本身没有提供像MySQL那样直观的limit offset, size语法，而是依靠rownum伪列配合嵌套子查询来实现物理分页。
一、为什么要用存储过程做分页
把分页封装成存储过程，首先带来的是接口稳定性。前端或者后端服务只需要传入页码和每页条数，就能拿到当前页数据与总记录数，不必关心底层表结构变化。如果将来表加了字段或者拆分了分区，只要存储过程内部调整，对外参数保持不变，调用方完全无感。
其次是性能层面的收益。Oracle对存储过程里的SQL会做游标缓存，使用绑定变量（如:p_page、:p_size）可以避免每次请求都发生硬解析。对于高并发的列表查询，软解析比例提升后，CPU开销显著下降。此外，存储过程可以一次性用out参数返回总条数和数据集，减少应用与数据库的交互次数。
二、基础分页存储过程写法
最核心的坑是rownum的生成时机。rownum是在结果集返回时按顺序分配的，如果先写where rownum <= 10 order by salary desc，数据库会先给无序数据编上1到10的号再排序，导致分页内容错误。正确方式是在内层先完成排序，然后外层再用rownum做区间过滤。
下面给出一个基于emp表的示例，包含总条数查询与分页数据查询。注意入参的页码从1开始，每页大小由调用方指定。
create or replace procedure sp_emp_page(
p_page in number,
p_size in number,
p_total out number,
p_cursor out sys_refcursor
) as
v_start number;
v_end number;
begin
-- 计算起始和结束行号
v_start := (p_page - 1) * p_size + 1;
v_end := p_page * p_size;
-- 查询总记录数
select count(*) into p_total from emp;
-- 打开游标返回分页数据
open p_cursor for
select * from (
select a.*, rownum rn from (
select * from emp order by empno desc
) a
where rownum <= v_end
)
where rn >= v_start;
end sp_emp_page;
上述代码中，最里层子查询先按empno倒序排列，中间层用rownum <= v_end截断前面页的数据，最外层再用rn >= v_start取下界。这种三层嵌套是Oracle分页的经典结构，逻辑清晰且优化器容易识别。
调用时可以在PLSQL块里声明变量接收结果。比如要取第2页、每页10条，就传入p_page=2、p_size=10，总条数会写入p_total，当前页数据通过p_cursor流出。Java或Python等语言也能通过JDBC或OCI对接sys_refcursor拿到结果集。
三、大偏移量下的性能问题
当翻到很深的页码，例如第10000页，v_start会非常大。虽然中间层用rownum <= v_end限制了扫描上限，但最里层排序仍要遍历并排序全表，深翻页时响应会变慢。这是所有基于偏移量的分页共性瓶颈，并非存储过程本身缺陷。
一种缓解方案是改用键集分页（keyset pagination）。如果排序字段唯一，可记录上一页最后一条的empno值，内层用where empno < 上次最大值代替rownum下界，再取前p_size条。这样数据库可以利用索引范围扫描，避免大偏移。下面的改造片段展示了思路：
create or replace procedure sp_emp_page_keyset(
p_last_id in number,
p_size in number,
p_cursor out sys_refcursor
) as
begin
open p_cursor for
select * from (
select * from emp
where empno < p_last_id
order by empno desc
)
where rownum <= p_size;
end sp_emp_page_keyset;
这种写法去掉了总条数查询，更适合无限下拉的场景。它的缺点是没法直接跳到第N页，只能一页页往后翻，但换来了数量级的性能提升。在后台管理系统的浅分页可用前面的全量计数方案，在C端信息流可用键集方案，两者互补。
四、常见错误与排查
初学者常把rownum当普通列用，写成where rownum > 10，这永远返回空，因为rownum从1开始分配，不满足大于10的条件就淘汰，后续行号也不会生成。必须用子查询把rownum固化成别名rn，再在外层用rn做大于判断。
另一个易错点是在存储过程里拼接字符串执行动态SQL时，忘记对表名或排序列做白名单校验，可能引发SQL注入。如果分页的排序字段由用户传参，建议用case when映射到已知列，而不是直接拼接到SQL文本里。如下面这样处理更安全：
declare
v_order_col varchar2(30);
begin
v_order_col := case p_sort
when 'name' then 'ename'
when 'sal' then 'sal'
else 'empno' end;
-- 仅允许映射后的固定列参与排序
end;
通过映射取代拼接，既满足灵活排序，又封死了注入通道。配合前面讲到的分页骨架，就能在真实项目中写出健壮的Oracle分页存储过程。