一個關於Oracle分頁的實現方案

著作權(c) 2007 Dorian Deng。署名 共用 非商用。

轉載請註明出處：http://www.cnblogs.com/doriandeng/archive/2007/10/05/914834.html

前言：

在使用資料庫進行項目的開發過程中，往往需要對查詢結果進行分頁處理，但由於每個資料庫管理系統的實現不同，所使用的方法也不相同，本文展示了一個在使用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;