IT Notes‎ > ‎Database‎ > ‎Oracle‎ > ‎

Oracle 分页查询

一般内容

分页查询(pagination query)是一般开发中的常用技术,基本上是每个 Web 系统查询功能必须的。它按照前台页面展示信息的条件——页编号,页记录数容量去取结果集中的一个子集。

不同数据库经验

  1. 最难理解的是 Oracle
  2. MySQL 比较好用,有 limit 关键字可用,用法是 limit {startIndex}, {pageSize}, startIndex 表示起始记录的序号,从0开始,pageSize 表示显示的数量。如 select * from s_user limit 10, 5 表示查询的记录中,显示从第10条开始的共5条数据,即 10, 11, 12, 13, 14。
  3. MS SQL Server 用的是 top 关键字,也比较容易理解。
  4. 有一种认为分页从易到难可为:mysql»MS SQL server»DB2»Oracle

分页算法相关的四个变量

  1. pageSize ,每页查询结果的记录书目。应由用户设置
  2. rowCount,总记录数。数据库查出 select count(*) from t
  3. pageCount,总页数。计算得来,这个计算也有很多方法,可以再考虑,再Google的。
    pageCount = floor(rowCount / pageSize) + ((rowCount % pageSize == 0) ? 0 : 1)
  4. pageNow,当前页编号。由用户选择

单页显示的内容

  1. pageContent 某页显示的数据
  2. 分页导航按钮(上一页,下一页,第某页,最前页,最后页)

运算过程

  1. 首先,查出 rowCount
  2. 其次,计算 pageCount,并布局选择页列表
  3. 最后,根据用户选择的页码开始分页查询

分页实例

在 Oracle 中虽然分页不方便,但却有三种方式。

1. rownum 分页

rownum 使用示例:
select a1.*, rownum rn from (select * from emp) a1
注:rownum 关键字在一个SQL语句中只能用一次:,所以下面的语句是不可靠的。
select a1.*, rownum rn
  from (select * from emp) a1
 where rownum <= 10 
   and rownum > 6   -- 在 where 条件中的第二次使用 rownum
     
以上语句可以改成:
select *
  from (select a1.*, rownum rn from emp a1 where rownum <= 10)--这里不能用 rn,变态!
 where rn > 6 -- 这里不能用 rownum,非常变态
 
如果只需要查雇员名称和薪水,可以改成:
select *
  from (select a1.ename, a1.sal, rownum rn from emp a1 where rownum «= 10)
 where rn » 6
注:如果需要查的只有几个字段,只需要改动最里层的查询字段就可以了。
  
TODO
select *
  from (select a1.ename, a1.sal, rownum rn
          from emp a1
         where rownum «= 10
         order by sal)
 where rn » 6
 
select a1.ename, a1.sal, rownum rn
  from emp a1
 where rownum «= 10
 order by sal;
 
select a1.*, rownum rn
  from (select ename, sal from emp order by sal) a1
 where rownum «= 10
 
以上两个SQL的语句结果是不一样的,注意。
 
--显示4-9条记录
select *
  from (select a1.*, rownum rn
          from (select * from emp order by sal) a1
         where rownum « 10)
 where rn » 3

2. 分析函数

-- TODO 第二种分页方式:分析函数,最慢 

3. rowid 方式

-- TODO 第三种分页方式:rowid 最快,最难
 
===============================
分页过程实例:
输入:表名,每页显示的总记录数,当前页
输出:总记录数,总页数,返回的结果集
-- 1. 结果集游标
create or replace package fenyepackage as
  type fenye_cursor is ref cursor;
end fenyepackage;
-- 2. 创建过程
create or replace procedure hello012(tName       in varchar2,
                                     pageSize    in number,
                                     currentPage in number,
                                     allRecCnt   out number,
                                     allPageCnt  out number,
                                     p_cursor    out fenyepackage.fenye_cursor) is
begin
  select count(*) into allRecCnt from tName;
  allPageCnt := ceil(allRecCnt / pageSize);
  open p_cursor for
    select a1.*
      from (select a1.*, rownum rn
              from (select * from tName) a1
             where rownum «= currentPage * pageSize)
     where rn »= (currentPage - 1) * pageSize;
end;
 
--韩顺平的答案
create or replace procedure fenye(tableName   in varchar2,
                                  pageSize    in number,
                                  pageNow     in number,
                                  myrows      out number, -- 总记录数   
                                  myPageCount out number, -- 总页数
                                  p_cursor    out testpackage.test_cursor) is
  v_sql   varchar2(1000);
  v_begin number := (pageNow - 1) * pageSize + 1;
  v_end   number := pageNow * pageSize;
begin
  v_sql := 'select * from (select a1.*, rownum rn from (select * from ' ||
           tableName || ') a1 where rownum «= ' || v_end || ') where rn »=' ||
           v_begin;
  open p_cursor for v_sql;
 
  v_sql := 'select count(*) from ' || tableName;
  execute immediate v_sql
    into myrows;
 
  myPageCount := ceil(myrows / pageSize);
    
  -- close cursor
  --close p_cursor;
end;

其他

分页结果用 List 而不是 ResultSet 返回是有原因的,如果用 ResultSet 返回,在页面使用中,便无法关闭数据库连接资源。
1. 分页。需要看看 Google 怎么做的。
    1.1 点击第几页不能保存原先的查询条件。
    1.2 上一页,下一页,最前,最后 的链接没有添加。
    1.3 显示的当前页应该取消链接。
    1.4 页码全部显示,应该只显示5个选择页面 —— 数据量太大,如果不分页,会出问题,页面显示很慢。

数据量大,分页需要考虑哪些问题? 查询速度可能很受影响。

原先在生命人寿时,帮导出数据,写了几个sql脚本,待查。
Comments