熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

精彩的近乎完美的分頁存儲過程

2013-11-13 15:37:42  來源: Oracle 

  CREATE procedure main_table_pwqzc
  (@pagesize int
  @pageindex int
  @docount bit
  @this_id)
  as
  if(@docount=)
  begin
  select count(id) from luntan where this_id=@this_id
  end
  else
  begin
  declare @indextable table(id int identity()nid int)
  declare @PageLowerBound int
  declare @PageUpperBound int
  set @PageLowerBound=(@pageindex)*@pagesize
  set @PageUpperBound=@PageLowerBound+@pagesize
  set rowcount @PageUpperBound
  insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc
  select a* from luntan a@indextable t where aid=tnid
  and tid>@PageLowerBound and tid<=@PageUpperBound order by tid
  end
  GO
  
  存儲過程會根據傳入的參數@docount來確定是不是要返回所有要分頁的記錄總數
  
  特別是這兩行
  set rowcount @PageUpperBound
  insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc
  
  真的是妙不可言!!set rowcount @PageUpperBound當記錄數達到@PageUpperBound時就會停止處理查詢
  
  select id 只把id列取出放到臨時表裡select a* from luntan a@indextable t where aid=tnid
  and tid>@PageLowerBound and tid<=@PageUpperBound order by tid
  而這句也只從表中取出所需要的記錄而不是所有的記錄結合起來極大的提高了效率!!
  
  妙啊真的妙!!!!
  
  CREATE PROCEDURE Paging_RowCount
  (
  @Tables varchar()
  @PK varchar()
  @Sort varchar() = NULL
  @PageNumber int =
  @PageSize int =
  @Fields varchar() = *
  @Filter varchar() = NULL
  @Group varchar() = NULL)
  AS
  
  /*Default Sorting*/
  IF @Sort IS NULL OR @Sort =
  SET @Sort = @PK
  
  /*Find the @PK type*/
  DECLARE @SortTable varchar()
  DECLARE @SortName varchar()
  DECLARE @strSortColumn varchar()
  DECLARE @operator char()
  DECLARE @type varchar()
  DECLARE @prec int
  
  /*Set sorting variables*/
  IF CHARINDEX(DESC@Sort)>
  BEGIN
  SET @strSortColumn = REPLACE(@Sort DESC )
  SET @operator = <=
  END
  ELSE
  BEGIN
  IF CHARINDEX(ASC @Sort) =
  SET @strSortColumn = REPLACE(@Sort ASC )
  SET @operator = >=
  END
  
  IF CHARINDEX( @strSortColumn) >
  BEGIN
  SET @SortTable = SUBSTRING(@strSortColumn CHARINDEX(@strSortColumn))
  SET @SortName = SUBSTRING(@strSortColumn CHARINDEX(@strSortColumn) + LEN(@strSortColumn))
  END
  ELSE
  BEGIN
  SET @SortTable = @Tables
  SET @SortName = @strSortColumn
  END
  
  SELECT @type=tname @prec=cprec
  FROM sysobjects o
  JOIN syscolumns c on oid=cid
  JOIN systypes t on cxusertype=txusertype
  WHERE oname = @SortTable AND cname = @SortName
  
  IF CHARINDEX(char @type) >
  SET @type = @type + ( + CAST(@prec AS varchar) + )
  
  DECLARE @strPageSize varchar()
  DECLARE @strStartRow varchar()
  DECLARE @strFilter varchar()
  DECLARE @strSimpleFilter varchar()
  DECLARE @strGroup varchar()
  
  /*Default Page Number*/
  IF @PageNumber <
  SET @PageNumber =
  
  /*Set paging variables*/
  SET @strPageSize = CAST(@PageSize AS varchar())
  SET @strStartRow = CAST(((@PageNumber )*@PageSize + ) AS varchar())
  
  /*Set filter & group variables*/
  IF @Filter IS NOT NULL AND @Filter !=
  BEGIN
  SET @strFilter = WHERE + @Filter +
  SET @strSimpleFilter = AND + @Filter +
  END
  ELSE
  BEGIN
  SET @strSimpleFilter =
  SET @strFilter =
  END
  IF @Group IS NOT NULL AND @Group !=
  SET @strGroup = GROUP BY + @Group +
  ELSE
  SET @strGroup =
  
  /*Execute dynamic query*/
  EXEC(
  
  DECLARE @SortColumn + @type +
  SET ROWCOUNT + @strStartRow +
  SELECT @SortColumn= + @strSortColumn + FROM + @Tables + @strFilter + + @strGroup + ORDER BY + @Sort +
  SET ROWCOUNT + @strPageSize +
  SELECT + @Fields + FROM + @Tables + WHERE + @strSortColumn + @operator + @SortColumn + @strSimpleFilter + + @strGroup + ORDER BY + @Sort +
  
  )
  GO
From:http://tw.wingwit.com/Article/program/Oracle/201311/17031.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.