筆者打算通過本文也來簡單談一下基于SQL SERVER 2000的分頁存儲過程，同時談談SQL SERVER 2005下分頁存儲過程的演進。
在進行基于UI顯示的數據分頁時，常見的數據提取方式主要有兩種。第一種是從數據庫提取所有數據然后在系統應用程序層進行數據分頁，顯示當前頁數據。第二種分頁方式為從數據庫取出需要顯示的一頁數據顯示在UI界面上。
以下是筆者對兩種實現方式所做的優缺點比較，針對應用程序編寫，筆者以.NET技術平臺為例。
|
類別
|
SQL語句
|
代碼編寫
|
設計時
|
性能
|
|
第一種
|
語句簡單，兼容性好
|
很少
|
完全支持
|
數據越大性能越差
|
|
第二種
|
看具體情況
|
較多
|
部分支持
|
良好，跟SQL語句有關
|
對于第一種情況本文不打算舉例，第二種實現方式筆者只以兩次TOP方式來進行討論。
在編寫具體SQL語句之前，定義以下數據表。
數據表名稱為：Production.Product。Production為SQL SERVER 2005中改進后的數據表架構，對舉例不造成影響。
包含的字段為：
|
列名
|
數據類型
|
允許空
|
說明
|
|
ProductID
|
Int
|
|
產品ID，PK。
|
|
Name
|
Nvarchar(50)
|
|
產品名稱。
|
不難發現以上表結構來自SQL SERVER 2005 樣例數據庫AdventureWorks的Production.Product表，并且只取其中兩個字段。
分頁相關元素：
PageIndex – 頁面索引計數，計數0為第一頁。
PageSize – 每個頁面顯示大小。
RecordCount – 總記錄數。
PageCount – 頁數。
對于后兩個參數，筆者在存儲過程中以輸出參數提供。
1．SQL SERVER 2000中的TOP分頁
CREATE PROCEDURE [Zhzuo_GetItemsPage]
@PageIndex INT, /*@PageIndex從計數,0為第一頁*/
@PageSize INT, /*頁面大小*/
@RecordCount INT OUT, /*總記錄數*/
@PageCount INT OUT /*頁數*/
AS
/*獲取記錄數*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*計算頁面數據*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP記錄數*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
'ProductID,Name FROM Production.Product ORDER BY ProductID DESC'
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
END
ELSE
BEGIN
SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
END
END
/*執行*/
EXEC (@SQLSTR)
以上存儲過程對頁數進行判斷，如果是第一頁或最后一頁，進行特殊處理。其他情況使用2次TOP翻轉。其中排序條件為ProductID倒序。最后通過EXECUTE執行SQL字符串拼串。
2．SQL SERVER 2005中的TOP分頁
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/**//*獲取記錄數*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/**//*計算頁面數據*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/**//*TOP記錄數*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
/**//*基于SQL SERVER 2005 */
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SELECT TOP(@PageSize) ProductID,Name FROM Production.Product ORDER BY ProductID DESC
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SELECT * FROM ( SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T
ORDER BY ProductID DESC
END
ELSE
BEGIN
SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T
ORDER BY ProductID DESC
END
END
以上存儲過程是使用2005的TOP (表達式) 新功能，避免了字符串拼串，使結構化查詢語言變得簡潔。實現的為同樣的功能。
3．SQL SERVER 2005中的新分頁
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*獲取記錄數*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*計算頁面數據*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基于SQL SERVER 2005 */
SELECT SerialNumber,ProductID,Name FROM
(SELECT ProductID,Name,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
第三個存儲過程使用2005下新的功能，實現的分頁存儲過程功能更加簡單明了，而且更加容易理解。注意這里的ProductID為主鍵,根據ProductID進行排序生成ROW_NUMBER，通過ROW_NUMBER來確定具體的頁數。
通過對三個分頁存儲過程的比較，可見SQL SERVER 的TSQL 語言對分頁功能的支持進步不少。使分頁實現趨向于簡單化
posted on 2008-09-27 11:52
fly 閱讀(318)
評論(0) 編輯 收藏 所屬分類:
數據庫學習