快速分页存储过程

create PROCEDURE DotNetPagination --完成分页功能
(
Pindex in number, --分页索引
Psql in varchar2, --产生dataset的sql语句
Psize in number, --页面大小
Pcount out number, --返回分页总数
v_cur out type_cur --返回当前页数据记录
);
----------------------------------------------------------------------------
procedure DotNetPageRecordsCount --返回sql的记录数量
(
Psqlcount in varchar2, --产生dataset的sql语句
Prcount out number --返回记录总数
);

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;

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

v_sql:= 'select * from (select rownum rnm, a.* from ('||Psql ||') a where rownum <='|| v_Phei||' ) where rnm >='|| v_Plow ;


open v_cur for v_sql;

End DotNetPagination;