Oracle 分页存储过程实现

在Oracle数据库中实现分页功能通常需要使用ROWNUM或ROW_NUMBER()函数。下面我将介绍几种实现分页的存储过程方法。

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

CREATE OR REPLACE PROCEDURE paging_query(
    p_table_name    IN VARCHAR2,      -- 表名
    p_fields       IN VARCHAR2,      -- 查询字段
    p_page_size    IN NUMBER,        -- 每页记录数
    p_page_no      IN NUMBER,        -- 当前页码
    p_order_by     IN VARCHAR2,      -- 排序字段
    p_total_records OUT NUMBER,      -- 总记录数
    p_total_pages   OUT NUMBER,      -- 总页数
    p_cursor       OUT SYS_REFCURSOR -- 返回结果集
)
IS
    v_sql VARCHAR2(2000);
    v_start NUMBER := (p_page_no - 1) * p_page_size + 1;
    v_end NUMBER := p_page_no * p_page_size;
BEGIN
    -- 计算总记录数
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_table_name INTO p_total_records;

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

    -- 分页查询
    v_sql := 'SELECT * FROM (
                SELECT A.*, ROWNUM rn FROM (
                    SELECT ' || p_fields || ' FROM ' || p_table_name || 
                    ' ORDER BY ' || p_order_by || 
                ') A WHERE ROWNUM <= ' || v_end || 
              ') WHERE rn >= ' || v_start;

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

方法二：使用ROW_NUMBER()函数实现分页（Oracle 9i及以上版本）

CREATE OR REPLACE PROCEDURE paging_query_row_number(
    p_table_name    IN VARCHAR2,
    p_fields       IN VARCHAR2,
    p_page_size    IN NUMBER,
    p_page_no      IN NUMBER,
    p_order_by     IN VARCHAR2,
    p_total_records OUT NUMBER,
    p_total_pages   OUT NUMBER,
    p_cursor       OUT SYS_REFCURSOR
)
IS
    v_sql VARCHAR2(2000);
    v_start NUMBER := (p_page_no - 1) * p_page_size + 1;
    v_end NUMBER := p_page_no * p_page_size;
BEGIN
    -- 计算总记录数
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_table_name INTO p_total_records;

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

    -- 分页查询
    v_sql := 'SELECT * FROM (
                SELECT ' || p_fields || ', 
                       ROW_NUMBER() OVER (ORDER BY ' || p_order_by || ') AS rn 
                FROM ' || p_table_name || 
              ') WHERE rn BETWEEN ' || v_start || ' AND ' || v_end;

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

方法三：使用12c及以上版本的OFFSET-FETCH语法

CREATE OR REPLACE PROCEDURE paging_query_12c(
    p_table_name    IN VARCHAR2,
    p_fields       IN VARCHAR2,
    p_page_size    IN NUMBER,
    p_page_no      IN NUMBER,
    p_order_by     IN VARCHAR2,
    p_total_records OUT NUMBER,
    p_total_pages   OUT NUMBER,
    p_cursor       OUT SYS_REFCURSOR
)
IS
    v_sql VARCHAR2(2000);
    v_offset NUMBER := (p_page_no - 1) * p_page_size;
BEGIN
    -- 计算总记录数
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_table_name INTO p_total_records;

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

    -- 分页查询(12c及以上版本)
    v_sql := 'SELECT ' || p_fields || ' FROM ' || p_table_name || 
              ' ORDER BY ' || p_order_by || 
              ' OFFSET ' || v_offset || ' ROWS FETCH NEXT ' || p_page_size || ' ROWS ONLY';

    -- 打开游标返回结果
    OPEN p_cursor FOR v_sql;
END paging_query_12c;
/

调用示例

DECLARE
    v_total_records NUMBER;
    v_total_pages NUMBER;
    v_cursor SYS_REFCURSOR;
    v_empno employees.empno%TYPE;
    v_ename employees.ename%TYPE;
BEGIN
    -- 调用分页存储过程
    paging_query(
        p_table_name => 'employees',
        p_fields => 'empno, ename, job, sal',
        p_page_size => 10,
        p_page_no => 2,
        p_order_by => 'empno',
        p_total_records => v_total_records,
        p_total_pages => v_total_pages,
        p_cursor => v_cursor
    );

    -- 输出分页信息
    DBMS_OUTPUT.PUT_LINE('总记录数: ' || v_total_records);
    DBMS_OUTPUT.PUT_LINE('总页数: ' || v_total_pages);

    -- 处理结果集
    DBMS_OUTPUT.PUT_LINE('第2页数据:');
    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;
/

注意事项

1. 对于大型表，分页查询的性能可能会受到影响，特别是在后面几页
2. 确保排序字段上有适当的索引以提高分页性能
3. 在Oracle 12c及以上版本，推荐使用OFFSET-FETCH语法，它更简洁且性能更好
4. 对于复杂查询，可能需要调整存储过程以适应特定的业务需求

以上存储过程可以根据实际需求进行修改和优化。