Oracle 分页方法研究

• 1、Oracle 中的三大分页方法      
  • 1.1、通过分析函数分页
  • 1.2、通过 ROWNUM 分页
  • 1.3、通过 ROWID 分页
• 2、Oracle 分页解决方案浅析      
  • 2.1、纯后端代码完成分页
  • 2.2、通过存储过程来分页
  • 2.3、两个通用的分页存储过程
• 3、总结

1、Oracle 中的三大分页方法

本人最近总结了一下 Oracle 中的分页写法，从纯粹的 SQL 写法上来看，所谓分页就是嵌套子查询，无非就是不同的分页方法嵌套的子查询层数不同而已。Oracle 中一共有三种分页写法，分别是：嵌套一层子查询的分析函数分页、嵌套两层子查询的 ROWNUM 分页和嵌套三层子查询的 ROWID 分页。

1.1、通过分析函数分页

按员工年龄排序，每页显示 3 个员工，取第 1 页的数据。只嵌套一层子查询，写法简洁，容易理解，但一般没人用这种方法。只需要在子查询中的分析函数内部排序即可实现排序功能。

SELECT t2.staff_name,t2.birthday FROM( SELECT t1.staff_name,t1.birthday,ROW_NUMBER() OVER(ORDER BY t1.birthday) rn FROM demo.t_staff t1
) t2 WHERE t2.rn >= ((1-1)*3+1) AND t2.rn <= (1*3);

1.2、通过 ROWNUM 分页

按员工年龄排序，每页显示 3 个员工，取第 1 页的数据。嵌套两层子查询，写法比较灵活，一般都是用这种方法。只需要在子查询内部排序即可实现排序功能。

SELECT t3.staff_name,t3.birthday FROM( SELECT t2.*,ROWNUM rn FROM( SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
  ) t2 WHERE ROWNUM <= (1*3)
) t3 WHERE t3.rn >= ((1-1)*3+1);

通过 ROWNUM 分页的一种变通写法（相对来说更好理解）：

SELECT t3.staff_name,t3.birthday FROM( SELECT t2.*,ROWNUM rn FROM( SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
  ) t2
) t3 WHERE t3.rn >= ((1-1)*3+1) AND t3.rn <= (1*3);

1.3、通过 ROWID 分页

按员工年龄排序，每页显示 3 个员工，取第 1 页的数据。写法复杂，不太灵活，不易理解，很少有人用这种方法。必须在最内层子查询和最外层查询中都排序才可实现排序功能。

SELECT t4.staff_name,t4.birthday FROM demo.t_staff t4 WHERE t4.ROWID IN( SELECT t3.rid FROM( SELECT t2.rid,ROWNUM rn FROM( SELECT t1.ROWID rid FROM demo.t_staff t1 ORDER BY t1.birthday
    ) t2 WHERE ROWNUM <= (1*3)
  ) t3 WHERE t3.rn >= ((1-1)*3+1)
) ORDER BY t4.birthday;

2、Oracle 分页解决方案浅析

Oracle 中的三大分页方法应用最广泛的还是第二种，也就是基于 ROWNUM 的分页方法。由于实现分页的语法是固定的，所以一般项目中都是会提供一个公用的分页模版方法，然后其它需要分页的业务方法再调用这个方法来完成分页功能的。

分页的实现过程就是拼接 SQL 语句的过程，但选择在那个地方来完成拼接也是有讲究的。一般来说在服务端拼接是一个比较好的选择，这种方案主要好处就是灵活、简单、易维护。另一种比较常见的做法是通过存储过程来分页，然后在服务端调用存储过程，这种方案理论上分页效率比较高，但实现过程相对复杂，也没有纯服务端代码那么好维护。

2.1、纯后端代码完成分页

纯后端代码完成分页在定义、调用、性能、理解、维护等方面有不少小的技巧值得推敲。前几天我结合自己这些年来的分页经验和一个在公司干了十多年的技术专家交流了这个问题，最终我们一致认为还是传递整个内层子查询的方式最好（主要是可以规避掉一大堆小的坑）。拼接格式如下：

