Oracle分页函数(存储过程)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
得即高歌失即休,多愁多恨亦悠悠。今朝有酒今朝醉,明日愁来明日愁