用oracle存储过程来做分页其实挺实用的一个方法，简单讲讲怎么写和调用吧

说到在Oracle数据库里处理大量数据,分页是个绕不开的话题，尤其是在开发网站或者管理系统的时候，我们经常遇到这种需求：查询结果有几千几万条，但用户界面上一次只能显示10条或20条，如果一次性把所有数据都从数据库取出来，再在程序里进行分页，那对网络传输和服务器内存都是巨大的浪费，速度也会慢得让人无法接受，最有效的方法就是把分页这个动作直接放在数据库层面完成，让数据库只返回我们需要的那一页数据，用Oracle的存储过程来实现这个功能，是一个非常经典且实用的做法。

具体该怎么写这么一个存储过程呢？别担心，我们一步一步来，用最直白的话说清楚。

一个完整的分页存储过程通常需要几个关键信息,也就是我们说的“入参”：

1. 你要查哪张表或者哪个查询结果：这个通常我们会用一个SQL查询语句的文本来表示，SELECT * FROM employees WHERE salary > 5000。
2. 当前是第几页：比如用户点了一下“下一页”，当前是第2页。
3. 每一页有多少条数据：比如我们规定一页显示10行。
4. 按哪个字段排序：比如按照员工ID升序，或者按照工资降序，这个很重要，因为排序不固定，分页结果就会乱套。

光有输入还不够,存储过程执行完了，我们总得拿到结果吧？所以它还需要“出参”：

1. 当前页的数据内容：这就是我们最终想要的东西，一个包含了10条（假设每页10条）数据的结果集。
2. 总的数据条数：这个是为了方便前端显示总页数，共100页”。

在Oracle里,存储过程不能直接返回一个像查询结果那样的表格，但它可以返回一个“游标”，你可以把游标想象成一个指针，程序拿到这个指针，就能像读取普通查询结果一样，一条一条地把数据读出来，当前页的数据我们就用一个“输出游标”参数来返回，总条数则用一个普通的数字类型的输出参数。

我们看看存储过程内部的核心逻辑是怎么样的,思路其实很清晰：

第一步，拼装核心的SQL语句。 我们不能直接执行传入的那个查询语句，因为它可能没有排序，而且数据量巨大，我们要把它包装一下，Oracle分页的一个黄金搭档就是 ROWNUM 这个伪列，它会给查询结果的每一行分配一个从1开始的序号。

一个经典的分页查询模板长这样：

SELECT * FROM (
    SELECT t.*, ROWNUM as rn FROM (
        -- 这里放你传入的原始SQL，并且必须要有明确的排序，ORDER BY id
        your_original_sql_here
    ) t WHERE ROWNUM <= page_end_index
) WHERE rn > page_start_index;

我来解释一下这个三层嵌套：

• 最内层：是你原本的查询，并加上确定的排序（ORDER BY employee_id），这是为了保证数据的顺序是稳定的。
• 中间层：给内层查询的结果加上 ROWNUM（我们给它起了个别名叫 rn），并且只取序号小于等于“本页结束序号”的数据。page_end_index 怎么算？很简单：当前页码 * 每页条数，比如第2页，每页10条，结束序号就是20。
• 最外层：从中间层的结果中，筛选出序号大于“本页开始序号”的数据。page_start_index (当前页码 - 1) * 每页条数，继续上面的例子，开始序号就是10，这样一筛，最终得到的就是第11条到第20条，正好是第二页的数据。

第二步，计算总条数。 这个就简单了，直接对传入的原始SQL语句外面套一个 SELECT COUNT(*) FROM (...) 就可以了。

第三步，把上面的逻辑用代码写出来。 我们把这些想法变成真正的存储过程代码，为了避免使用专业术语，我尽量用注释来解释每一行。

