| 選項 | 說明 |
| LOCAL | 僅在批次/預存程序範圍內可見 (預設) |
| GLOBAL | 連線層級可見 |
| FORWARD_ONLY | 僅支援 FETCH NEXT,效能最佳 |
| SCROLL | 支援 FIRST/LAST/PRIOR/NEXT/ABSOLUTE/RELATIVE |
| STATIC | 建立結果集複本,不反映後續資料變更 |
| DYNAMIC | 反映所有資料變更 (預設) |
| FAST_FORWARD | FORWARD_ONLY + READ_ONLY + 效能最佳化 (常用) |
| READ_ONLY | 禁止透過游標更新資料 |
建議:唯讀、單向掃描場景請使用 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
效能對比分析
| 指標 | 游標版本 | 表變數+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 進程
- 部署預存程序至目標 SQL Server
- VS 開啟方案,設中斷點於游標/迴圈關鍵行
- 瀏覽器觸發呼叫 (或 SSMS 執行)
- VS → 偵錯 → 附加至處理序 → 選
w3wp.exe (IIS 6+) 或 aspnet_wp.exe (IIS 5)
- 勾選「Common Language Runtime」,確取消「Native」
- 觸發操作,中斷點命中,F11 逐步執行檢視變數
注意:偵錯會嚴重影響效能,遇到問題請重置 IIS 並重新附加。
六、總結:選擇正確的迴圈工具
- 優先考慮集合式單一陳述式 (INSERT...SELECT, MERGE, UPDATE...JOIN) — 效能最佳、最符合關聯式思維
- 需逐列處理時:
• 固定邏輯、大量資料 → 表變數/暫存表 + WHILE (避免游標開銷)
• 動態 SQL、需反映即時變更、複雜巡覽 → FAST_FORWARD 游標
- WHILE 迴圈 適合簡單計數、條件控制流程,不直接處理結果集
- 永遠記得
CLOSE / DEALLOCATE 清理游標資源
copyright ©
萬盛學電腦網 all rights reserved