萬盛學電腦網

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

SQL Server 無游標逐行處理:ROW_NUMBER + WHILE 迴圈模式

替代游標的集合式逐行處理模式

游標逐行處理效能差、鎖持久、程式碼冗長。SQL Server 2005+ 引入 ROW_NUMBER() 窗口函數,配合 WHILE 迴圈可實現「偽集合」逐行邏輯,效能大幅提升。

標準範本

-- 1. 將目標資料集物化至暫時表,並產生連續行號
SELECT Department_No AS DepartmentNo,
       ROW_NUMBER() OVER (ORDER BY Department_No) AS RowNum
INTO #DeptTemp
FROM Departments;

-- 2. 取得最大行號
DECLARE @MaxRow INT = (SELECT MAX(RowNum) FROM #DeptTemp);
DECLARE @CurrentRow INT = 1;

-- 3. WHILE 迴圈逐行處理
WHILE @CurrentRow <= @MaxRow
BEGIN
    -- 取得當前行資料
    DECLARE @DeptNo NVARCHAR(50);
    SELECT @DeptNo = DepartmentNo FROM #DeptTemp WHERE RowNum = @CurrentRow;

    -- ===== 此處放置單行業務邏輯 =====
    -- 例:呼叫存儲過程、動態 SQL、跨資料庫操作等無法集合化的動作
    -- EXEC usp_ProcessDepartment @DeptNo;
    PRINT 'Processing: ' + @DeptNo;
    -- ================================

    SET @CurrentRow += 1;
END

-- 4. 清理
DROP TABLE #DeptTemp;

關鍵優化點

  1. 暫時表而非表變數:#Temp 擁有統計資訊,優化器可產生更好計畫;@Table 無統計資訊,大資料量易導致嚴重誤估。
  2. ROW_NUMBER 確保確定性順序:OVER (ORDER BY ...) 保證每次執行行號一致,避免並行計畫導致順序不穩。
  3. 單行邏輯封裝為存儲過程:迴圈體內僅呼叫 EXEC usp_XXX @Param,保持主邏輯清晰,便於單元測試。
  4. 批次提交:若單行操作包含 DML,考慮每 100-500 行 COMMIT 一次,避免長交易鎖升級與日誌暴增。

進階:並行化處理(SQL Server 2016+)

若單行邏輯無相依關係,可改用 sp_execute_external_script (R/Python) 或 SSIS 並行任務,或將迴圈拆分為多批次併發執行(需應用層協調)。

何時仍需游標

  • 動態 SQL 結構每行不同(極罕見)。
  • 需逐行向用戶端回傳結果(如觸發器內)。
  • 舊版本 (SQL 2000) 維護專案。

反模式警示

-- ❌ 反模式:WHILE 迴圈內直接 SELECT/UPDATE 永久表
WHILE @i <= @max
BEGIN
    UPDATE BigTable SET Col = @i WHERE Id = @i; -- 每次獨立查詢計畫、鎖、網路往返
    SET @i += 1;
END

此模式將集合操作退化為 RBAR (Row By Agonizing Row),效能災難。務必先物化至暫時表。

copyright © 萬盛學電腦網 all rights reserved