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