SQL分页存储过程
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE PROCEDURE [dbo].[sp_PageIndex]
(
-- Add the parameters for the stored procedure here
@tblName varchar(50), --(表名、视图名)
@strGetFields varchar(255)='*', --(查询字段列表)
@orderName varchar(255), --(排序字段名称)
@maxRows int , --(每页显示记录数)
@StartIndex int, --(当前页码)
@IsCount bit=0, --是否返回查询总记录数 (0: no;1:yes)
@OrderType bit=0, --排序类型名称(0:升序;1:降序)
@strWhere varchar(3000)='' --查询条件（where部分，不用加where关键字）
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @strSql varchar(5000)
declare @strTemp varchar(100)
declare @strOrder varchar(100)
declare @strCount varchar(3000) -- 返回数据的总条数语句
if @orderType!=0
begin
set @strTemp='<(select min'
set @strOrder='order by ['+@orderName+'] desc'
end
else
begin
set @strTemp='>(select max'
set @strOrder='order by ['+@orderName+'] asc'
end
if @StartIndex=1
begin
if @strWhere!=''
begin
set @strSql='select top '+str(@maxRows)+' '+@strGetFields+' from ['+@tblName+']where '+@strWhere+' '+@strOrder
SET @strCount = 'select count(*) as Total from ' + @tblName + ' as count where ' + @strWhere
end
else
begin
set @strSql='select top '+str(@maxRows)+' '+@strGetFields+' from ['+@tblName+'] '+@strOrder
SET @strCount = 'select count(*) as Total from ' + @tblName
end
end
else
begin
if @strWhere!=''
begin
set @strSql='select top '+str(@maxRows)+' '+@strGetFields+' from ['+@tblName+'] where ['
+@orderName+'] '+@strTemp+'(['+@orderName+']) from (select top '+str((@StartIndex-1)*@maxRows)
+' ['+@orderName+'] from ['+@tblName+'] where '+@strWhere+' '+@strOrder+') as tableTemp) and '
+@strWhere+' '+@strOrder
SET @strCount = 'select count(*) as Total from ' + @tblName + ' as count where ' + @strWhere
end
else
begin
set @strSql='select top '+str(@maxRows)+' '+@strGetFields+' from ['+@tblName+'] where ['+@orderName+'] '+@strTemp+'(['+@orderName+']) from (select top '+str((@StartIndex-1)*@maxRows)
+' ['+@orderName+'] from ['+@tblName+']'+@strOrder+') as tableTemp) '+@strOrder
SET @strCount = 'select count(*) as Total from ' + @tblName
end
end
if @IsCount!=0 ----按需要返回数据的总条数
begin
exec (@strCount)
end
exec (@strSql)
END
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
oracle存储过程分页（包和包体结构）
***************包结构*******************
create or replace package PG_BK_QUERYPAGE
is
--定义游标
TYPE BKCURSOR IS REF CURSOR;
PROCEDURE SP_PageIndex
(
PI_TBNAME IN VARCHAR2, --表名
PI_FIELD0 IN VARCHAR2, --字段集
PI_ROWFIL IN VARCHAR2, --过滤条件
PI_SORTST IN VARCHAR2, --排序集
PI_PAGENU IN VARCHAR2, --页码
PI_PAGEPG IN VARCHAR2, --每页显示条数
P0_TOTALC OUT NUMBER, --总记录数
P0_CURSOR OUT BKCURSOR --返回游标
);
end PG_BK_QUERYPAGE;
***************包体结构*******************
create or replace package body PG_BK_QUERYPAGE
is
PROCEDURE SP_PageIndex
(
PI_TBNAME IN VARCHAR2, --表名
PI_FIELD0 IN VARCHAR2, --字段集
PI_ROWFIL IN VARCHAR2, --过滤条件
PI_SORTST IN VARCHAR2, --排序集
PI_PAGENU IN VARCHAR2, --页码
PI_PAGEPG IN VARCHAR2, --每页显示条数
P0_TOTALC OUT NUMBER, --总记录数
P0_CURSOR OUT BKCURSOR --返回游标
)
IS
V_TABLE1 VARCHAR2(500); --动态名1
V_TABLE2 VARCHAR2(500); --动态名2
V_TABLE3 VARCHAR2(500); --动态名3
V_TABLE4 VARCHAR2(500); --动态名4
V_PAGENU VARCHAR2(10); --页码变量
V_PAGESY VARCHAR2(10); --记录数数量
BEGIN
V_PAGENU:=PI_PAGENU*PI_PAGEPG;
V_PAGESY:=(PI_PAGENU-1)*PI_PAGEPG;
V_TABLE1 := 'SELECT T1.'|| PI_FIELD0 ||', ROWNUM AS RN FROM '|| PI_TBNAME || ' T1';
V_TABLE2 := V_TABLE1 ||' WHERE ' || PI_ROWFIL || ' AND ROWNUM <= '|| V_PAGENU ;
V_TABLE3 := 'SELECT COUNT(*) AS SUM FROM ' || PI_TBNAME || ' WHERE ' || PI_ROWFIL;
V_TABLE4 := 'SELECT * FROM ('|| V_TABLE2 ||') T WHERE T.RN>'||V_PAGESY || 'ORDER BY '||PI_SORTST||' DESC';
execute immediate V_TABLE3 INTO P0_TOTALC; --返回总记录
OPEN P0_CURSOR FOR V_TABLE4;
END;
end PG_BK_QUERYPAGE;
