一个关于Oracle分页的实现方案

前言：

在使用数据库进行项目的开发过程中，往往需要对查询结果进行分页处理，但由于每个数据库管理系统的实现不同，所使用的方法也不相同，本文展示了一个在使用Oracle数据库管理系统时的一个分页方案。

注意：

在此展现的是一种方法，并不是一个通用的分页存储过程。

环境：

OS：Microsoft® Windows Server 20003

DBMS：Oracle9i Enterprise Edition Release 9.2.0.3.0

数据表：

方案中假设数据库中存在如下的示例数据库：

Create table authors(

Au_id varchar2( 11 ) primary key ,

Au_lname varchar2( 40 ) not null ,

Au_fname varchar2( 20 ) not null ,

Phone varchar2( 12 ) not null ,

Address varchar2( 4 ) null ,

City varchar2( 20 ) null ,

Zip char( 6 ) null

)

存储过程：

CREATE OR REPLACE  PACKAGE DIVPAGE  as
/**//**//**//*******************************************************************
*
* 作者：Dorian Deng
* 创建日期：2006-05-05
* 说明：
* 　　用于记录总页数和当前页的最小和最大记录号
*
********************************************************************/
procedure getPageRownum
(
  recordCount in number ,
  pageSize in number ,
  pageIndex in number ,
  pageCount out number ,
  pageMinRownum out number ,
  pageMaxRownum out number
);
end;
/
CREATE OR REPLACE  PACKAGE BODY DIVPAGE  as
/**//**//**//*******************************************************************
*
* 作者：Dorian Deng
* 创建日期：2006-05-05
* 说明：
* 　　用于记录总页数和当前页的最小和最大记录号
*
********************************************************************/
procedure getPageRownum
(
  recordCount in number ,
  pageSize in number ,
  pageIndex in number ,
  pageCount out number ,
  pageMinRownum out number ,
  pageMaxRownum out number
)
as
begin
  pageCount := ceil( recordCount / pageSize ) ;
  pageMaxRownum := pageIndex * pageSize ;
  pageMinRownum := pageMaxRownum - pageSize + 1 ;
end getPageRownum;

end;

/

CREATE OR REPLACE  PACKAGE AUTHORSPACKAGE  as
/**//**//**//*******************************************************************
*
* 作者：Dorian Deng
* 创建日期：2006-05-05
* 说明：
* 　　用于记录总页数和当前页的最小和最大记录号
*
********************************************************************/
type t_cursor is ref cursor  ;
procedure SelectAuthors
(
  zip_in in char ,
  pageIndex in number ,
  pageSize in number ,
  pageCount out number ,
  AuthorsList out t_cursor
);

end AUTHORSPACKAGE ;

/ 

CREATE OR REPLACE  PACKAGE BODY  AUTHORSPACKAGE  as
/**//**//**//*******************************************************************
*
* 作者：Dorian Deng
* 创建日期：2006-05-05
* 说明：
* 　　用于记录总页数和当前页的最小和最大记录号
*
********************************************************************/
procedure SelectAuthors
(
zip_in in char ,           --查询的条件
  pageIndex in number ,           --页码，以1开始
  pageSize in number ,           --每页记录数
  pageCount out number ,           --总页数
  AuthorsList out t_cursor           --当前页记录值
)
as
recordCount number ;
pageMinRownum number ;
pageMaxRownum number ;
begin
  select count( * ) into recordCount from ( select Au_id from authors ) ;
  DIVPAGE.getPageRownum( recordCount , pageSize , pageIndex ,
      pageCount , pageMinRownum , pageMaxRownum ) ;
  open AuthorsList for
    select * from ( --本级查询得出排序的结果集的rownum
select rownum rn , Au_id  ,
          Au_lname  ,
          Au_fname ,
          Phone  ,
          Address  ,
          City  ,
          Zip
          from (
--本级查询按给定条件得出结果集，如果使用了order by，其rownum可能乱序，
--所以需要外一级的查询，同时直接使用zip_in参数，可防止SQL注入
select  Au_id  ,
Au_lname  ,  Au_fname ,  Phone  , Address  ,City  ,Zip
from authors where zip = zip_in order by au_lname ) )
          where rn between pageMinRownum and pageMaxRownum ;
end ;
 

end AUTHORSPACKAGE ;

参考：

1．一种分页的通用存储过程

create or replace package DotNet is

  -- Author  : good_hy

  -- Created : 2004-12-13 13:30:30

  -- Purpose :

  TYPE type_cur IS REF CURSOR;     --定义游标变量用于返回记录集   

  PROCEDURE DotNetPagination(     

  Pindex in number,                --分页索引  

  Psql in varchar2,                --产生dataset的sql语句

  Psize in number,                 --页面大小

  Pcount out number,               --返回分页总数

  v_cur out type_cur               --返回当前页数据记录

  );  

  procedure DotNetPageRecordsCount(

  Psqlcount in varchar2,           --产生dataset的sql语句                           

  Prcount   out number             --返回记录总数

  );

end DotNot;


create or replace package body DotNet is

--****************************************************************

PROCEDURE DotNetPagination(

  Pindex in number,

  Psql in varchar2,

  Psize in number, 

  Pcount out number,

  v_cur out type_cur

)

AS

  v_sql VARCHAR2(1000);

  v_count number; 

  v_Plow number;

  v_Phei number;

Begin

  ------------------------------------------------------------取分页总数

  v_sql := 'select count(*) from (' || Psql || ')';

  execute immediate v_sql into v_count;

  Pcount := ceil(v_count/Psize);

  ------------------------------------------------------------显示任意页内容

  v_Phei := Pindex * Psize + Psize;

  v_Plow := v_Phei - Psize + 1;

  --Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段

  v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;

  open v_cur for v_sql;

End DotNetPagination;

--********************************************************

procedure DotNetPageRecordsCount(

  Psqlcount in varchar2,

  Prcount   out number

  )

 as

   v_sql varchar2(1000);

   v_prcount number;

 begin

   v_sql := 'select count(*) from (' || Psqlcount || ')';

   execute immediate v_sql into v_prcount;

   Prcount := v_prcount;                  --返回记录总数

    end DotNetPageRecordsCount; 

--*************************************************************

end DotNot;