您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 游標與 WHILE 迴圈實戰：從基礎語法到效能優化替代方案

編輯：關於SqlServer

游標基礎語法

DECLARE @A1 VARCHAR(10), @A2 VARCHAR(10), @A3 INT;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR  -- 僅前進、唯讀、區域
  SELECT A1, A2, A3 FROM YourTable;

OPEN cur;
FETCH NEXT FROM cur INTO @A1, @A2, @A3;

WHILE @@FETCH_STATUS = 0 BEGIN
  -- 處理單列邏輯
  UPDATE AnotherTable SET Col = @A3 WHERE KeyCol = @A1;

  FETCH NEXT FROM cur INTO @A1, @A2, @A3;
END

CLOSE cur;
DEALLOCATE cur;

關鍵選項：LOCAL 限制作用域、FAST_FORWARD (FORWARD_ONLY + READ_ONLY) 效能最佳、STATIC 建立複本隔離並發變更。

WHILE 迴圈基礎語法

DECLARE @i INT = 1;
WHILE @i < 30 BEGIN
  INSERT INTO TestTable (UserID) VALUES (@i);
  SET @i += 1;
END

控制流：BREAK 跳出最內層、CONTINUE 跳過本次迴圈剩餘語句。

實戰案例：批次為學生加選課程

版本 A：游標逐列 INSERT (高 I/O 交互)

CREATE PROCEDURE P_InsertSubject_Cursor @SubjectId INT AS
DECLARE @StudentId INT;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT StudentId FROM Student WHERE StudentGradu = 1; -- 未畢業
OPEN cur;
FETCH NEXT FROM cur INTO @StudentId;
WHILE @@FETCH_STATUS = 0 BEGIN
  INSERT INTO SelSubject (SubjectId, StudentId) VALUES (@SubjectId, @StudentId);
  FETCH NEXT FROM cur INTO @StudentId;
END
CLOSE cur; DEALLOCATE cur;

版本 B：表變數 + 旗標模擬集合操作 (減少 I/O 往返)

CREATE PROCEDURE P_InsertSubject_TempTable @SubjectId INT AS
DECLARE @StudentId INT, @RowCount INT;

DECLARE @Tmp TABLE (StudentId INT PRIMARY KEY, Done BIT NOT NULL DEFAULT 0);
INSERT @Tmp (StudentId) SELECT StudentId FROM Student WHERE StudentGradu = 1;

SELECT @RowCount = COUNT(*) FROM @Tmp;
WHILE @RowCount > 0 BEGIN
  SELECT TOP 1 @StudentId = StudentId FROM @Tmp WHERE Done = 0;
  INSERT INTO SelSubject (SubjectId, StudentId) VALUES (@SubjectId, @StudentId);
  UPDATE @Tmp SET Done = 1 WHERE StudentId = @StudentId;
  SET @RowCount = @@ROWCOUNT;
END

版本 C：純集合 INSERT (最佳效能)

CREATE PROCEDURE P_InsertSubject_SetBased @SubjectId INT AS
INSERT INTO SelSubject (SubjectId, StudentId)
SELECT @SubjectId, StudentId
FROM Student
WHERE StudentGradu = 1
  AND NOT EXISTS (SELECT 1 FROM SelSubject s WHERE s.SubjectId = @SubjectId AND s.StudentId = Student.StudentId);

效能對比與選擇指南

方案	適用場景	優點	缺點
游標	複雜逐列邏輯、需呼叫外部程序、動態 SQL 建構	邏輯直觀、支援複雜控制流	逐列執行、高 CPU/鎖/記錄開銷、難以平行化
WHILE + 表變數	需逐列但可批次提交、邏輯較簡單	減少編譯/計畫開銷、可控制批次大小	仍屬逐列、統計資訊缺失可能導致差計畫
集合操作 (SET-based)	絕大多數 DML、查詢、ETL	優化器充分優化、平行執行、最低記錄開銷	極複雜邏輯難以單陳述式表達

進階：多變量游標與動態 SQL 範例

CREATE PROCEDURE dbo.UpdateAreaCollection AS
DECLARE @DueDate CHAR(10), @Area CHAR(20), @Cnt VARCHAR(10), @SQL NVARCHAR(MAX);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT CONVERT(CHAR(10), DueDate, 120), Area, CAST(COUNT(DISTINCT MainId) AS VARCHAR(10))
  FROM V_FeeCollection
  WHERE SubmitDate >= CONVERT(CHAR(10), DATEADD(DAY, -90, GETDATE()), 120)
    AND SubmitDate < CONVERT(CHAR(10), DATEADD(DAY, -89, GETDATE()), 120)
    AND FeeType = N'續費'
  GROUP BY CONVERT(CHAR(10), DueDate, 120), Area
  ORDER BY CONVERT(CHAR(10), DueDate, 120);

OPEN cur;
FETCH NEXT FROM cur INTO @DueDate, @Area, @Cnt;
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @SQL = N'UPDATE ' + QUOTENAME(@Area + N'實收') + 
             N' SET 實收 = 實收 + ' + @Cnt + 
             N' WHERE 收費日期 = ' + QUOTENAME(@DueDate, '''');
  EXEC sp_executesql @SQL;  -- 參數化更安全
  FETCH NEXT FROM cur INTO @DueDate, @Area, @Cnt;
END
CLOSE cur; DEALLOCATE cur;