SQL SERVER 遊標的使用

http://www.cnitblog.com/ffan/archive/2005/10/13/3276.aspx

在這一步中，需要指定遊標的屬性和根據要求產生的結果集。有兩種方法可以指定一個遊標。
形式1（ANSI 92）
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
形式2
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關鍵字指明要為檢索到的結果集建立一個臨時拷貝，以後的資料從這個臨時拷貝中獲取。如果在後來遊標處理的過程中，原有基表中資料發生了改變，那麼它們對於該遊標而言是不可見的。這種不敏感的遊標不允許資料更改。
SCROLL關鍵字指明遊標可以在任意方向上滾動。所有的fetch選項（first、last、next、relative、absolute）都可以在遊標中使用。如果忽略該選項，則遊標只能向前滾動（next）。
Select_statement指明SQL語句建立的結果集。Transact SQL語句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在遊標宣告的選擇語句中不允許使用。
READ ONLY指明在遊標結果集中不允許進行資料修改。
UPDATE關鍵字指明遊標的結果集可以修改。
OF column_list指明結果集中可以進行修改的列。預設情況下（使用UPDATE關鍵字），所有的列都可進行修改。
LOCAL關鍵字指明遊標是區域性的，它只能在它所宣告的過程中使用。
GLOBAL關鍵字使得遊標對於整個連線全域性可見。全域性的遊標在連線啟用的任何時候都是可用的。只有當連線結束時，遊標才不再可用。
FORWARD_ONLY指明遊標只能向前滾動。
STATIC的遊標與INSENSITIVE的遊標是相同的。
KEYSET指明選取的行的順序。SQL Server將從結果集中建立一個臨時關鍵字集。如果對資料庫的非關鍵字列進行了修改，則它們對遊標是可見的。因為是固定的關鍵字集合，所以對關鍵字列進行修改或新插入列是不可見的。
DYNAMIC指明遊標將反映所有對結果集的修改。
SCROLL_LOCK是為了保證遊標操作的成功，而對修改或刪除加鎖。
OPTIMISTIC指明哪些通過遊標進行的修改或者刪除將不會成功。
注意：
· 如果在SELECT語句中使用了DISTINCT、UNION、GROUP BY語句，且在選擇中包含了聚合表示式，則遊標自動為INSENSITIVE的遊標。
· 如果基表沒有唯一的索引，則遊標建立成INSENSITIVE的遊標。
· 如果SELECT語句包含了ORDER BY，而被ORDER BY的列並非唯一的行標識，則DYNAMIC遊標將轉換成KEYSET遊標。如果KEYSET遊標不能開啟，則將轉換成INSENSITIVE遊標。使用SQL ANSI-92語法定義的遊標同樣如此，只是沒有INSENSITIVE關鍵字而已。
ii. 開啟遊標
開啟遊標就是建立結果集。遊標通過DECLARE語句定義，但其實際的執行是通過OPEN語句。語法如下：
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
GLOBAL指明一個全域性遊標。
Cursor_name是被開啟的遊標的名稱。
Cursor_variable_name是所引用遊標的變數名。該變數應該為遊標型別。
在遊標被開啟之後，系統變數@@cursor_rows可以用來檢測結果集的行數。@@cursor_rows為負數時，表示遊標正在被非同步遷移，其絕對值（如果@@cursor_rows為－５，則絕對值為５）為當前結果集的行數。非同步遊標使使用者在遊標被完全遷移時仍然能夠訪問遊標的結果。
iii. 從遊標中取值
在從遊標中取值的過程中，可以在結果集中的每一行上來回移動和處理。如果遊標定義成了可滾動的（在宣告時使用SCROLL關鍵字），則任何時候都可取出結果集中的任意行。對於非滾動的遊標，只能對當前行的下一行實施取操作。結果集可以取到區域性變數中。Fetch命令的語法如下：
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO @variable_name ][,……n]]
NEXT指明從當前行的下一行取值。
PRIOR指明從當前行的前一行取值。
FIRST是結果集的第一行。
LAST是結果集的最後一行。
ABSOLUTE n表示結果集中的第n行，該行數同樣可以通過一個區域性變數傳播。行號從0開始，所以n為0時不能得到任何行。
RELATIVE n表示要取出的行在當前行的前n行或後n行的位置上。如果該值為正數，則要取出的行在當前行前n行的位置上，如果該值為負數，則返回當前行的後n行。
INTO @cursor_variable_name表示遊標列值儲存的地方的變數列表。該列表中的變數數應該與DECLARE語句中選擇語句所使用的變數數相同。變數的資料型別也應該與被選擇列的資料型別相同。直到下一次使用FETCH語句之前，變數中的值都會一直保持。
每一次FETCH的執行都儲存在系統變數@@fetch_status中。如果FETCH成功，則@@fetch_status被設定成0。@@fetch_status為-1表示已經到達了結果集的一部分（例如，在遊標被開啟之後，基表中的行被刪除）。@@fetch_status可以用來構造遊標處理的迴圈。
例如：
DECLARE @iname char(20), @fname char(20)
OPEN author_cur
FETCH FIRST FROM author_cur INTO @iname, @fname
WHILE @@fetch_status = 0
BEGIN
IF @fname = ‘Albert’
PRINT “Found Albert Ringer”
ELSE
Print “Other Ringer”
FETCH NEXT FROM author_cur INTO @iname, @fname
END
iv. 關閉遊標
CLOSE語句用來關閉遊標並釋放結果集。遊標關閉之後，不能再執行FETCH操作。如果還需要使用FETCH語句，則要重新開啟遊標。語法如下：
CLOSE [GLOBAL] cursor_name | cursor_variable_name
v. 釋放遊標
遊標使用不再需要之後，要釋放遊標。DEALLOCATE語句釋放資料結構和遊標所加的鎖。語法如下：
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 table”+@TableName+”…”
PRINT @msg
SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”')”
EXEC (@cmd)
PRINT “ “
FETCH NEXT FROM table_cur INTO @TableName
END
DEALLOCATE table_cur
GO
下面的指令碼將為PUBS資料庫執行sp_BuildIndexes
USE pubs
GO
EXEC ap_BuildIndexes
注意：上面也是建立使用者定義的系統儲存過程的示例。

使用臨時表
臨時表是在TempDB中建立的表。臨時表的名稱都以“#”開頭。臨時表的範圍為建立臨時表的連線。因為，臨時表不能在兩個連線之間共享，一旦連線關閉，臨時表就會被丟棄。如果臨時表被建立於儲存過程之中，則臨時表的範圍在儲存過程之中，或者被該儲存過程呼叫的任何儲存過程之中。如果需要在連線之間共享臨時表，則需要使用全域性的臨時表。全域性的臨時表以“##”符號開頭，它將一直存在於資料庫中，直到SQL Server重新啟動。一旦這類臨時表建立之後，所有的使用者都可以訪問到。在臨時表上不能明確地指明許可權。 臨時表提供了儲存中間結果的能力。有時候，臨時表還能通過將一個複雜的查詢分解成兩個查詢而獲得效能的改善。這可以通過首先將第一個查詢的結果存在臨時表中，然後在第二個查詢中使用臨時表來實現。當一個大表中的某個子集在一個在座過程中使用多次時，建議使用臨時表。在這種情況下，在臨時表中保持資料的子集，以在隨後的連線中使用，這樣能大大改善效能。還可以在臨時表中建立索引。