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的。
    1. 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脚本,待查。