Oracle分页函数（存储过程）

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;

Package bodies

 

转载于:https://www.cnblogs.com/gotoschool/p/4777679.html