create or replace package body Get_RecordByPage is

StrSQL varchar2(2000);

--分页函数

procedure GetRecordByPage(tblName in varchar2,

FLDName in varchar2,

PageSize in number,

PageIndex in number,

strWhere in varchar2,

orderType in varchar2,

r_cur out myCur) is

pageMax number(10);

pageMin number(10);

begin

pageMin := (PageIndex - 1) * PageSize;

pageMax := PageIndex * PageSize;

StrSQL := 'select * from (select a.*,rownum recordRownum from';

StrSQL := StrSQL || '(select * from ' || tblName;

if (length(strWhere) > 0) then

begin

StrSQL := StrSQL || ' where ' || strWhere;

-- StrSQL := StrSQL || ' where :strwhere';

end;

end if;

StrSQL := StrSQL || ' order by ' || FLDName || ' ' || orderType;

StrSQL := StrSQL || ') a ) b';

StrSQL := StrSQL || ' where recordRownum<=' || pageMax ||' and recordRownum>' || pageMin;

open R_cur for StrSQL ;

end GetRecordByPage;

--计算总记录行数

procedure GetRecordCounts(tblName in varchar2,

strWhere in varchar2,

counts out number) is

begin

StrSQL := 'select count(*) from ' || tblName;

if (length(strWhere) > 0) then

begin

StrSQL := StrSQL || ' where ' || strWhere;

-- StrSQL := StrSQL || ' where :strwhere';

end;

end if;

execute immediate StrSQL

into counts ;

end GetRecordCounts;

end Get_RecordByPage;