萬盛學電腦網

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

SQL Server 游標與 WHILE 迴圈實戰:從基本語法到高效能集合式改寫

前言:逐列處理的必要與代價

關聯式資料庫的核心優勢在於集合式操作,但實務上仍有情境需逐列處理:複雜業務邏輯、動態 SQL 組裝、跨資料庫同步、維護腳本等。SQL Server 提供 CURSOR (游標) 與 WHILE 迴圈 兩大機制。本文將系統說明語法、進階用法、效能陷阱,並示範如何以表變數 + WHILE 替代游標大幅提升效能。

一、CURSOR 游標完整語法與流程

標準 8 步驟流程

  1. DECLARE cursor_name CURSOR FOR SELECT ... — 宣告游標綁定結果集
  2. OPEN cursor_name — 開啟游標,建立結果集
  3. FETCH NEXT FROM cursor_name INTO @var1, @var2... — 取得第一列
  4. WHILE @@FETCH_STATUS = 0 BEGIN ... END — 迴圈處理 (0=成功, -1=失敗/結束, -2=列已刪除)
  5. 迴圈內執行業務邏輯 (UPDATE/INSERT/動態 SQL 等)
  6. FETCH NEXT FROM cursor_name INTO @var1, @var2... — 取得下一列
  7. CLOSE cursor_name — 關閉游標釋放結果集鎖定
  8. DEALLOCATE cursor_name — 釋放游標資源

基本範本

DECLARE @A1 VARCHAR(10), @A2 VARCHAR(10), @A3 INT;
DECLARE CURSOR YOUCURNAME FOR SELECT A1, A2, A3 FROM YOUTABLENAME;
OPEN YOUCURNAME;
FETCH NEXT FROM YOUCURNAME INTO @A1, @A2, @A3;
WHILE @@FETCH_STATUS <> -1 BEGIN
  -- 業務邏輯,例如:
  UPDATE SomeTable SET Col3 = @A3 WHERE Col1 = @A1 AND Col2 = @A2;
  FETCH NEXT FROM YOUCURNAME INTO @A1, @A2, @A3;
END
CLOSE YOUCURNAME;
DEALLOCATE YOUCURNAME;

游標選項關鍵字

建議:唯讀、單向掃描場景請使用 FAST_FORWARD 或 FORWARD_ONLY READ_ONLY,效能最佳。

二、WHILE 迴圈:無游標的迴圈替代方案

基本語法

WHILE Boolean_expression
BEGIN
  { sql_statement | statement_block }
  [ BREAK ]  -- 跳出迴圈
  [ CONTINUE ] -- 略過本次迭代剩餘敘述,進入下一次判斷
END

範例:簡單計數器插入

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

進階:巢狀迴圈與 BREAK/CONTINUE 控制

WHILE (SELECT AVG(price) FROM titles) < 30
BEGIN
  UPDATE titles SET price = price * 2;
  IF (SELECT MAX(price) FROM titles) > 50
    BREAK;
  ELSE
    CONTINUE;
END
PRINT 'Too much for the market to bear';

三、實戰案例:批量為學生加選課程

場景

老師為未畢業學生 (StudentGradu = 1) 批量加選指定課程 (@SubjectId),寫入 SelSubject 表。

版本 1:游標實作 (直觀但效能較差)

CREATE PROCEDURE P_InsertSubject_Cursor @SubjectId INT AS
BEGIN
  DECLARE @tempStudentID INT;
  DECLARE rs CURSOR LOCAL FAST_FORWARD FOR 
    SELECT studentid FROM student WHERE StudentGradu = 1;
  OPEN rs;
  FETCH NEXT FROM rs INTO @tempStudentID;
  WHILE @@FETCH_STATUS = 0
  BEGIN
    INSERT SelSubject VALUES (@SubjectId, @tempStudentID);
    FETCH NEXT FROM rs INTO @tempStudentID;
  END
  CLOSE rs;
  DEALLOCATE rs;
END

版本 2:表變數 + WHILE 迴圈 (避免游標開銷,大幅提升效能)

CREATE PROCEDURE P_InsertSubject_TableVar @SubjectId INT AS
BEGIN
  DECLARE @i INT, @studentid INT;
  DECLARE @tCanStudent TABLE (
    studentid INT PRIMARY KEY,
    FlagID TINYINT NOT NULL DEFAULT 0
  );
  
  -- 一次性載入符合條件學生
  INSERT @tCanStudent (studentid) 
  SELECT studentid FROM student WHERE StudentGradu = 1;
  
  SET @i = 1;
  WHILE @i >= 1
  BEGIN
    SELECT @studentid = '';
    SELECT TOP 1 @studentid = studentid FROM @tCanStudent WHERE FlagID = 0;
    SET @i = @@ROWCOUNT;
    IF @i <= 0 GOTO Return_Label;  -- 無未處理資料,跳出
    
    INSERT SelSubject VALUES (@SubjectId, @studentid);
    IF @@ERROR = 0
      UPDATE @tCanStudent SET FlagID = 1 WHERE studentid = @studentid;
      
  Return_Label:
  END
