Oracle 分页存储过程实现方案

在Oracle数据库中实现分页功能通常需要使用存储过程。以下是几种常见的Oracle分页存储过程实现方式：

1. 使用ROWNUM实现分页（Oracle传统方式）

CREATE OR REPLACE PROCEDURE paging_procedure (
    p_table_name    IN VARCHAR2,
    p_page_size     IN NUMBER,
    p_page_no       IN NUMBER,
    p_total_records OUT NUMBER,
    p_total_pages   OUT NUMBER,
    p_cursor        OUT SYS_REFCURSOR
) AS
    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 * FROM ' || p_table_name || ' 
                    ORDER BY id
                ) a WHERE ROWNUM <= ' || v_end || '
              ) WHERE rn >= ' || v_start;

    OPEN p_cursor FOR v_sql;
END paging_procedure;

2. 使用Oracle 12c及以上版本的OFFSET-FETCH语法

CREATE OR REPLACE PROCEDURE paging_procedure_12c (
    p_table_name    IN VARCHAR2,
    p_page_size     IN NUMBER,
    p_page_no       IN NUMBER,
    p_total_records OUT NUMBER,
    p_total_pages   OUT NUMBER,
    p_cursor        OUT SYS_REFCURSOR
) AS
    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);

    -- 使用OFFSET-FETCH分页查询
    OPEN p_cursor FOR 
        'SELECT * FROM ' || p_table_name || ' 
         ORDER BY id 
         OFFSET ' || v_offset || ' ROWS FETCH NEXT ' || p_page_size || ' ROWS ONLY';
END paging_procedure_12c;

3. 带条件查询的分页存储过程

CREATE OR REPLACE PROCEDURE conditional_paging (
    p_table_name    IN VARCHAR2,
    p_where_clause  IN VARCHAR2 DEFAULT NULL,
    p_order_by      IN VARCHAR2 DEFAULT 'id',
    p_page_size     IN NUMBER,
    p_page_no       IN NUMBER,
    p_total_records OUT NUMBER,
    p_total_pages   OUT NUMBER,
    p_cursor        OUT SYS_REFCURSOR
) AS
    v_sql VARCHAR2(4000);
    v_count_sql VARCHAR2(4000);
    v_start NUMBER := (p_page_no - 1) * p_page_size + 1;
    v_end NUMBER := p_page_no * p_page_size;
    v_where VARCHAR2(2000) := NVL(p_where_clause, '1=1');
BEGIN
    -- 计算总记录数
    v_count_sql := 'SELECT COUNT(*) FROM ' || p_table_name || ' WHERE ' || v_where;
    EXECUTE IMMEDIATE v_count_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 || ' 
                    WHERE ' || v_where || '
                    ORDER BY ' || p_order_by || '
                ) a WHERE ROWNUM <= ' || v_end || '
              ) WHERE rn >= ' || v_start;

    OPEN p_cursor FOR v_sql;
END conditional_paging;

调用示例

DECLARE
    v_total_records NUMBER;
    v_total_pages NUMBER;
    v_cursor SYS_REFCURSOR;
    v_id NUMBER;
    v_name VARCHAR2(100);
BEGIN
    -- 调用存储过程
    paging_procedure(
        p_table_name => 'EMPLOYEES',
        p_page_size => 10,
        p_page_no => 2,
        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);

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

    CLOSE v_cursor;
END;

性能优化建议

1. 对于大数据表，确保排序列有索引
2. 避免在分页查询中使用SELECT *，只查询需要的列
3. 考虑使用物化视图或缓存结果集提高性能
4. 对于复杂查询，可以先创建临时表存储中间结果

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