SELECT t3.* FROM(
  SELECT t2.*,ROWNUM rn FROM( :subquery ) t2 WHERE ROWNUM <= (:pageIndex*:pageSize)
) t3 WHERE t3.rn >= ((:pageIndex-1)*:pageSize+1)

我们以前都有尝试过将子查询分拆成多个部分，然后分别传递的方式，不过一旦项目深入之后问题总比想象的要多得多。譬如参数过多导致调用难度增加，为了实现分页不得不将写好的整条语句拆成几个部分多余浪费时间，出问题时调试的复杂度也增加了，多表分页也相对难以处理，经验不足的程序员常常没耐心看懂现有代码进而又捏造了一个所谓的改进版（事实上这种情况还很多）……

不过即便是整个子查询传进来，也仍然会有不同的处理方式。譬如我上文提到的那个专家说他们就曾尝试过把传递进来子查询切分成多个部分再重新组合，但后来发现复杂的子查询极难写对，徒增了团队里新人的挫败感……

外层查询中的那个星号是比较关键的一点，尽管我们都知道查询中出现星号往往是不好的，但分页时依然拘泥这一点的话，必然会到导致复杂的拼接。复杂的拼接往往不好写，调用时也容易出错，时不时还得回头去看内部的实现再推导出该如何调用，这个过程显然是比较浪费时间的。

2.2、通过存储过程来分页

我本人大部分时候还是通过存储过程来实现分页的，不过对很多人来说写存储过程甚至调用存储过程都是比较难的，我觉得主要原因还是因为相关知识点不熟、写的少。下面列出了写分页存储过程和调用存储过程的相关参考连接：

• 《.Net程序员学用Oracle系列(7)：视图、函数、存储过程、包》：存储过程
• 《.Net程序员学用Oracle系列(26)：PLSQL 之类型、变量和结构》：变量
• 《.Net程序员学用Oracle系列(27)：PLSQL 之游标、异常和事务》：游标
• 《.Net程序员学用Oracle系列(16)：访问数据库(ODP.NET)》：甲骨文提供的驱动

下面是一个调用 Oracle 分页存储过程的 C# 方法：

/// <summary> /// 调用存储过程，执行分页 /// </summary> /// <param name="tableName">表名</param> /// <param name="queryFields">查询（字段）列表</param> /// <param name="queryWhere">查询条件</param> /// <param name="orderBy">排序子句</param> /// <param name="pageIndex">页索引（页码）</param> /// <param name="pageSize">页大小（每页数据条数）</param> /// <param name="pageCount">总页数</param> /// <param name="rowCount">总行数</param> /// <param name="resultSet">结果集</param> public void ExecutePaging( string tableName, string queryFields, string queryWhere, string orderBy, int pageIndex, int pageSize, ref int pageCount, ref int rowCount, ref DataTable resultSet) {
    OracleParameter[] ps = { new OracleParameter(":tableName", OracleDbType.Varchar2, 1000), new OracleParameter(":queryFields", OracleDbType.Varchar2, 1000), new OracleParameter(":queryWhere", OracleDbType.Varchar2, 2000), new OracleParameter(":orderBy", OracleDbType.Varchar2, 200), new OracleParameter(":pageIndex", OracleDbType.Int32), new OracleParameter(":pageSize", OracleDbType.Int32), new OracleParameter(":pageCount", OracleDbType.Int32), new OracleParameter(":rowCount", OracleDbType.Int32), new OracleParameter(":resultSet", OracleDbType.RefCursor)
    };

    ps[0].Value = tableName;
    ps[1].Value = queryFields;
    ps[2].Value = queryWhere;
    ps[3].Value = orderBy;
    ps[4].Value = pageIndex;
    ps[5].Value = pageSize;
    ps[6].Direction = ParameterDirection.Output;
    ps[7].Direction = ParameterDirection.Output;
    ps[8].Direction = ParameterDirection.Output;

    resultSet = OracleHelper.ProcQuery("sp_dynamic_paging", ps); // 调用存储过程 pageCount = Verifier.VerifyInt(ps[6].Value);
    rowCount = Verifier.VerifyInt(ps[7].Value);
}

