关于我在Oracle数据库中写的关于分页功能的存储过程(有错误),耗了我好几天的时间,头搞大了,也不知道错在哪里了,帮我看看嘛,该怎么解决呢?
/*
函数名称: GetPage
函数功能: 获取指定页的数据
参数说明:tblName 包含数据的表名
fldName 关键字段名
PageSize 每页记录数
PageIndex 要获取的页码
OrderType 排序类型, 0 - 升序, 1 - 降序
strWhere 查询条件 (注意: 不要加 where)
*/
Create Procedure GetPage(tblName varchar2(255),fldName varchar2(255),PageSize int32 := 10,PageIndex int32 := 1,OrderType Byte := 0,strWhere varchar2(2000) := '')
lS
strSQL varchar2(6000)
strTmp varchar2(1000)
strOrder varchar2(500)
if OrderType != 0
begin
set strTmp := '<(select min'
set strOrder := ' order by [' + fldName + '] desc'
end
else
begin
set strTmp := '>(select max'
set strOrder := ' order by [' + fldName +'] asc'
end
set strSQL := 'select top ' + str(PageSize) + ' * from ['
+ tblName + '] where [' + fldName + ']' + strTmp + '(['
+ fldName + ']) from (select top ' + str((PageIndex-1)*PageSize) + ' ['
+ fldName + '] from [' + tblName + ']' + strOrder + ') as tblTmp)'
+ strOrder
if strWhere != ''
set strSQL := 'select top ' + str(PageSize) + ' * from ['
+ tblName + '] where [' + fldName + ']' + strTmp + '(['
+ fldName + ']) from (select top ' + str((PageIndex-1)*PageSize) + ' ['
+ fldName + '] from [' + tblName + '] where ' + strWhere + ' '
+ strOrder + ') as tblTmp) and ' + strWhere + ' ' + strOrder
if PageIndex := 1
begin
set strTmp := ''
if strWhere != ''
set strTmp := ' where (' + strWhere + ')'

set strSQL := 'select top ' + str(PageSize) + ' * from ['
+ tblName + ']' + strTmp + ' ' + strOrder
end;