CREATE OR REPLACE PROCEDURE paging_procedure (
    -- 输入参数：原始SQL语句
    p_sql IN VARCHAR2,
    -- 输入参数：当前页码
    p_page_no IN NUMBER,
    -- 输入参数：每页记录数
    p_page_size IN NUMBER,
    -- 输出参数：返回分页数据的游标
    p_cursor OUT SYS_REFCURSOR,
    -- 输出参数：返回总记录数
    p_total_count OUT NUMBER
)
IS
    -- 定义变量：计算分页的开始行和结束行
    v_start NUMBER := (p_page_no - 1) * p_page_size + 1;
    v_end NUMBER := p_page_no * p_page_size;
    -- 定义变量：拼装最终分页查询的SQL语句
    v_paging_sql VARCHAR2(4000);
    -- 定义变量：拼装计算总条数的SQL语句
    v_count_sql VARCHAR2(4000);
BEGIN
    -- 【第一步】先计算总记录数
    -- 把传入的SQL包装成 `SELECT COUNT(*) FROM (原SQL)`
    v_count_sql := 'SELECT COUNT(*) FROM (' || p_sql || ')';
    -- 动态执行这个SQL语句，并把结果放入输出参数 p_total_count 中
    EXECUTE IMMEDIATE v_count_sql INTO p_total_count;
    -- 【第二步】拼装分页查询的SQL语句
    v_paging_sql := '
        SELECT *
        FROM (
            SELECT a.*, ROWNUM as rn
            FROM (' || p_sql || ') a
            WHERE ROWNUM <= ' || v_end || '
        )
        WHERE rn >= ' || v_start;
    -- 【第三步】打开游标，将拼装好的SQL语句的结果集赋给输出游标 p_cursor
    OPEN p_cursor FOR v_paging_sql;
END paging_procedure;
/

存储过程写好了,又该怎么调用它呢？也非常简单，我们可以在Oracle的SQL开发工具里（比如SQLPlus, SQL Developer）测试一下。

假设我们想对 employees 表进行分页，按工资降序排列，查看第2页，每页5条记录。

-- 声明几个变量来接收存储过程的返回结果
SET SERVEROUTPUT ON
DECLARE
    -- 定义一个游标变量来接收分页数据
    my_cursor SYS_REFCURSOR;
    -- 定义一个数字变量来接收总条数
    total_num NUMBER;
    -- 定义几个变量，用来存放从游标里取出的每一列数据（这里需要和employees表的字段对应）
    emp_id employees.employee_id%TYPE;
    emp_name employees.last_name%TYPE;
    emp_sal employees.salary%TYPE;
    -- 其他字段...
    current_row_number NUMBER := 0;
BEGIN
    -- 调用存储过程
    -- 第一个参数是SQL字符串，注意一定要有ORDER BY
    -- 第二个参数是页码
    -- 第三个参数是页大小
    -- 第四个是输出的游标
    -- 第五个是输出的总记录数
    paging_procedure(
        p_sql => 'SELECT employee_id, last_name, salary FROM employees ORDER BY salary DESC',
        p_page_no => 2,
        p_page_size => 5,
        p_cursor => my_cursor,
        p_total_count => total_num
    );
    -- 打印总记录数
    DBMS_OUTPUT.PUT_LINE('总记录数为： ' || total_num);
    -- 循环从游标中取出每一行数据并打印
    LOOP
        FETCH my_cursor INTO emp_id, emp_name, emp_sal;
        EXIT WHEN my_cursor%NOTFOUND;
        current_row_number := current_row_number + 1;
        DBMS_OUTPUT.PUT_LINE('行号：' || current_row_number || ', ID：' || emp_id || ', 姓名：' || emp_name || ', 工资：' || emp_sal);
    END LOOP;
    -- 关闭游标
    CLOSE my_cursor;
END;
/

当你执行这段调用代码后,在输出窗口就能看到总记录数，以及第二页的5条员工数据了。

在实际的Java、Python等应用程序中，调用方式也是类似的：使用数据库连接对象创建可调用语句，注册输入和输出参数（尤其是游标类型的输出参数），然后执行存储过程，最后从游标结果集中遍历数据即可。

用Oracle存储过程做分页的优势非常明显：高效、通用、安全，它最大限度地减少了数据库和应用程序之间的数据传输量，通过一套代码应对各种不同的分页查询需求，并且通过参数化拼接SQL，也在一定程度上提升了安全性，虽然现在很多成熟的框架（如MyBatis）也提供了分页插件，但理解这个最原生的数据库层面分页原理，对于处理超大规模数据和高并发场景，依然非常有价值。