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;