Oracle存储过程实现分页查询详解

分页查询是数据库应用中常见的需求，下面我将详细介绍如何使用Oracle存储过程实现高效的分页查询功能。

基本分页原理

Oracle中常用的分页技术主要有三种： 1. 使用ROWNUM伪列 2. 使用ROW_NUMBER()分析函数(12c及以上版本更推荐) 3. 使用OFFSET-FETCH语法(12c及以上版本)

方法一：使用ROWNUM的存储过程实现

CREATE OR REPLACE PROCEDURE paging_query(
    p_table_name    IN VARCHAR2,   -- 表名
    p_page_size     IN NUMBER,     -- 每页记录数
    p_page_no       IN NUMBER,     -- 当前页码
    p_order_column  IN VARCHAR2,   -- 排序列
    p_total_records OUT NUMBER,    -- 总记录数
    p_total_pages   OUT NUMBER,    -- 总页数
    p_result_cursor OUT SYS_REFCURSOR  -- 结果集游标
) AS
    v_sql       VARCHAR2(4000);
    v_start_row NUMBER;
    v_end_row   NUMBER;
BEGIN
    -- 计算起始行和结束行
    v_start_row := (p_page_no - 1) * p_page_size + 1;
    v_end_row := p_page_no * p_page_size;

    -- 获取总记录数
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
    EXECUTE IMMEDIATE v_sql INTO p_total_records;

    -- 计算总页数
    p_total_pages := CEIL(p_total_records / p_page_size);

    -- 获取分页数据
    v_sql := 'SELECT * FROM (
                SELECT a.*, ROWNUM rn FROM (
                  SELECT * FROM ' || p_table_name || 
                  ' ORDER BY ' || p_order_column || 
                ') a WHERE ROWNUM <= ' || v_end_row || 
              ') WHERE rn >= ' || v_start_row;

    -- 打开游标返回结果
    OPEN p_result_cursor FOR v_sql;
END paging_query;
/

调用示例

DECLARE
    v_total_records NUMBER;
    v_total_pages   NUMBER;
    v_cursor        SYS_REFCURSOR;
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    -- 其他字段...
BEGIN
    paging_query(
        p_table_name    => 'emp',
        p_page_size     => 5,
        p_page_no       => 2,
        p_order_column  => 'empno',
        p_total_records => v_total_records,
        p_total_pages   => v_total_pages,
        p_result_cursor => v_cursor
    );

    DBMS_OUTPUT.PUT_LINE('总记录数: ' || v_total_records);
    DBMS_OUTPUT.PUT_LINE('总页数: ' || v_total_pages);

    LOOP
        FETCH v_cursor INTO v_empno, v_ename; -- 根据实际表结构调整
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
    END LOOP;

    CLOSE v_cursor;
END;
/

方法二：使用ROW_NUMBER()的存储过程实现(12c及以上推荐)

CREATE OR REPLACE PROCEDURE paging_query_row_number(
    p_table_name    IN VARCHAR2,
    p_page_size     IN NUMBER,
    p_page_no       IN NUMBER,
    p_order_column  IN VARCHAR2,
    p_total_records OUT NUMBER,
    p_total_pages   OUT NUMBER,
    p_result_cursor OUT SYS_REFCURSOR
) AS
    v_sql       VARCHAR2(4000);
    v_start_row NUMBER;
    v_end_row   NUMBER;
BEGIN
    -- 计算起始行和结束行
    v_start_row := (p_page_no - 1) * p_page_size + 1;
    v_end_row := p_page_no * p_page_size;

    -- 获取总记录数
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
    EXECUTE IMMEDIATE v_sql INTO p_total_records;

    -- 计算总页数
    p_total_pages := CEIL(p_total_records / p_page_size);

    -- 获取分页数据(使用ROW_NUMBER)
    v_sql := 'SELECT * FROM (
                SELECT t.*, 
                       ROW_NUMBER() OVER (ORDER BY ' || p_order_column || ') AS rn 
                FROM ' || p_table_name || ' t
              ) WHERE rn BETWEEN ' || v_start_row || ' AND ' || v_end_row;

    -- 打开游标返回结果
    OPEN p_result_cursor FOR v_sql;
END paging_query_row_number;
/

方法三：使用12c的OFFSET-FETCH语法(最简单)

CREATE OR REPLACE PROCEDURE paging_query_offset(
    p_table_name    IN VARCHAR2,
    p_page_size     IN NUMBER,
    p_page_no       IN NUMBER,
    p_order_column  IN VARCHAR2,
    p_total_records OUT NUMBER,
    p_total_pages   OUT NUMBER,
    p_result_cursor OUT SYS_REFCURSOR
) AS
    v_sql       VARCHAR2(4000);
    v_offset    NUMBER;
BEGIN
    -- 计算偏移量
    v_offset := (p_page_no - 1) * p_page_size;

    -- 获取总记录数
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
    EXECUTE IMMEDIATE v_sql INTO p_total_records;

    -- 计算总页数
    p_total_pages := CEIL(p_total_records / p_page_size);

    -- 获取分页数据(使用OFFSET-FETCH)
    v_sql := 'SELECT * FROM ' || p_table_name || 
             ' ORDER BY ' || p_order_column || 
             ' OFFSET ' || v_offset || ' ROWS FETCH NEXT ' || p_page_size || ' ROWS ONLY';

    -- 打开游标返回结果
    OPEN p_result_cursor FOR v_sql;
END paging_query_offset;
/

性能优化建议

1. 索引优化：确保排序列(p_order_column)上有适当的索引
2. 参数化查询：对于复杂查询，考虑使用绑定变量而非字符串拼接
3. 只查询必要字段：避免使用SELECT *，只查询需要的列
4. 大表分页：对于大表，深度分页(如第1000页)性能较差，考虑使用"上一页/下一页"模式而非直接跳转

高级扩展

带条件的分页查询

CREATE OR REPLACE PROCEDURE paging_query_with_condition(
    p_table_name    IN VARCHAR2,
    p_conditions    IN VARCHAR2,  -- 例如: "deptno = 10 AND sal > 2000"
    p_page_size     IN NUMBER,
    p_page_no       IN NUMBER,
    p_order_column  IN VARCHAR2,
    p_total_records OUT NUMBER,
    p_total_pages   OUT NUMBER,
    p_result_cursor OUT SYS_REFCURSOR
) AS
    v_sql       VARCHAR2(4000);
    v_start_row NUMBER;
    v_end_row   NUMBER;
BEGIN
    v_start_row := (p_page_no - 1) * p_page_size + 1;
    v_end_row := p_page_no * p_page_size;

    -- 获取总记录数(带条件)
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
    IF p_conditions IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_conditions;
    END IF;
    EXECUTE IMMEDIATE v_sql INTO p_total_records;

    p_total_pages := CEIL(p_total_records / p_page_size);

    -- 获取分页数据(带条件)
    v_sql := 'SELECT * FROM (
                SELECT a.*, ROWNUM rn FROM (
                  SELECT * FROM ' || p_table_name;
    IF p_conditions IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_conditions;
    END IF;
    v_sql := v_sql || ' ORDER BY ' || p_order_column || 
             ') a WHERE ROWNUM <= ' || v_end_row || 
             ') WHERE rn >= ' || v_start_row;

    OPEN p_result_cursor FOR v_sql;
END paging_query_with_condition;
/

通过以上存储过程，您可以实现灵活高效的分页查询功能，根据Oracle版本和具体需求选择最适合的实现方式。