Oracle分页存储过程优化方案

分页查询的常见问题

在Oracle数据库中进行分页查询时，开发者常遇到以下问题： - 性能低下，特别是大数据量时 - 代码重复性高 - 分页逻辑与业务逻辑混杂 - 不同分页需求需要重写SQL

高效分页存储过程实现

1. 基础分页存储过程

CREATE OR REPLACE PROCEDURE sp_pagination_query(
    p_sql         IN VARCHAR2,      -- 基础SQL语句
    p_page_size   IN NUMBER,        -- 每页记录数
    p_page_no     IN NUMBER,        -- 当前页码
    p_total_rec   OUT NUMBER,       -- 总记录数
    p_total_page  OUT NUMBER,       -- 总页数
    p_result_cursor OUT SYS_REFCURSOR -- 返回结果集
) AS
    v_start_row NUMBER;
    v_end_row   NUMBER;
    v_count_sql VARCHAR2(4000);
BEGIN
    -- 计算总记录数
    v_count_sql := 'SELECT COUNT(*) FROM (' || p_sql || ')';
    EXECUTE IMMEDIATE v_count_sql INTO p_total_rec;

    -- 计算总页数
    p_total_page := CEIL(p_total_rec / p_page_size);

    -- 计算起始行和结束行
    v_start_row := (p_page_no - 1) * p_page_size + 1;
    v_end_row := p_page_no * p_page_size;

    -- 执行分页查询
    OPEN p_result_cursor FOR 
        'SELECT * FROM (
            SELECT a.*, ROWNUM rn FROM (' || p_sql || ') a 
            WHERE ROWNUM <= ' || v_end_row || '
        ) WHERE rn >= ' || v_start_row;
END sp_pagination_query;

2. 使用ROW_NUMBER()的高级分页（Oracle 12c+）

CREATE OR REPLACE PROCEDURE sp_advanced_pagination(
    p_sql         IN VARCHAR2,
    p_page_size   IN NUMBER,
    p_page_no     IN NUMBER,
    p_total_rec   OUT NUMBER,
    p_total_page  OUT NUMBER,
    p_result_cursor OUT SYS_REFCURSOR
) AS
    v_start_row NUMBER;
    v_end_row   NUMBER;
BEGIN
    -- 计算总记录数
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || p_sql || ')' INTO p_total_rec;

    -- 计算分页信息
    p_total_page := CEIL(p_total_rec / p_page_size);
    v_start_row := (p_page_no - 1) * p_page_size + 1;
    v_end_row := p_page_no * p_page_size;

    -- 使用ROW_NUMBER()分页
    OPEN p_result_cursor FOR 
        'SELECT * FROM (
            SELECT t.*, ROW_NUMBER() OVER (ORDER BY 1) rn FROM (' || p_sql || ') t
        ) WHERE rn BETWEEN ' || v_start_row || ' AND ' || v_end_row;
END sp_advanced_pagination;

3. 带排序参数的分页存储过程

CREATE OR REPLACE PROCEDURE sp_pagination_with_sort(
    p_table_name  IN VARCHAR2,
    p_columns     IN VARCHAR2 DEFAULT '*',
    p_where       IN VARCHAR2 DEFAULT NULL,
    p_order_by    IN VARCHAR2,
    p_page_size   IN NUMBER,
    p_page_no     IN NUMBER,
    p_total_rec   OUT NUMBER,
    p_total_page  OUT NUMBER,
    p_result_cursor OUT SYS_REFCURSOR
) AS
    v_sql        VARCHAR2(4000);
    v_start_row  NUMBER;
    v_end_row    NUMBER;
BEGIN
    -- 构建基础SQL
    v_sql := 'SELECT ' || p_columns || ' FROM ' || p_table_name;
    IF p_where IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where;
    END IF;

    -- 计算总记录数
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || v_sql || ')' INTO p_total_rec;

    -- 计算分页信息
    p_total_page := CEIL(p_total_rec / p_page_size);
    v_start_row := (p_page_no - 1) * p_page_size + 1;
    v_end_row := p_page_no * p_page_size;

    -- 执行分页查询
    OPEN p_result_cursor FOR 
        'SELECT * FROM (
            SELECT a.*, ROWNUM rn FROM (' || v_sql || ' ORDER BY ' || p_order_by || ') a 
            WHERE ROWNUM <= ' || v_end_row || '
        ) WHERE rn >= ' || v_start_row;
END sp_pagination_with_sort;

使用示例

-- 示例1：基础分页查询
DECLARE
    v_total_rec  NUMBER;
    v_total_page NUMBER;
    v_cursor     SYS_REFCURSOR;
BEGIN
    sp_pagination_query(
        'SELECT * FROM employees WHERE department_id = 10',
        10,  -- 每页10条
        2,   -- 第2页
        v_total_rec,
        v_total_page,
        v_cursor
    );

    DBMS_OUTPUT.PUT_LINE('总记录数: ' || v_total_rec);
    DBMS_OUTPUT.PUT_LINE('总页数: ' || v_total_page);
    -- 处理结果集...
END;

-- 示例2：带排序的分页查询
DECLARE
    v_total_rec  NUMBER;
    v_total_page NUMBER;
    v_cursor     SYS_REFCURSOR;
BEGIN
    sp_pagination_with_sort(
        'employees',
        'employee_id, first_name, last_name, salary',
        'department_id = 20',
        'salary DESC',
        5,  -- 每页5条
        1,   -- 第1页
        v_total_rec,
        v_total_page,
        v_cursor
    );

    -- 处理结果集...
END;

性能优化建议

1. 索引优化：确保分页查询的排序字段和条件字段有适当的索引
2. **避免SELECT ***：只查询需要的列，减少数据传输量
3. 绑定变量：对于动态SQL，考虑使用绑定变量而非字符串拼接
4. 结果集缓存：对于不常变动的数据，考虑使用缓存
5. 分区表：超大表考虑使用分区表提高查询效率
6. 使用12c的FETCH FIRST语法（Oracle 12c及以上版本）：

-- Oracle 12c+ 更简洁的分页语法
SELECT * FROM employees
ORDER BY employee_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

通过使用这些存储过程，可以显著提高Oracle分页查询的效率和可维护性，同时保持代码的整洁和一致性。