Oracle存储过程分页实现方案

在Oracle数据库中实现分页功能通常有几种方法，下面我将介绍几种常见的存储过程分页实现方式。

方法一：使用ROWNUM实现分页

CREATE OR REPLACE PROCEDURE paging_procedure (
    p_table_name    IN VARCHAR2,
    p_page_size     IN NUMBER,
    p_page_no       IN NUMBER,
    p_order_by      IN VARCHAR2 DEFAULT NULL,
    p_where_clause  IN VARCHAR2 DEFAULT NULL,
    p_total_records 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_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    END IF;

    EXECUTE IMMEDIATE v_sql INTO p_total_records;

    -- 构建分页查询SQL
    v_sql := 'SELECT * FROM (
                SELECT a.*, ROWNUM rn FROM (
                    SELECT * FROM ' || p_table_name;

    IF p_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    END IF;

    IF p_order_by IS NOT NULL THEN
        v_sql := v_sql || ' ORDER BY ' || p_order_by;
    END IF;

    v_sql := v_sql || ') a WHERE ROWNUM <= ' || v_end_row || '
              ) WHERE rn >= ' || v_start_row;

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

方法二：使用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_order_by      IN VARCHAR2 DEFAULT NULL,
    p_where_clause  IN VARCHAR2 DEFAULT NULL,
    p_total_records 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;
    IF p_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    END IF;

    EXECUTE IMMEDIATE v_sql INTO p_total_records;

    -- 构建分页查询SQL(12c及以上版本)
    v_sql := 'SELECT * FROM ' || p_table_name;

    IF p_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    END IF;

    IF p_order_by IS NOT NULL THEN
        v_sql := v_sql || ' ORDER BY ' || p_order_by;
    END IF;

    v_sql := v_sql || ' OFFSET ' || v_offset || ' ROWS FETCH NEXT ' || p_page_size || ' ROWS ONLY';

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

方法三：使用分析函数ROW_NUMBER()

CREATE OR REPLACE PROCEDURE paging_with_row_number (
    p_table_name    IN VARCHAR2,
    p_page_size     IN NUMBER,
    p_page_no       IN NUMBER,
    p_order_by      IN VARCHAR2 DEFAULT NULL,
    p_where_clause  IN VARCHAR2 DEFAULT NULL,
    p_total_records 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_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    END IF;

    EXECUTE IMMEDIATE v_sql INTO p_total_records;

    -- 构建分页查询SQL
    v_sql := 'SELECT * FROM (
                SELECT t.*, ROW_NUMBER() OVER (ORDER BY ';

    IF p_order_by IS NOT NULL THEN
        v_sql := v_sql || p_order_by;
    ELSE
        v_sql := v_sql || '1'; -- 默认按第一列排序
    END IF;

    v_sql := v_sql || ') AS rn FROM ' || p_table_name || ' t';

    IF p_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    END IF;

    v_sql := v_sql || ') WHERE rn BETWEEN ' || v_start_row || ' AND ' || v_end_row;

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

调用示例

DECLARE
    v_total NUMBER;
    v_cursor SYS_REFCURSOR;
    v_empno employees.empno%TYPE;
    v_ename employees.ename%TYPE;
    -- 其他字段声明
BEGIN
    paging_procedure(
        p_table_name => 'employees',
        p_page_size => 10,
        p_page_no => 2,
        p_order_by => 'empno DESC',
        p_where_clause => 'deptno = 20',
        p_total_records => v_total,
        p_result_cursor => v_cursor
    );

    DBMS_OUTPUT.PUT_LINE('总记录数: ' || v_total);
    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. 性能考虑：对于大数据量表，确保ORDER BY字段有适当的索引
2. SQL注入：在实际应用中，应对输入参数进行验证和转义
3. 版本兼容性：OFFSET-FETCH语法需要Oracle 12c及以上版本
4. 参数验证：应添加对页码和每页大小的验证逻辑

以上存储过程可以根据实际需求进行调整和优化，例如添加更多的输入参数来控制查询条件或输出字段。