在Oracle数据库中实现分页查询，最常见的做法是利用rownum伪列或者分析函数row_number()，将查询逻辑封装到存储过程里统一对外提供服务。这样做不仅可以减少网络传输冗余数据，还能让业务代码摆脱复杂的分页SQL拼接。下面先通过一张示意图了解整体调用链路。
一、为什么需要分页存储过程
当表数据量增长到几十万甚至上亿行时，如果每次查询都返回全量结果集再在应用内存中截取，会严重消耗数据库IO和应用服务器内存。Oracle的SQL执行计划往往会对全表扫描产生巨大的代价，而分页的本质是只取当前页需要的少数几行。
将分页封装为存储过程，有几个实际好处。第一，存储过程在数据库端编译，执行计划可缓存，重复使用绑定变量能减少硬解析。第二，前端只需要传入页码和每页大小，不需要感知底层是用rownum还是row_number。第三，可以在过程内部统一处理总记录数查询、空页保护等边界情况，避免每个开发人员写出不同风格的低效SQL。
二、基于rownum的经典写法与陷阱
很多初学者会直接在外层用rownum过滤，例如写where rownum > 10 and rownum <= 20，这永远查不到数据。因为rownum是在结果集返回时按顺序分配的，第一行必须是1，如果第一行的rownum不满足>10，它就被丢弃，后续行永远补不上来。正确做法是将原查询作为内嵌视图，先让rownum在内部生成，再在外层限制。
下面给出一个简单的分页函数示例，使用嵌套查询和rownum实现。注意内部查询必须取别名，并且给rownum起一个列名，否则外层无法引用。
create or replace procedure sp_page_by_rownum(
p_table in varchar2,
p_page in number,
p_size in number,
p_cursor out sys_refcursor,
p_total out number
) as
v_start number := (p_page - 1) * p_size + 1;
v_end number := p_page * p_size;
begin
-- 统计总条数
execute immediate 'select count(*) from ' || p_table into p_total;
-- 打开分页游标
open p_cursor for
select * from (
select a.*, rownum rn from (
select * from ' || p_table || ' order by id
) a where rownum <= ' || v_end || '
) where rn >= ' || v_start;
end sp_page_by_rownum;
这种写法在小数据量时没问题，但使用动态SQL拼接表名存在SQL注入风险，且字符串拼接会导致无法复用带绑定变量的执行计划。另外，内层order by必须确保有索引支撑，否则排序本身就会成为性能瓶颈。
三、使用row_number()分析函数的改进方案
从Oracle 9i开始，分析函数row_number()可以更直观地给每行编号，配合窗口子句完成分页。它的逻辑是在排序后的结果集上打序号，再在外层过滤区间。相比rownum嵌套，语义更清晰，也更容易加上条件过滤。
以下是一个封装在包中的存储过程示例，采用绑定变量和静态SQL（假设表名固定为emp），实际项目中可改为视图或特定业务表，避免动态拼表名。
create or replace package pkg_page as
procedure get_emp_page(
p_page in number,
p_size in number,
p_cur out sys_refcursor,
p_total out number
);
end pkg_page;
create or replace package body pkg_page as
procedure get_emp_page(
p_page in number,
p_size in number,
p_cur out sys_refcursor,
p_total out number
) is
v_start number := (p_page - 1) * p_size + 1;
v_end number := p_page * p_size;
begin
select count(*) into p_total from emp;
open p_cur for
select * from (
select e.*, row_number() over (order by e.id) as seq
from emp e
) t
where t.seq between v_start and v_end;
end get_emp_page;
end pkg_page;
该方案把分页逻辑收拢到包内，调用方只需pkg_page.get_emp_page(2,10,cur,total)即可拿到第2页数据和总条数。由于使用了between和绑定变量，Oracle可以把执行计划缓存下来。如果emp表的id是主键，排序几乎零成本；若按非索引列排序，建议先建函数索引或物化视图。
四、总记录数与性能优化建议
分页接口通常要返回总条数，以便前端渲染页码。上面的例子每次都执行一次count(*)，在大数据表上代价不小。如果业务允许，可以只在第一页统计总数并缓存，后续页复用。或者改用近似统计：select num_rows from user_tables拿到估算值，适合对精确度不敏感的展示。
索引方面，排序列务必命中索引，否则Oracle只能全表排序。对于多条件筛选的分页，可以建组合索引，把where条件和order by字段都覆盖进去。另外，存储过程里尽量用sys_refcursor返回结果集，避免定义临时表，减少锁和段管理开销。
五、调用示例与注意事项
在Java或C#中调用上述存储过程，通常通过CallableStatement注册out参数。以JDBC为例，先注册游标类型，执行后遍历ResultSet即可。注意游标在数据库会话关闭前有效，取数要尽快完成。
最后提醒，分页页码要从1开始，存储过程里要做好p_page小于1或p_size过大的防御；每页大小建议限制在100以内，防止单页数据量过大拖垮网络。动态表名场景如果无法避免，务必用dbms_assert对输入做白名单校验，杜绝注入。