SQL Server 7.0 入門教學（七）：游標與暫存表完整指南

游標操作完整流程

使用游標需五步驟：宣告 → 開啟 → 擷取 → 關閉 → 釋放。

1. 宣告游標

兩種語法形式：

ANSI-92 標準：

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE} [OF column_list]]

T-SQL 擴充：

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR {READ ONLY | UPDATE} [OF column_list]]

關鍵字說明：

• INSENSITIVE/STATIC：建立結果集暫存複本，基表變更不可見，不允許修改
• SCROLL：支援任意方向捲動 (FIRST, LAST, NEXT, PRIOR, ABSOLUTE, RELATIVE)
• FORWARD_ONLY：僅能向前捲動
• KEYSET：固定鍵值集合，非鍵值欄變更可見，鍵值修改/新增不可見
• DYNAMIC：反映所有結果集變更
• SCROLL_LOCKS：鎖定修改/刪除列確保操作成功
• OPTIMISTIC：不鎖定，若資料已變更則修改/刪除失敗
• LOCAL/GLOBAL：作用域限於批次/連線全域

自動轉換規則： 含 DISTINCT/UNION/GROUP BY/彙總函數、基表無唯一索引、ORDER BY 非唯一鍵時，游標會自動轉為 INSENSITIVE/KEYSET 類型。

2. 開啟游標

OPEN { { [GLOBAL] cursor_name } | cursor_variable_name }

開啟後 @@CURSOR_ROWS 回傳結果集列數 (負值表示非同步填入中)。

3. 擷取資料

FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]
FROM { [GLOBAL] cursor_name | cursor_variable_name }
[INTO @variable_list]

• @@FETCH_STATUS：0=成功, -1=列已刪除/超出範圍, -2=列不存在
• 可用於迴圈控制：WHILE @@FETCH_STATUS = 0

範例：

DECLARE @lname CHAR(20), @fname CHAR(20)
OPEN author_cur
FETCH FIRST FROM author_cur INTO @lname, @fname
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @fname = 'Albert' PRINT 'Found Albert'
    ELSE PRINT 'Other'
    FETCH NEXT FROM author_cur INTO @lname, @fname
END

4. 關閉游標

CLOSE [GLOBAL] cursor_name | cursor_variable_name

釋放結果集，若需重用須重新 OPEN。

5. 釋放游標

DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name

釋放資料結構與鎖定。

完整游標範例：為所有使用者表重建索引

USE master; GO
CREATE PROCEDURE sp_BuildIndexes AS
DECLARE @TableName SYSNAME, @msg VARCHAR(100), @cmd VARCHAR(100)
DECLARE table_cur CURSOR FOR SELECT name FROM sysobjects WHERE type='u'
OPEN table_cur
FETCH NEXT FROM table_cur INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@FETCH_STATUS = -2 CONTINUE
    SELECT @msg = 'Building indexes for ' + @TableName + '...'
    PRINT @msg
    SELECT @cmd = 'DBCC DBREINDEX (''' + @TableName + ''')'
    EXEC (@cmd)
    PRINT ''
    FETCH NEXT FROM table_cur INTO @TableName
END
DEALLOCATE table_cur; GO

-- 執行
USE pubs; GO
EXEC sp_BuildIndexes; GO

暫存表應用

• 名稱以 # 開頭，儲存於 tempdb
• 作用域：連線層級 (連線關閉自動銷毀)；若在預存程序建立，作用域為該程序及其呼叫的子程序
• 全域暫存表以 ## 開頭，存活至 SQL Server 重啟，所有使用者可存取
• 不可明確設定權限

效能優勢： 儲存中繼結果、將複雜查詢分解、大表子集多次使用時避免重複掃描、可建立索引進一步提升效能。