萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2000/2005 分頁存儲過程大全:三種優化版本與二分法查詢

SQL Server 2005 方案一:基礎版(ROW_NUMBER)

CREATE PROC up_Page2005
    @TableName varchar(50),
    @Fields varchar(5000) = '*',
    @OrderField varchar(5000),      -- 必填,支援多欄位
    @SqlWhere varchar(5000) = NULL,
    @PageSize int,
    @PageIndex int = 1,
    @TotalPage int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;

        DECLARE @sql nvarchar(4000), @totalRecord int;

        -- 1. 計算總筆數
        SET @sql = 'SELECT @totalRecord = COUNT(*) FROM ' + @TableName + 
                   CASE WHEN @SqlWhere IS NULL OR @SqlWhere = '' THEN '' ELSE ' WHERE ' + @SqlWhere END;
        EXEC sp_executesql @sql, N'@totalRecord int OUTPUT', @totalRecord OUTPUT;

        SET @TotalPage = CEILING(@totalRecord * 1.0 / @PageSize);

        -- 2. 組裝分頁查詢
        SET @sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + ') AS RowId, ' + 
                   @Fields + ' FROM ' + @TableName + 
                   CASE WHEN @SqlWhere IS NULL OR @SqlWhere = '' THEN '' ELSE ' WHERE ' + @SqlWhere END + ') t ' +
                   'WHERE RowId BETWEEN ' + CAST((@PageIndex-1)*@PageSize + 1 AS varchar) + ' AND ' + 
                   CAST(@PageIndex*@PageSize AS varchar);

        EXEC (@sql);

        COMMIT TRAN;
        RETURN @totalRecord;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN;
        RETURN -1;
    END CATCH
END

方案二:優化版(支援傳入總筆數、避免重複計數)

CREATE PROC up_Page2005V2
    @TableName varchar(50),
    @Fields varchar(5000) = '*',
    @OrderField varchar(5000),
    @SqlWhere varchar(5000) = NULL,
    @PageSize int,
    @PageIndex int = 1,
    @TotalRecord int = 0,           -- 傳入已知總數可跳過 COUNT(*)
    @TotalPage int OUTPUT
AS
BEGIN
    -- 邏輯同方案一,前面加上 IF @TotalRecord <= 0 BEGIN ... 計算總數 END
    -- 其餘相同
END

方案三:支援 JOIN 多表查詢

CREATE PROC up_Page2005V2_Join
    @TableName varchar(150),        -- 可傳入 "TableA a JOIN TableB b ON a.id=b.aid"
    @Fields varchar(5000) = '*',
    @OrderField varchar(5000),
    @SqlWhere varchar(5000) = NULL,
    @PageSize int,
    @PageIndex int = 1,
    @TotalRecord int = 0,
    @TotalPage int OUTPUT
AS
BEGIN
    -- 計算總數與分頁查詢同方案二,但 FROM 子句直接使用 @TableName 參數
    -- 注意:@OrderField 需包含別名前綴,如 a.CreateTime
END

SQL Server 2000 相容版本(無 ROW_NUMBER)

CREATE PROC up_Page2000
    @TableName varchar(50), @Fields varchar(5000), @OrderField varchar(5000),
    @SqlWhere varchar(5000), @PageSize int, @PageIndex int,
    @TotalRecord int OUTPUT, @TotalPage int OUTPUT
AS
BEGIN
    -- 使用臨時表 + IDENTITY 模擬行號
    DECLARE @sql nvarchar(4000), @start int, @end int;
    SET @sql = 'SELECT IDENTITY(int,1,1) AS RowId, ' + @Fields + ' INTO #TmpPage FROM ' + @TableName + 
               CASE WHEN @SqlWhere IS NULL OR @SqlWhere='' THEN '' ELSE ' WHERE ' + @SqlWhere END +
               ' ORDER BY ' + @OrderField;
    EXEC (@sql);

    SELECT @TotalRecord = COUNT(*) FROM #TmpPage;
    SET @TotalPage = CEILING(@TotalRecord * 1.0 / @PageSize);
    SET @start = (@PageIndex-1)*@PageSize + 1;
    SET @end = @PageIndex*@PageSize;

    EXEC ('SELECT ' + @Fields + ' FROM #TmpPage WHERE RowId BETWEEN ' + @start + ' AND ' + @end);
    DROP TABLE #TmpPage;
END

附錄:二分法分頁(適用於有連續主鍵的大表)

CREATE PROC up_PageBinary
    @TableName varchar(50), @Fields varchar(5000), @OrderField varchar(5000),
    @SqlWhere varchar(5000), @PageSize int, @PageIndex int
AS
BEGIN
    DECLARE @low int, @high int, @mid int, @sql nvarchar(4000);
    -- 先取得主鍵最小/最大值
    SET @sql = 'SELECT @low=MIN('+@OrderField+'), @high=MAX('+@OrderField+') FROM '+@TableName+
               CASE WHEN @SqlWhere IS NULL OR @SqlWhere='' THEN '' ELSE ' WHERE '+@SqlWhere END;
    EXEC sp_executesql @sql, N'@low int OUTPUT, @high int OUTPUT', @low OUTPUT, @high OUTPUT;

    -- 二分法定位起始主鍵值
    WHILE @low < @high
    BEGIN
        SET @mid = (@low + @high) / 2;
        SET @sql = 'SELECT COUNT(*) FROM '+@TableName+' WHERE '+@OrderField+' <= '+CAST(@mid AS varchar)+
                   CASE WHEN @SqlWhere IS NULL OR @SqlWhere='' THEN '' ELSE ' AND '+@SqlWhere END;
        EXEC sp_executesql @sql, N'@cnt int OUTPUT', @cnt OUTPUT;
        IF @cnt < (@PageIndex-1)*@PageSize SET @low = @mid + 1 ELSE SET @high = @mid;
    END

    -- 最後取一頁
    SET @sql = 'SELECT TOP ' + CAST(@PageSize AS varchar) + ' ' + @Fields + ' FROM ' + @TableName + 
               ' WHERE ' + @OrderField + ' >= ' + CAST(@low AS varchar) +
               CASE WHEN @SqlWhere IS NULL OR @SqlWhere='' THEN '' ELSE ' AND '+@SqlWhere END +
               ' ORDER BY ' + @OrderField;
    EXEC (@sql);
END

選型建議

  • SQL Server 2005+:**優先使用方案二**(可傳入總數、效能最佳)。
  • 需 JOIN 多表:**方案三**。
  • SQL Server 2000:方案四(臨時表)或二分法(主鍵連續、分布均勻時極快)。
  • **避免動態 SQL 注入**:@TableName、@OrderField、@SqlWhere 來自用戶輸入時**必須嚴格白名單驗證**。
copyright © 萬盛學電腦網 all rights reserved