END

效能對比分析

選項說明
LOCAL僅在批次/預存程序範圍內可見 (預設)
GLOBAL連線層級可見
FORWARD_ONLY僅支援 FETCH NEXT,效能最佳
SCROLL支援 FIRST/LAST/PRIOR/NEXT/ABSOLUTE/RELATIVE
STATIC建立結果集複本,不反映後續資料變更
DYNAMIC反映所有資料變更 (預設)
FAST_FORWARDFORWARD_ONLY + READ_ONLY + 效能最佳化 (常用)
READ_ONLY禁止透過游標更新資料
指標游標版本表變數+WHILE版本
資料庫往返N 次 (每列一次 FETCH + INSERT)1 次載入 + N 次 INSERT (無 FETCH 往返)
鎖持有時間游標開啟期間持有共享鎖僅 INSERT 瞬間
Tempdb 壓力游標內部工作表表變數 (記憶體優先)
程式碼複雜度低中
適用場景動態 SQL、複雜逐列邏輯、需反映即時變更批量固定邏輯處理、大量資料

四、動態 SQL 搭配游標:多變量迴圈範例

CREATE PROCEDURE my_FeeCount AS
BEGIN
  DECLARE @到期時間 CHAR(10), @片區 CHAR(20), @繳費用戶數 CHAR(10), @sql NVARCHAR(2000);
  DECLARE cur_data CURSOR LOCAL FAST_FORWARD FOR
    SELECT CONVERT(VARCHAR(10), 到期時間, 120), 片區, 
           COUNT(DISTINCT main_id)
    FROM V_aipu_fee
    WHERE 提交時間 >= CONVERT(VARCHAR(10), GETDATE()-90, 120)
      AND 提交時間 < CONVERT(VARCHAR(10), GETDATE()+1-90, 120)
      AND 收費類型 = '續費收費'
    GROUP BY CONVERT(VARCHAR(10), 到期時間, 120), 片區
    ORDER BY CONVERT(VARCHAR(10), 到期時間, 120);
    
  OPEN cur_data;
  FETCH NEXT FROM cur_data INTO @到期時間, @片區, @繳費用戶數;
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = N'UPDATE ' + QUOTENAME(RTRIM(@片區) + N'實收') + 
               N' SET ' + QUOTENAME(RTRIM(@片區) + N'實收') + N' = ' + 
               QUOTENAME(RTRIM(@片區) + N'實收') + N' + ' + RTRIM(@繳費用戶數) +
               N' WHERE 收費日期 = ''' + RTRIM(@到期時間) + N'''';
    PRINT @sql;  -- 實際執行改用 EXEC sp_executesql @sql
    FETCH NEXT FROM cur_data INTO @到期時間, @片區, @繳費用戶數;
  END
  CLOSE cur_data;
  DEALLOCATE cur_data;
END
安全提醒:動態 SQL 必須使用 QUOTENAME() 防止 SQL 注入,參數化優於字串拼接。

五、偵錯技巧:VS 附加至 SQL Server 進程

  1. 部署預存程序至目標 SQL Server
  2. VS 開啟方案,設中斷點於游標/迴圈關鍵行
  3. 瀏覽器觸發呼叫 (或 SSMS 執行)
  4. VS → 偵錯 → 附加至處理序 → 選 w3wp.exe (IIS 6+) 或 aspnet_wp.exe (IIS 5)
  5. 勾選「Common Language Runtime」,確取消「Native」
  6. 觸發操作,中斷點命中,F11 逐步執行檢視變數
注意:偵錯會嚴重影響效能,遇到問題請重置 IIS 並重新附加。

六、總結:選擇正確的迴圈工具

  1. 優先考慮集合式單一陳述式 (INSERT...SELECT, MERGE, UPDATE...JOIN) — 效能最佳、最符合關聯式思維
  2. 需逐列處理時:
      • 固定邏輯、大量資料 → 表變數/暫存表 + WHILE (避免游標開銷)
      • 動態 SQL、需反映即時變更、複雜巡覽 → FAST_FORWARD 游標
  3. WHILE 迴圈 適合簡單計數、條件控制流程,不直接處理結果集
  4. 永遠記得 CLOSE / DEALLOCATE 清理游標資源
copyright © 萬盛學電腦網 all rights reserved