2.3、两个通用的分页存储过程

下面这个存储过程是从我曾负责过的一个项目中抽取出来的，也是我第一次尝试写存储过程分页，100%原创，中间改版过几次，为方便阅读注释内容已被我去掉，现在的这个版本中的i_queryFields参数是不接受星号的：

CREATE OR REPLACE PROCEDURE sp_paging(
  i_tableName VARCHAR2, -- 表名
  i_queryFields VARCHAR2, -- 查询（字段）列表
  i_queryWhere VARCHAR2, -- 查询条件
  i_orderBy VARCHAR2, -- 排序子句
  i_pageIndex NUMBER, -- 当前页索引
  i_pageSize NUMBER, -- 页大小
  o_rowCount OUT NUMBER, -- 总行数
  o_pageCount OUT NUMBER, -- 总页数
  o_resultSet OUT SYS_REFCURSOR -- 结果集
) IS
  v_count_sql VARCHAR2(2000);
  v_select_sql VARCHAR2(4000); BEGIN -- 拼接查询总行数的语句
    v_count_sql := 'SELECT COUNT(1) FROM '||i_tableName;

    -- 拼接查询条件 IF i_queryWhere IS NOT NULL THEN
      v_count_sql := v_count_sql||' WHERE 1=1 '||i_queryWhere; END IF;

    -- 计算总行数
    EXECUTE IMMEDIATE v_count_sql INTO o_rowCount;
    --DBMS_OUTPUT.PUT_LINE(v_count_sql||';');

    -- 计算总页数（CEIL 向上取整）
    o_pageCount := CEIL(o_rowCount / i_pageSize);

    -- 如果有记录，且当前页索引合法，则继续查询 IF o_rowCount >= 1 AND i_pageIndex >= 1 AND i_pageIndex <= o_pageCount THEN

      -- 当记录总数小于或等于页大小时，查询所有记录 IF o_rowCount <= i_pageSize THEN
        v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;

      -- 查询第一页
      ELSIF i_pageIndex = 1 THEN
        v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;

        v_select_sql := 'SELECT '||i_queryFields||' FROM('||v_select_sql||') WHERE ROWNUM<='||i_pageSize;

      -- 查询指定页 ELSE v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;

        v_select_sql := 'SELECT '||i_queryFields||' FROM(SELECT ROWNUM rn,'||i_queryFields||' FROM('||v_select_sql
          ||')) WHERE rn>'||((i_pageIndex-1)*i_pageSize)||' AND rn<='||(i_pageIndex*i_pageSize); END IF;

      --DBMS_OUTPUT.PUT_LINE(v_select_sql||';');
      OPEN o_resultSet FOR v_select_sql; ELSE OPEN o_resultSet FOR 'SELECT * FROM '||i_tableName||' WHERE 1!=1'; END IF; END;

下面这个存储过程摘自《剑破冰山——Oracle开发艺术》一书，有删改：

CREATE OR REPLACE PROCEDURE sp_dynamic_paging(
  i_tableName VARCHAR2, -- 表名
  i_queryFields VARCHAR2, -- 查询列表
  i_queryWhere VARCHAR2, -- 查询条件
  i_orderBy VARCHAR2, -- 排序
  i_pageSize NUMBER, -- 页大小
  i_pageIndex NUMBER, -- 页索引
  o_rowCount OUT NUMBER, -- 返回总条数
  o_pageCount OUT NUMBER, -- 返回总页数
  o_resultSet OUT SYS_REFCURSOR -- 返回分页结果集
) IS v_startRows INT; -- 开始行
  v_endRows INT; -- 结束行 
  v_pageSize INT;
  v_pageIndex INT;
  v_queryFields VARCHAR2(2000);
  v_queryWhere VARCHAR2(2000);
  v_orderBy VARCHAR2(200);
  v_count_sql VARCHAR2(1000); -- 接收统计数据条数的 SQL 语句
  v_select_sql VARCHAR2(4000); -- 接收查询分页数据的 SQL 语句
