Oracle分页必须三层嵌套：最内层完成ORDER BY确保排序正确，中间层用ROWNUM≤上限获取前N行，最外层用别名过滤下限；因ROWNUM赋值在WHERE和ORDER BY之前且不支持>比较，直接写BETWEEN或>会导致空结果或错乱。
oracle 存储过程中用 rownum 实现分页，必须用三层嵌套子查询，且排序必须在最内层完成；否则分页结果错乱、跳行或重复。
为什么不能直接在 WHERE 中写 ROWNUM BETWEEN x AND y
因为 ROWNUM 是在结果集生成时动态赋值的，且赋值发生在 WHERE 过滤之后、ORDER BY 之前。如果你写 WHERE ROWNUM > 5，Oracle 会先取第 1 行、赋 ROWNUM = 1，发现不满足 >5 就丢弃；再取第 2 行、赋 ROWNUM = 1（注意：不是 2），又丢弃……最终永远查不到数据。
常见错误现象：
-
SELECT * FROM emp WHERE ROWNUM > 3→ 返回空 SELECT * FROM emp ORDER BY sal DESC WHERE ROWNUM → 排序无效，取的是原始顺序前 10 行
标准三层结构怎么写（带排序）
核心逻辑：最内层排序 → 中间层用 ROWNUM 截断上界 → 外层用别名过滤下界。
假设要查第 2 页、每页 10 条，按 id 升序：
SELECT * FROM (
SELECT /*+ FIRST_ROWS(10) */ t.*, ROWNUM rn FROM (
SELECT * FROM your_table ORDER BY id
) t WHERE ROWNUM 10
说明：
-
/*+ FIRST_ROWS(10) */是可选 hint，提示优化器优先返回前 N 行，对大表分页有性能帮助 - 中间层的
WHERE ROWNUM 必须存在，否则外层rn > 10无法生效（ROWNUM不会在未限制时全量生成） - 外层的
rn是别名，不是原始ROWNUM，所以能安全用于>判断
存储过程里怎么拼 SQL 和传参
不能直接把 page_index、page_size 拼进字符串再 EXECUTE IMMEDIATE —— 容易 SQL 注入，且 Oracle 对绑定变量支持良好，应优先使用。
正确做法（PL/SQL 块示例）：
DECLARE
v_sql VARCHAR2(4000);
v_page PLS_INTEGER := 2;
v_size PLS_INTEGER := 10;
v_start PLS_INTEGER := (v_page - 1) * v_size + 1;
v_end PLS_INTEGER := v_page * v_size;
BEGIN
v_sql := 'SELECT * FROM (
SELECT t.*, ROWNUM rn FROM (
SELECT * FROM your_table ORDER BY id
) t WHERE ROWNUM :start';
<p>-- 执行时绑定变量，不是字符串拼接
EXECUTE IMMEDIATE v_sql USING v_end, v_start;
END;</p>
关键点：
- 计算
v_start和v_end时注意：第 1 页是(1-1)*10+1 = 1到1*10 = 10，别写成0~9 - 中间层用
:end绑定，外层用:start绑定，避免硬编码和注入风险 - 如果表很大、排序字段无索引，
ORDER BY会严重拖慢分页，务必确认该字段有索引
为什么不用 ROW_NUMBER()？它不更直观吗
可以，但要注意版本和性能差异：
-
ROW_NUMBER() OVER (ORDER BY ...)从 Oracle 9i 就支持，语法更接近其他数据库，也支持窗口函数的灵活排序 - 但它的执行计划通常比三层
ROWNUM多一次全排序（尤其没索引时），对超大数据集分页，ROWNUM方式往往更快 - 如果业务已用
ROW_NUMBER()且稳定，无需强行切换；但新写存储过程、且数据量 > 百万级时，建议压测对比两种方式的consistent gets和响应时间
真正容易被忽略的是：无论用哪种方式，**排序字段必须有索引，且分页越靠后（比如第 1000 页），性能衰减越明显**——这不是写法问题，是 Oracle 分页本身的物理限制。
