oracle分页必须用三层rownum嵌套：内层排序保序，中间层rownum≤max截断，外层rn>min筛选；直接where rownum between会因赋值早于order by而失效。
直接用 ROWNUM 嵌套加动态拼 SQL 是可行的，但“优雅”取决于三点：条件可空不崩、排序稳定不乱序、分页边界不越界。Oracle 12c+ 推荐用 OFFSET/FETCH，但如果你还在用 11g 或更早版本，必须靠子查询 + ROWNUM，且不能省略内层排序。
为什么不能在主查询里直接写 ROWNUM BETWEEN x AND y
因为 ROWNUM 是结果集生成时按顺序分配的虚拟列，它在 WHERE 过滤和 ORDER BY 之前就已赋值。如果你写成：
SELECT * FROM t WHERE ROWNUM BETWEEN 11 AND 20 ORDER BY id DESC
——实际只会返回前 20 行中满足 WHERE 的部分，再取其中第 11–20 个（极大概率为空）。真正生效的写法必须是三层嵌套：
- 最内层：带完整
ORDER BY的原始查询（保证顺序） - 中间层：用
ROWNUM标记行号，并限制上限（WHERE ROWNUM ） - 最外层：筛选行号下限（
WHERE r >= start）
漏掉任意一层，分页结果就可能错位或重复。
多条件动态拼接时怎么避免 SQL 注入和语法错误
所有用户输入（如 p_where、p_order）都必须做白名单校验或绑定变量处理。但 Oracle 存储过程中无法对 ORDER BY 列名或 WHERE 字段名使用绑定变量，只能拼接 —— 所以必须严格过滤。
- 用正则校验字段名：
REGEXP_LIKE(p_order_column, '^[a-zA-Z_][a-zA-Z0-9_]*$') - 把排序方向限定为
'ASC'或'DESC'，别接受'ASC NULLS LAST'这类扩展写法（除非你明确支持） - 字符串值要用
DBMS_ASSERT.ENQUOTE_LITERAL()包裹，比如：'''' || DBMS_ASSERT.ENQUOTE_LITERAL(p_keyword) || '''' - 空条件要跳过，不要拼出
WHERE 1=1 AND后跟空字符串
示例片段：
v_sql := 'SELECT * FROM (SELECT A.*, ROWNUM r FROM (' ||
'SELECT * FROM my_table WHERE 1=1';
IF p_name IS NOT NULL THEN
v_sql := v_sql || ' AND name LIKE ' || DBMS_ASSERT.ENQUOTE_LITERAL('%' || p_name || '%');
END IF;
IF p_status IS NOT NULL THEN
v_sql := v_sql || ' AND status = ' || p_status; -- 数值型可直传
END IF;
v_sql := v_sql || ' ORDER BY ' || p_order_col || ' ' || UPPER(p_order_dir) ||
') A WHERE ROWNUM = ' || v_start_row;
分页参数校验和越界保护必须做在 SQL 执行前
Oracle 不会自动帮你截断超大页码或负页大小，全靠存储过程自己兜底，否则可能查出空结果、报 ORA-00936（缺少表达式），甚至拖垮性能（比如 page_no = 9999999）。
-
p_page_size必须 > 0，建议上限设为 500（防恶意拉取） -
p_page_no小于 1 → 强制设为 1；大于计算出的总页数 → 设为总页数（需先查COUNT(*)） - 总记录数查询必须复用相同
WHERE条件，不能漏掉任何动态条件，否则p_total_pages算错 - 如果总记录数为 0，跳过分页查询，直接 OPEN 空游标
容易被忽略的是：COUNT(*) 查询和分页主查询的 WHERE 条件必须完全一致（包括大小写、空格、括号），否则分页和总数对不上。
Oracle 12c+ 应该优先用 OFFSET/FETCH，但注意兼容性陷阱
如果数据库版本 ≥ 12.1，用标准 SQL 分页更清晰、不易出错：
SELECT * FROM my_table WHERE status = :p_status ORDER BY id DESC OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
但要注意：
-
OFFSET必须是非负整数，不能传负值，否则报 ORA-00933 -
FETCH后不能跟变量名（如FETCH NEXT v_limit ROWS），必须用绑定变量（调用时传入） - 存储过程中执行动态 SQL 时，
OFFSET/FETCH仍需拼接，但无需三层嵌套，逻辑干净得多 - 如果应用连接的是 11g 客户端（如旧版 ODP.NET），即使服务端是 12c，也可能因协议限制不识别新语法
所以真实项目中，版本探测 + 分支逻辑仍是必要的，不能只靠一个存储过程通吃。