BEGIN
  -- 如果没有表名，则直接返回异常消息
  -- 如果没有字段，则表示查询全部字段 IF i_queryFields IS NOT NULL THEN v_queryFields:=i_queryFields; ELSE v_queryFields:=' * '; END IF;

  -- 可以没有查询条件 IF i_queryWhere IS NOT NULL THEN v_queryWhere := ' WHERE 1=1 AND'||i_queryWhere||' '; ELSE v_queryWhere := ' WHERE 1=1 '; END IF;

  -- 可以没有排序条件 IF i_orderBy IS NULL THEN v_orderBy:=' '; ELSE v_orderBy:='ORDER BY '||i_orderBy; END IF;

  -- 如果未指定查询页，则默认为首页 IF i_pageIndex IS NULL OR i_pageIndex<1 THEN v_pageIndex:=1; ELSE v_pageIndex:=i_pageIndex; END IF;

  -- 如果未指定每页记录数，则默认为 10 条 IF i_pageSize IS NULL THEN v_pageSize:=10; ELSE v_pageSize:=i_pageSize; END IF;

  -- 构造查询总条数的语句
  v_count_sql:='SELECT COUNT(1) FROM '||i_tableName||v_queryWhere; --DBMS_OUTPUT.PUT_LINE(v_count_sql||';'); -- 构造查询数据的语句
  v_select_sql:='(SELECT '||v_queryFields||' FROM '||i_tableName||v_queryWhere||v_orderBy||') t2'; -- 查询总条数
  EXECUTE IMMEDIATE v_count_sql INTO o_rowCount;

  -- 得到总页数 IF MOD(o_rowCount,i_pageSize)=0 THEN o_pageCount:=o_rowCount/i_pageSize; ELSE o_pageCount:=FLOOR(o_rowCount/i_pageSize)+1; END IF;

  -- 如果当前页大于最大页数，则取最大页数 IF i_pageIndex>o_pageCount THEN v_pageIndex:=o_pageCount; END IF;

  -- 设置开始结束的记录数
  v_startRows:=(v_pageIndex-1)*v_pageSize+1;
  v_endRows:=v_pageIndex*v_pageSize;

  -- 进行完成的动态 SQL 语句拼接
  v_select_sql:='SELECT t3.* FROM'||'(SELECT t2.*,ROWNUM rn FROM'||v_select_sql ||' WHERE ROWNUM<='||v_endRows||') t3 WHERE t3.rn>='||v_startRows; --DBMS_OUTPUT.PUT_LINE(v_select_sql||';'); OPEN o_resultSet FOR v_select_sql; END;

下面这段 PL/SQL 代码用于测试上面两个存储过程：

DECLARE v_tableName VARCHAR2(1000);
  v_queryFields VARCHAR2(1000);
  v_queryWhere VARCHAR2(1000);
  v_orderBy VARCHAR2(200);
  v_pageSize INT := 3;
  v_pageIndex INT;
  v_rowCount INT := 0;
  v_pageCount INT := 0;
  v_resultSet SYS_REFCURSOR;
BEGIN
  v_tableName:='t_staff';
  v_queryFields:='staff_name,birthday';
  v_orderBy:='birthday';
  v_pageIndex:=1;

  sp_dynamic_paging(
    i_tableName => v_tableName,
    i_queryFields => v_queryFields,
    i_queryWhere => v_queryWhere,
    i_orderBy => v_orderBy,
    i_pageSize => v_pageSize,
    i_pageIndex => v_pageIndex,
    o_rowCount => v_rowCount,
    o_pageCount => v_pageCount,
    o_resultSet => v_resultSet
  );
END;

3、总结

本文主要讲述了 Oracle 中的三种分页方法和常见的两种分页解决方案，并给出了两个通用的分页存储过程源码。主要是对我个人所掌握的 Oracle 分页方法和技术做了个全面的回顾。