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

sql存儲過程分頁

2013-11-13 16:02:24  來源: Oracle 

  CREATE PROC myx_prPageRecordset
    @queryStr nvarchar()
    @keyField nvarchar ()
    @pageSize int
    @pageNumber int
    AS
    BEGIN
    DECLARE @sqlText AS nvarchar()
    DECLARE @sqlTable AS nvarchar()
    SET @sqlTable = SELECT TOP + CAST((@pageNumber + ) * @pageSize AS varchar()) + + @queryStr
    SET @sqlText =
    SELECT TOP + CAST(@pageSize AS varchar()) + * +
    FROM ( + @sqlTable + ) AS tableA +
    WHERE + @keyField + NOT IN(SELECT TOP +
    CAST(@pageNumber * @pageSize AS varchar()) + + @keyField +
    FROM ( + @sqlTable + ) AS tableB)
    EXEC (@sqlText)
    END

  GO

  核心代碼
    Dim strsql As String
    myComm = New SqlClientSqlCommand(myx_prPageRecordset myConn)
    myCommCommandType = CommandTypeStoredProcedure
    myCommParametersAdd(New SqlClientSqlParameter(@queryStr SqlDbTypeNVarChar ))
    myCommParameters(@queryStr)Value = * from tbpage order by id DESC
    myCommParametersAdd(New SqlClientSqlParameter(@keyField SqlDbTypeNVarChar ))
    myCommParameters(@keyField)Value = [id]
    myCommParametersAdd(New SqlClientSqlParameter(@pageSize SqlDbTypeNVarChar ))
    myCommParameters(@pageSize)Value = PageSize
    myCommParametersAdd(New SqlClientSqlParameter(@pageNumber SqlDbTypeNVarChar ))
    myCommParameters(@pageNumber)Value = myPage

  呵呵執行幾W條的代碼只需毫秒左右

  建立一個test(idnamefid)

  向test添充幾十條數據使id=(即遞增的integer)其他任意在Tsql Debugger給改存儲過程分別傳遞如下參數:

  @queryStr= * from test
    @keyField=[ID]
    @pageSize=
    @pageNumber=

  問題出來了看輸出結果(注意id):
    id name fid
    kwklover
    kwklover
    kwklover

  根據傳入參數我們的預期應該是:
    id name fid
    kwklover
    kwklover
    kwklover

  下面是我參照小春的存儲分頁寫的分頁存儲過程可以解決上面的問題:
    CREATE Procedure prGetRecordByPage
    (
    @PageSize int 每頁的記錄條數
    @PageNumber int 當前頁面
    @QuerySql varchar()部分查詢字符串如* From Test order by id desc
    @KeyField varchar()
    )
    AS
    Begin

  Declare @SqlTable AS varchar()
    Declare @SqlText AS Varchar()

  Set @SqlTable=Select Top +CAST(@PageNumber*@PageSize AS varchar())+ +@QuerySql
    Set @SqlText=Select Top +Cast(@PageSize AS varchar())+ * From
    +(+@SqlTable+) As TembTbA
    +Where +@KeyField+ Not In (Select Top +CAST((@PageNumber)*@PageSize AS varchar())+ +@KeyField+ From
    +(+@SqlTable+) AS TempTbB)
    Exec(@SqlText)

  End
    GO


From:http://tw.wingwit.com/Article/program/Oracle/201311/17708.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.