ORACLE10G 分页存储过程

CREATE OR REPLACE
PROCEDURE PAGING (TableName IN VARCHAR,Fields IN VARCHAR DEFAULT '*',OrderField IN VARCHAR,sqlWhere VARCHAR,pageSize IN
INT,pageIndex IN INT DEFAULT 1,totalPage OUT INT,totalRecord OUT INT,RS OUT RESULTSET.RS)
AS
tsql varchar(4000);
doPageIndex int:=pageIndex;--当前页码
StartRecord int;--开始点
EndRecord int;--结束点
begin
--commit; --开始事务
--计算总记录数
If (sqlWhere is NULL) Then
tsql:='Select Count(*) as totalRecord From '||TableName;
Else
tsql:='Select Count(*) as totalRecord From '||TableName||' Where '||sqlWhere;
End If;
execute immediate tsql into totalRecord;
--计算总页数
totalPage:=ceil((totalRecord+0.0)/pageSize);
If (sqlWhere is NULL) Then
tsql:='Select * From (Select ROW_NUMBER() Over(Order By '||OrderField||') growID,'||Fields||' From '||TableName;
Else
tsql:='Select * From (Select ROW_NUMBER() Over(Order By '||OrderField||') growID,'||Fields||' From '||TableName||' Where '||sqlWhere;
End If;
--DBMS_OUTPUT.PUT_LINE(doPageIndex);
--处理页数超出范围情况
If (pageIndex<=0) Then
doPageIndex:=1;
End If;
If (pageIndex>totalPage) Then
doPageIndex:=totalPage;
End If;
--处理开始点和结束点
StartRecord:=(doPageIndex-1)*pageSize+1;
EndRecord:=StartRecord+(pageSize-1);
--继续合成SQL语句
tsql:=tsql||') '||TableName||' Where growID Between '||StartRecord||' And '||EndRecord;
DBMS_OUTPUT.PUT_LINE(tsql);
open RS for tsql;
--rollback;
end PAGING;