小弟我才转到oracel很不熟悉,有一个存储过程自己修改了部分,但是有很多错误,请大虾修改成oracle版,马上给分。
--最通用的分页存储过程
-- 获取指定页的数据
CREATE PROCEDURE Pagination
tableName varchar2(255), -- 表名
strGetFields varchar2(1000) = '*', -- 需要返回的列
fieldName varchar(255)='', -- 排序的字段名
PageSize int = 10, -- 页尺寸
PageIndex int = 1, -- 页码
doCount bit = 0, -- 返回记录总数, 非 0 值则返回
OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
strWhere varchar2(1500) = '' -- 查询条件 (注意 不要加 where)
AS
declare strSQL varchar2(5000) -- 主Sql语句
declare strTmp varchar2(110) -- 临时变量
declare strOrder varchar2(400) -- 排序类型 if doCount <> 0 --如果记录数不为0
begin
if :strWhere <>''
set strSQL = 'select count(*) as Total from '+ tableName +' where '+ strWhere
else
set strSQL = 'select count(*) as Total from '+ tableName +''
end
--以上代码的意思是如果 doCount传递过来的不是0,就执行总数统计。以下的所有代码都
--是 doCount为0的情况
else
begin
if OrderType <> 0 --如果@OrderType不是0,就执行降序,这句很重要!
begin
set strTmp = '<(select min'
set strOrder = ' order by '+ fieldName +' desc'
end
else
begin
set strTmp = '>(select max'
set strOrder = ' order by '+ fieldName +'asc'
end if PageIndex = 1 --如果是第一页就执行以上代码,这样会加快执行速度
begin
if strWhere <> ''
set strSQL = 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +' where ' + strWhere + ' ' + strOrder
else
set strSQL = 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +' '+ strOrder
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set strSQL = 'select top ' + str( PageSize) +' '+ strGetFields+ ' from ' + tableName +' where ' + fieldName + '' + strTmp + '('+ fieldName + ')
from (select top ' + str(( PageIndex-1)* PageSize) + ' '+ fieldName + ' from '+ tableName +'' + strOrder + ') as tblTmp)'+ strOrder
if strWhere <> ''
set strSQL = 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +'
where ' + fieldName + '' + strTmp + '('+ fieldName + ')
from (select top ' + str(( PageIndex-1)* PageSize) + ' '+ fieldName + '
from '+ tableName +' where ' + strWhere + ' ' + strOrder + ') as tblTmp) and ' + strWhere + ' ' + strOrder
end
end exec ( strSQL)
GO
--最通用的分页存储过程
-- 获取指定页的数据
CREATE PROCEDURE Pagination
tableName varchar2(255), -- 表名
strGetFields varchar2(1000) = '*', -- 需要返回的列
fieldName varchar(255)='', -- 排序的字段名
PageSize int = 10, -- 页尺寸
PageIndex int = 1, -- 页码
doCount bit = 0, -- 返回记录总数, 非 0 值则返回
OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
strWhere varchar2(1500) = '' -- 查询条件 (注意 不要加 where)
AS
declare strSQL varchar2(5000) -- 主Sql语句
declare strTmp varchar2(110) -- 临时变量
declare strOrder varchar2(400) -- 排序类型 if doCount <> 0 --如果记录数不为0
begin
if :strWhere <>''
set strSQL = 'select count(*) as Total from '+ tableName +' where '+ strWhere
else
set strSQL = 'select count(*) as Total from '+ tableName +''
end
--以上代码的意思是如果 doCount传递过来的不是0,就执行总数统计。以下的所有代码都
--是 doCount为0的情况
else
begin
if OrderType <> 0 --如果@OrderType不是0,就执行降序,这句很重要!
begin
set strTmp = '<(select min'
set strOrder = ' order by '+ fieldName +' desc'
end
else
begin
set strTmp = '>(select max'
set strOrder = ' order by '+ fieldName +'asc'
end if PageIndex = 1 --如果是第一页就执行以上代码,这样会加快执行速度
begin
if strWhere <> ''
set strSQL = 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +' where ' + strWhere + ' ' + strOrder
else
set strSQL = 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +' '+ strOrder
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set strSQL = 'select top ' + str( PageSize) +' '+ strGetFields+ ' from ' + tableName +' where ' + fieldName + '' + strTmp + '('+ fieldName + ')
from (select top ' + str(( PageIndex-1)* PageSize) + ' '+ fieldName + ' from '+ tableName +'' + strOrder + ') as tblTmp)'+ strOrder
if strWhere <> ''
set strSQL = 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +'
where ' + fieldName + '' + strTmp + '('+ fieldName + ')
from (select top ' + str(( PageIndex-1)* PageSize) + ' '+ fieldName + '
from '+ tableName +' where ' + strWhere + ' ' + strOrder + ') as tblTmp) and ' + strWhere + ' ' + strOrder
end
end exec ( strSQL)
GO
tableName varchar2(255), -- 表名
strGetFields varchar2(1000) := '*', -- 需要返回的列
fieldName varchar(255):='', -- 排序的字段名
PageSize pls_integer default 10, -- 页尺寸
PageIndex pls_integer default 1, -- 页码
doCount pls_integer default 0, -- 返回记录总数, 非 0 值则返回
OrderType pls_integer default 0, -- 设置排序类型, 非 0 值则降序
strWhere varchar2(1500) default '' -- 查询条件 (注意 不要加 where)
)
AS
strSQL varchar2(5000); -- 主Sql语句
strTmp varchar2(110); -- 临时变量
strOrder varchar2(400); -- 排序类型if doCount <> 0 then--如果记录数不为0
if strWhere <> '' then
strSQL := 'select count(*) as Total from '+ tableName +' where '+ strWhere;
else
strSQL := 'select count(*) as Total from '+ tableName +'';
end if;--以上代码的意思是如果 doCount传递过来的不是0,就执行总数统计。以下的所有代码都
--是 doCount为0的情况else
if OrderType <> 0 --如果@OrderType不是0,就执行降序,这句很重要!
strTmp := '<(select min';
strOrder := ' order by '+ fieldName +' desc';
else
strTmp := '>(select max';
strOrder := ' order by '+ fieldName +'asc';
end if;
end if;if PageIndex = 1 then --如果是第一页就执行以上代码,这样会加快执行速度
if strWhere <> '' then
strSQL := 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +' where ' + strWhere + ' ' + strOrder;
else
strSQL := 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +' '+ strOrder;
end if;else
--以下代码赋予了@strSQL以真正执行的SQL代码
strSQL := 'select top ' + str( PageSize) +' '+ strGetFields+ ' from ' + tableName +' where ' + fieldName + '' + strTmp + '('+ fieldName + ')
from (select top ' + str(( PageIndex-1)* PageSize) + ' '+ fieldName + ' from '+ tableName +'' + strOrder + ') as tblTmp)'+ strOrder;
end if;if strWhere <> '' then
strSQL:= 'select top ' + str( PageSize) +' '+ strGetFields+ ' from '+ tableName +'where ' + fieldName + '' + strTmp + '('+ fieldName + ')
from (select top ' + str(( PageIndex-1)* PageSize) + ' '+ fieldName + '
from '+ tableName +' where ' + strWhere + ' ' + strOrder + ') as tblTmp) and ' + strWhere + ' ' + strOrder;
end if;end Pagination;