SQL与oracle分页存储过程

 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;