在Oracle数据库的实际开发场景中，当查询的数据量较大时，一次性返回所有数据会给数据库和前端带来较大压力，因此分页查询成为必备功能。将分页逻辑封装成分页存储过程，既能复用代码，也能统一分页规则，降低后续维护成本。
Oracle分页存储过程的核心参数设计
一个通用的Oracle分页存储过程需要包含输入参数和输出参数，常见的参数定义如下：
- 输入参数：表名或查询SQL、每页显示记录数、当前页码、排序字段
- 输出参数：当前页的数据结果集、总记录数、总页数
如果使用动态SQL拼接查询条件，还可以额外增加条件参数，适配不同的查询场景。
基础分页存储过程实现示例
以下是一个通用的Oracle分页存储过程示例，支持传入表名、分页参数，返回分页后的数据和统计信息：
CREATE OR REPLACE PROCEDURE sp_oracle_page(
p_table_name IN VARCHAR2, -- 查询的表名
p_page_size IN NUMBER, -- 每页记录数
p_current_page IN NUMBER, -- 当前页码
p_order_column IN VARCHAR2, -- 排序字段
p_total_count OUT NUMBER, -- 总记录数
p_total_page OUT NUMBER, -- 总页数
p_result_cursor OUT SYS_REFCURSOR -- 结果游标
) AS
v_start_row NUMBER; -- 分页起始行
v_end_row NUMBER; -- 分页结束行
v_sql VARCHAR2(2000); -- 动态SQL
v_count_sql VARCHAR2(2000); -- 统计总数SQL
BEGIN
-- 计算分页起始和结束行
v_start_row := (p_current_page - 1) * p_page_size + 1;
v_end_row := p_current_page * p_page_size;
-- 拼接统计总记录数的SQL
v_count_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
EXECUTE IMMEDIATE v_count_sql INTO p_total_count;
-- 计算总页数
IF MOD(p_total_count, p_page_size) = 0 THEN
p_total_page := p_total_count / p_page_size;
ELSE
p_total_page := FLOOR(p_total_count / p_page_size) + 1;
END IF;
-- 拼接分页查询SQL，使用ROWNUM实现分页
v_sql := 'SELECT *
FROM (
SELECT t.*, ROWNUM rn
FROM (
SELECT * FROM ' || p_table_name || ' ORDER BY ' || p_order_column || '
) t
WHERE ROWNUM <= ' || v_end_row || '
)
WHERE rn >= ' || v_start_row;
-- 打开游标返回结果集
OPEN p_result_cursor FOR v_sql;
END sp_oracle_page;
/
存储过程的调用方式
编写完成存储过程后，可以通过PL/SQL块调用，获取分页结果，示例如下：
DECLARE
v_total_count NUMBER;
v_total_page NUMBER;
v_result_cursor SYS_REFCURSOR;
v_record your_table%ROWTYPE; -- 替换为实际表名对应的记录类型
BEGIN
-- 调用分页存储过程，查询your_table表，每页10条，查第2页，按id排序
sp_oracle_page(
p_table_name => 'your_table',
p_page_size => 10,
p_current_page => 2,
p_order_column => 'id',
p_total_count => v_total_count,
p_total_page => v_total_page,
p_result_cursor => v_result_cursor
);
-- 输出统计信息
DBMS_OUTPUT.PUT_LINE('总记录数：' || v_total_count);
DBMS_OUTPUT.PUT_LINE('总页数：' || v_total_page);
-- 遍历游标获取当前页数据
LOOP
FETCH v_result_cursor INTO v_record;
EXIT WHEN v_result_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('当前记录id：' || v_record.id);
END LOOP;
CLOSE v_result_cursor;
END;
/
开发注意事项与优化建议
在实际使用Oracle分页存储过程时，需要注意以下几点：
- 动态SQL拼接表名和排序字段时，要避免SQL注入问题，生产环境可以对传入的表名、字段名做白名单校验
- 如果查询包含复杂条件，可以在存储过程中增加条件参数，拼接动态SQL时追加WHERE条件
- 对于大数据量表，排序字段建议建立索引，提升分页查询的排序效率，避免全表排序
- 如果不需要返回总页数和总记录数，可以去掉统计总数的逻辑，减少一次全表扫描，提升存储过程执行效率
常见问题解答
为什么分页要用三层嵌套查询？
Oracle的ROWNUM是在查询结果生成时分配的，外层查询如果直接使用ROWNUM做范围过滤，会导致结果不符合预期，三层嵌套先排序，再限制ROWNUM上限，最后过滤起始行，才能保证分页结果正确。
存储过程支持自定义查询条件吗？
可以支持，只需要在存储过程中增加一个条件参数，拼接动态SQL时，在统计SQL和分页SQL中追加对应的WHERE条件即可，例如增加p_where_condition参数，拼接时加上' WHERE ' || p_where_condition。