如何使用Oracle PL/SQL编写通用分页查询存储过程模版？-Oracle-PHP中文网

oracle中ref cursor类型必须在package中预先声明，否则会报pls-00201错误；包仅需创建一次且只需声明类型，不需实现逻辑；ref cursor为弱类型游标，适用于动态sql场景。

为什么必须先建包再写存储过程

oracle 的 ref cursor 类型不能在存储过程中直接定义为本地类型，必须通过 package 提前声明。否则会报错：pls-00201: identifier 'paging_cursor' must be declared。
实际操作中，包只需创建一次，且命名要唯一（比如 page_pkg），后续所有分页过程都复用它：

create or replace package page_pkg as
  type paging_cursor is ref cursor;
end page_pkg;

注意：包体不需要实现逻辑，只声明类型即可；REF CURSOR 是弱类型游标，不绑定具体字段结构，适合动态 SQL 场景。

如何拼接安全、可排序、带条件的分页 SQL

直接字符串拼接 tableName 或 orderField 极易引发 SQL 注入，但 Oracle PL/SQL 没有参数化表名/列名的支持，只能靠白名单校验或系统视图验证。实际开发中更稳妥的做法是限制输入范围：

p_TableName 应从 ALL_TABLES 中查证存在，且属于当前 schema
p_Orderby 若允许动态传入，需提前拆解为字段名 + 方向（如 'EMPNO DESC'），并用正则过滤非法字符（如分号、括号）
p_Where 同样需检查是否含 SELECT、UNION 等关键字，或改用绑定变量方式——但 WHERE 条件本身无法参数化，只能靠应用层预处理

典型分页主查询模板（兼容 Oracle 11g+）：

v_sql := 'select * from (select t.*, rownum rn from (' ||
          'select ' || p_Fields || ' from ' || p_TableName ||
          CASE WHEN p_Where IS NOT NULL THEN ' where ' || p_Where ELSE '' END ||
          CASE WHEN p_Orderby IS NOT NULL THEN ' order by ' || p_Orderby ELSE '' END ||
          ') t where rownum  ' || v_begin;

为什么总记录数和分页结果要分开查

不能用 COUNT(*) OVER() 或子查询嵌套统计，因为 ROWNUM 过滤发生在排序之后，而分页 SQL 中的 ROWNUM 绑定的是内层结果集行号，不是原始全量数据的序号。强行合并会导致：

QuantOracle

63个确定性量化金融计算器 + 10个通过MCP的复合工作流。期权定价、Greeks、奇异衍生品、风险指标、投资组合优化……

下载

统计值不准（例如 WHERE 条件未生效于 COUNT）
性能严重下降（全表扫描 + 排序 + 窗口函数）
Oracle 11g 及以前版本对复杂嵌套支持不稳定

所以必须独立执行计数 SQL：

v_count_sql := 'select count(*) from ' || p_TableName;
IF p_Where IS NOT NULL THEN
  v_count_sql := v_count_sql || ' where ' || p_Where;
END IF;
EXECUTE IMMEDIATE v_count_sql INTO p_rowcount;

注意：EXECUTE IMMEDIATE 不支持绑定变量用于表名/字段名，这是硬伤，别试图绕过。