create or replace package DotNet is
-- Author : good_hy
-- Created : 2004-12-13 13:30:30
-- Purpose :
TYPE type_cur IS REF CURSOR; --定義游標(biāo)變量用于返回記錄集
PROCEDURE DotNetPagination(
Pindex in number, --分頁(yè)索引
Psql in varchar2, --產(chǎn)生dataset的sql語(yǔ)句
Psize in number, --頁(yè)面大小
Pcount out number, --返回分頁(yè)總數(shù)
v_cur out type_cur --返回當(dāng)前頁(yè)數(shù)據(jù)記錄
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2, --產(chǎn)生dataset的sql語(yǔ)句
Prcount out number --返回記錄總數(shù)
);
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
------------------------------------------------------------取分頁(yè)總數(shù)
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
------------------------------------------------------------顯示任意頁(yè)內(nèi)容
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 (select rownum rn , t.* from (' || sqlstr || ') t) where rn between ' || v_Plow || ' and ' || v_Phei ;
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; --返回記錄總數(shù)
end DotNetPageRecordsCount;
--**************************************************************************************
end DotNot;