一个关于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;