SqlServer2008執行個體50遊标

    有程式設計背景的查詢編寫者通常更習慣于使用遊标，而不是基于集合的方案來擷取或更新行。例如，程式員可能會決定一次周遊一行，一條一條地更新行，而不是用一個操作來更新整個行集。但是，遊标會耗盡SQL Server執行個體的記憶體、減少并發性、減少網絡帶寬、鎖定資源，并且經常會需要比基于集合的方案更多的代碼。T-SQL是基于集合的語言，也就是說它擅長操作和擷取行集，而不是一行一行地處理。

    然而，應用程式或業務需求可能需要T-SQL遊标提供的一行一行的處理方式。通常，應該在其他諸如WHILE循環、子查詢、臨時表或表變量等其他行級别的處理方法都不管用的時候才應該考慮使用遊标。

    T-SQL遊标的大緻生存周期如下所示。

•     遊标通過傳回有效結果集的SQL語句來定義。
•     然後遊标被填充（打開）。
•     一旦打開，行就能從遊标中擷取，一次一個或一組。行還能向前或向後移動，這取決于原始遊标的定義。
•    根據遊标類型，資料能随着行的滾動進行修改，或讀取并用在其他操作中。
•    最後，遊标使用後應該顯式關閉并從記憶體中釋放。 

DECLARE cursor_name CURSOR 
     [ LOCAL | GLOBAL ]   
     [ FORWARD_ONLY | SCROLL ]   
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
     [ TYPE_WARNING ]   
     FOR select_statement   
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]  
           

    有一些選項會影響遊标資料是否能更新以及你是否可以向後向前移動遊标中填充的行。

        遊标選項

參  數	描  述
LOCAL或GLOBAL	如果選擇了LOCAL，遊标隻在SQL批處理、觸發器或存儲過程的範圍中可用。如果選擇了GLOBAL，遊标對連接配接自身可用（例如，一個執行建立遊标的存儲過程的連接配接能使用通過執行存 儲過程建立的遊标）
FORWARD ONLY或SCROLL	FORWARO ONLY選項隻允許我們從遊标第一行開始向前移動。而SCROLL允許我們使用所有的FETCH選項（FIRST、LAST、NEXT、PRIOR、ABSOLUTE和RELATIVE）在結果集中前後移動。如果考慮性能，最好使用FORWARD_ ONLY——因為這種遊标類型的負載比SCROLL低
STATIC或KEYSET或DYNAMLC或 FASTFORWARD	如果指定了STATIC．則遊标資料的快照會儲存在DYNAMIC或FAST FORWARD tempdb資料庫中，任何對原始資料源的修改不會影響遊标資料。KEYSET允許我們看到遊标外部對行的修改，但是看不到符合遊标SELECT查詢的插入或遊标打開之後的删除．DYNAMIC允許我們在遊标打開對看到基礎資料源的更新、插入和删除。FAST_FORWARD定義兩種行為：設定遊标為隻讀和隻向前狀态（通常這是性能最好的遊标選項．但是也最不靈活）。如果面臨性能問題．并且你的功能不複雜．則使用這個選項
READ_ONLY或 SCROLL LOCKS或 OPTIMISTIC	READ ONLY選項意味着不能通過遊标進行更新。如粜考慮性能和并發，則使用這個選項．SCROLL_LOCKs在行上加鎖，這樣就能保證在遊标結束後進行更新和删除。OPTIMISTIC選項在已更新或已删除的行上不加鎖，隻有當上次資料讀取之後遊标外部沒有進行資料更新時才會使修改有效
TYPE WARNINGS	如果指定了TYPE WARNINGS，且遊标顯式地從一種類型轉換成了另一種類型，那麼會有警告被發送到用戶端

    select_statement參數是用來在遊标中定義資料的查詢。避免使用查詢來傳回很多用不到的列和行．因為遊标打開的時候會放在記憶體中。UPDATE [ OF column_name [ ,...n ] ]用來指定那些允許遊标更新的列。

    一旦遊标使用DECLARE CURSOR來聲明，下一步就足使用OPEN指令打開它并填充資料。文法如下：    

OPEN { [ GLOBAL ] cursor_name }
           

    遊标能在局部（預設）或全局打開。一旦打開了，你就可以使用FETCH指令在遊标中切換行。FETCH NEXT的文法如下：   

FETCH   
          [ [ NEXT | PRIOR | FIRST | LAST   
                    | ABSOLUTE { n | @nvar }   
                    | RELATIVE { n | @nvar }   
               ]   
               FROM   
          ]   
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }   
[ INTO @variable_name [ ,...n ] ]   
           

    FETCH提供了幾個選項用來在遊标中切換行，通過把每個遊标定義的列的結果填充到局部變最來實作。

    @@FETCH_STATUS函數用在FETCH操作後，用來檢測FETCH狀态，傳回0表示成功，-1表示不成功，-2表示丢失。

    一旦用完了打開的遊标，就執行CLOSE指令從記憶體中釋放結果集。文法如下：   

CLOSE { [GLOBAL ] cursor_name }
           

    現在，仍然可以重新打開遊标。然而，如果已經用完了，應該使用DEALLOCATE指令移除内部系統對遊标的引用。這樣會釋放遊标使用的所有資源。例如，如果遊标的卷鎖被表引用，這些鎖在OEALLOCATE後釋放。文法如下：    

DEALLOCATE { [GLOBAL ] cursor_name }
           

示例：建立和使用T-SQL遊标

USE AdventureWorks
GO

--不在結果集中顯示行數
SET NOCOUNT ON
DECLARE @session_id smallint

--聲明遊标
DECLARE session_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT session_id
    FROM sys.dm_exec_requests
    WHERE status IN ('runnable','sleeping','running')
    
--打開遊标
OPEN session_cursor

--從遊标中一次檢索一行
FETCH NEXT
FROM session_cursor
INTO @session_id

--當存在遊标就持續檢索行
WHILE @@FETCH_STATUS=0
BEGIN
	PRINT 'Spid #: ' +STR(@session_id)
	EXEC ('DBCC OUTPUTBUFFER (' + @session_id + ')')
	
	--提取下一行
	FETCH NEXT
	FROM session_cursor
	INTO @session_id
END

--關閉遊标
CLOSE session_cursor

--釋放遊标
DEALLOCATE session_cursor