在Oracle数据库的实际应用场景中，当数据表的记录数达到数十万甚至上百万级别时，前端查询列表的分页操作如果处理不当，很容易出现查询耗时过长的问题。传统的前端分页需要一次性查询所有数据再截取，后端分页如果直接拼接ROWNUM条件也容易出现索引失效的情况，而分页存储过程可以通过优化的查询逻辑和参数传递，有效提升分页查询的执行效率。
分页存储过程的核心设计思路
分页存储过程的核心目标是接收分页参数，返回指定页的数据和总记录数，避免冗余数据的查询和传输。设计时需要明确几个核心参数：
- 输入参数：表名或查询SQL、当前页码、每页记录数
- 输出参数：当前页的数据集合、总记录数
- 内部逻辑：先计算符合条件的总记录数，再根据页码和每页条数计算查询的起始和结束行号，通过ROWNUM实现范围筛选
基础分页存储过程实现示例
以下是一个通用的Oracle分页存储过程示例，支持传入自定义查询SQL实现分页：
CREATE OR REPLACE PROCEDURE sp_page_query(
p_sql IN VARCHAR2, -- 原始查询SQL（不需要包含分页逻辑）
p_page_num IN NUMBER, -- 当前页码（从1开始）
p_page_size IN NUMBER, -- 每页记录数
p_total_count OUT NUMBER, -- 输出总记录数
p_result OUT SYS_REFCURSOR -- 输出当前页数据游标
) AS
v_start_row NUMBER; -- 分页起始行号
v_end_row NUMBER; -- 分页结束行号
v_count_sql VARCHAR2(4000); -- 统计总记录数的SQL
v_query_sql VARCHAR2(4000); -- 分页查询SQL
BEGIN
-- 计算总记录数
v_count_sql := 'SELECT COUNT(*) FROM (' || p_sql || ')';
EXECUTE IMMEDIATE v_count_sql INTO p_total_count;
-- 计算分页行号范围
v_start_row := (p_page_num - 1) * p_page_size + 1;
v_end_row := p_page_num * p_page_size;
-- 拼接分页查询SQL，使用ROWNUM实现范围筛选
v_query_sql := 'SELECT * FROM ('
|| ' SELECT t.*, ROWNUM rn FROM (' || p_sql || ') t WHERE ROWNUM <= ' || v_end_row
|| ') WHERE rn >= ' || v_start_row;
-- 打开游标返回当前页数据
OPEN p_result FOR v_query_sql;
END sp_page_query;
/
存储过程调用示例
假设需要查询用户表user_info中状态为1的用户数据，每页显示10条，查询第2页的数据，调用方式如下：
DECLARE
v_total NUMBER;
v_cur SYS_REFCURSOR;
v_user_id NUMBER;
v_user_name VARCHAR2(50);
BEGIN
-- 调用分页存储过程，传入查询SQL、页码2、每页10条
sp_page_query(
p_sql => 'SELECT user_id, user_name FROM user_info WHERE status = 1 ORDER BY create_time DESC',
p_page_num => 2,
p_page_size => 10,
p_total_count => v_total,
p_result => v_cur
);
-- 输出总记录数
DBMS_OUTPUT.PUT_LINE('总记录数：' || v_total);
-- 遍历游标输出当前页数据
LOOP
FETCH v_cur INTO v_user_id, v_user_name;
EXIT WHEN v_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('用户ID：' || v_user_id || '，用户名：' || v_user_name);
END LOOP;
CLOSE v_cur;
END;
/
使用注意事项
- 传入的原始查询SQL最好已经包含排序逻辑，避免分页结果顺序混乱
- 如果查询的表数据量极大，建议给查询条件的字段添加合适的索引，避免全表扫描
- 存储过程中的动态SQL拼接需要注意SQL注入风险，如果是应用层调用，需要对传入的SQL部分做合法性校验
- 如果分页的表结构固定，也可以将表名和查询条件作为参数传入，进一步封装存储过程的逻辑，减少动态SQL的拼接复杂度
性能对比说明
和使用ROWNUM在应用层拼接分页SQL的方式相比，分页存储过程的优势在于：总记录数查询和分页数据查询的逻辑都在数据库内部完成，减少了应用层和数据库之间的交互次数；同时存储过程会被Oracle缓存执行计划，重复调用时不需要重新解析SQL，进一步提升了执行效率。在数据量超过10万条的表上，分页存储过程的查询耗时通常比普通分页方式降低30%以上。