SQL SERVER CURSOR

一：認識遊標

遊標(Cursor)它使使用者可逐行訪問由SQL Server返回的結果集。
使用遊標(cursor)的一個主要的原因就是把集合操作轉換成單個記錄處理方式。
用SQL語言從資料庫中檢索資料後，結果放在記憶體的一塊地區中，且結果往往是一個含有多個記錄的集合。
遊標機制允許使用者在SQL server內逐行地訪問這些記錄，按照使用者自己的意願來顯示和處理這些記錄。

二：遊標的基本形式

聲明遊標：形式1
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關鍵字而已。
開啟遊標
開啟遊標就是建立結果集。遊標通過DECLARE語句定義，但其實際的執行是通過OPEN語句。文法如下：
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
GLOBAL指明一個全域遊標。
Cursor_name是被開啟的遊標的名稱。
Cursor_variable_name是所引用遊標的變數名。該變數應該為遊標類型。
在遊標被開啟之後，系統變數@@cursor_rows可以用來檢測結果集的行數。@@cursor_rows為負數時，表示遊標正在被非同步遷移，其絕對值（如果@@cursor_rows為－５，則絕對值為５）為當前結果集的行數。非同步遊標使使用者在遊標被完全遷移時仍然能夠訪問遊標的結果。
從遊標中取值
在從遊標中取值的過程中，可以在結果集中的每一行上來回移動和處理。如果遊標定義成了可滾動的（在聲明時使用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可以用來構造遊標處理的迴圈。
關閉遊標
CLOSE語句用來關閉遊標並釋放結果集。遊標關閉之後，不能再執行FETCH操作。如果還需要使用FETCH語句，則要重新開啟遊標。文法如下：
CLOSE [GLOBAL] cursor_name | cursor_variable_name
釋放遊標
遊標使用不再需要之後，要釋放遊標。DEALLOCATE語句釋放資料結構和遊標所加的鎖。文法如下：
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name

三：遊標的基本使用模板

declare ： 
        declare  遊標名[scroll]  cursor  for select語句[for update [of列表名]]
        定義一個遊標，使之對應一個select語句
       for update任選項，表示該遊標可用於對當前行的修改與刪除
    open
       開啟一個遊標，執行遊標對應的查詢，結果集合為該遊標的活動集
       open  遊標名
    fetch
       在活動集中將遊標移到特定的行，並取出該行資料放到相應的變數中
       fetch [next | prior | first | last | current | relative n | absolute m] 遊標名into  [變數表]
    close
       關閉遊標，釋放活動集及其所佔資源。需要再使用該遊標時，執行open語句
       close  遊標名
    deallocate 
       刪除遊標，以後不能再對該遊標執行open語句
       deallocate 遊標名
    @@FETCH_STATUS
        返回被FETCH 語句執行的最後遊標的狀態.
       0 fetch語句成功
        -1 fetch語句失敗
        -2 被提取的行不存在

例：DECLARE Employee_Cursor CURSOR FORSELECT EmployeeID, Title 

FROM AdventureWorks.HumanResources.Employee;

OPEN Employee_Cursor;FETCH NEXT FROM Employee_Cursor;

WHILE @@FETCH_STATUS = 0   

BEGIN      

--//TO DO...　　

FETCH NEXT FROM Employee_Cursor;   

END;

CLOSE Employee_Cursor;DEALLOCATE Employee_Cursor;

GO

四：遊標效能問題

最好的改進遊標效能的技術就是：能避免時就避免使用遊標，儘可能用對應的陳述式完成相同的功能（一般情況下，考慮得當效率能大大提升）。

SQL Server是關聯式資料庫，其處理資料集比處理單行好得多，單獨行的訪問根本不適合關係DBMS。若有時無法避免使用遊標，則可以用如下技巧來最佳化遊標的效能。
(1). 除非必要否則不要使用static/insensitive遊標。開啟static遊標會造成所有的行都被拷貝到暫存資料表。這正是為什麼它對變化不敏感的原因——它實際上是指向

     臨時資料庫表中的一個備份。很自然，結果集越大，聲明其上的static遊標就會引起越多的臨時資料庫的資源爭奪問題。
(2). 除非必要否則不要使用keyset遊標。和static遊標一樣，開啟keyset遊標會建立暫存資料表。雖然這個表只包括基本表的一個關鍵字列(除非不存在唯一關鍵字)，

     但是當處理大結果集時還是會相當大的。
(3). 當處理單向的唯讀結果集時，使用fast_forward代替forward_only。使用fast_forward定義一個forward_only，則read_only遊標具有一定的內部效能最佳化。
(4). 使用read_only關鍵字定義唯讀遊標。這樣可以防止意外的修改，並且讓伺服器瞭解遊標移動時不會修改行。
(5). 小心交易處理中通過遊標進行的大量行修改。根據交易隔離等級，這些行在事務完成或復原前會保持鎖定，這可能造成伺服器上的資源爭奪。
(6). 小心動態游標的修改，尤其是建在非唯一叢集索引鍵的表上的遊標，因為他們會造成“Halloween”問題——對同一行或同一行的重複的錯誤的修改。

     因為SQL Server在內部會把某行的關鍵字修改成一個已經存在的值，並強迫伺服器追加下標，使它以後可以再結果集中移動。當從結果集的剩餘項中存取時，

     又會遇到那一行，然後程式會重複，結果造成死迴圈。
(7). 對於大結果集要考慮使用非同步遊標，儘可能地把控制權交給調用者。當返回相當大的結果集到可移動的表格時，非同步遊標特別有用，

     因為它們允許應用程式幾乎馬上就可以顯示行。

遊標(Cursor)它使使用者可逐行訪問由SQL Server返回的結果集。
使用遊標(cursor)的一個主要的原因就是把集合操作轉換成單個記錄處理方式。
用SQL語言從資料庫中檢索資料後，結果放在記憶體的一塊地區中，且結果往往是一個含有多個記錄的集合。
遊標機制允許使用者在SQL server內逐行地訪問這些記錄，按照使用者自己的意願來顯示和處理這些記錄。

二：遊標的基本形式

聲明遊標：形式1
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關鍵字而已。
開啟遊標
開啟遊標就是建立結果集。遊標通過DECLARE語句定義，但其實際的執行是通過OPEN語句。文法如下：
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
GLOBAL指明一個全域遊標。
Cursor_name是被開啟的遊標的名稱。
Cursor_variable_name是所引用遊標的變數名。該變數應該為遊標類型。
在遊標被開啟之後，系統變數@@cursor_rows可以用來檢測結果集的行數。@@cursor_rows為負數時，表示遊標正在被非同步遷移，其絕對值（如果@@cursor_rows為－５，則絕對值為５）為當前結果集的行數。非同步遊標使使用者在遊標被完全遷移時仍然能夠訪問遊標的結果。
從遊標中取值
在從遊標中取值的過程中，可以在結果集中的每一行上來回移動和處理。如果遊標定義成了可滾動的（在聲明時使用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可以用來構造遊標處理的迴圈。
關閉遊標
CLOSE語句用來關閉遊標並釋放結果集。遊標關閉之後，不能再執行FETCH操作。如果還需要使用FETCH語句，則要重新開啟遊標。文法如下：
CLOSE [GLOBAL] cursor_name | cursor_variable_name
釋放遊標
遊標使用不再需要之後，要釋放遊標。DEALLOCATE語句釋放資料結構和遊標所加的鎖。文法如下：
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name

三：遊標的基本使用模板

declare ： 
        declare  遊標名[scroll]  cursor  for select語句[for update [of列表名]]
        定義一個遊標，使之對應一個select語句
       for update任選項，表示該遊標可用於對當前行的修改與刪除
    open
       開啟一個遊標，執行遊標對應的查詢，結果集合為該遊標的活動集
       open  遊標名
    fetch
       在活動集中將遊標移到特定的行，並取出該行資料放到相應的變數中
       fetch [next | prior | first | last | current | relative n | absolute m] 遊標名into  [變數表]
    close
       關閉遊標，釋放活動集及其所佔資源。需要再使用該遊標時，執行open語句
       close  遊標名
    deallocate 
       刪除遊標，以後不能再對該遊標執行open語句
       deallocate 遊標名
    @@FETCH_STATUS
        返回被FETCH 語句執行的最後遊標的狀態.
       0 fetch語句成功
        -1 fetch語句失敗
        -2 被提取的行不存在

例：DECLARE Employee_Cursor CURSOR FORSELECT EmployeeID, Title 

FROM AdventureWorks.HumanResources.Employee;

OPEN Employee_Cursor;FETCH NEXT FROM Employee_Cursor;

WHILE @@FETCH_STATUS = 0   

BEGIN      

--//TO DO...　　

FETCH NEXT FROM Employee_Cursor;   

END;

CLOSE Employee_Cursor;DEALLOCATE Employee_Cursor;

GO

四：遊標效能問題

最好的改進遊標效能的技術就是：能避免時就避免使用遊標，儘可能用對應的陳述式完成相同的功能（一般情況下，考慮得當效率能大大提升）。

SQL Server是關聯式資料庫，其處理資料集比處理單行好得多，單獨行的訪問根本不適合關係DBMS。若有時無法避免使用遊標，則可以用如下技巧來最佳化遊標的效能。
(1). 除非必要否則不要使用static/insensitive遊標。開啟static遊標會造成所有的行都被拷貝到暫存資料表。這正是為什麼它對變化不敏感的原因——它實際上是指向

     臨時資料庫表中的一個備份。很自然，結果集越大，聲明其上的static遊標就會引起越多的臨時資料庫的資源爭奪問題。
(2). 除非必要否則不要使用keyset遊標。和static遊標一樣，開啟keyset遊標會建立暫存資料表。雖然這個表只包括基本表的一個關鍵字列(除非不存在唯一關鍵字)，

     但是當處理大結果集時還是會相當大的。
(3). 當處理單向的唯讀結果集時，使用fast_forward代替forward_only。使用fast_forward定義一個forward_only，則read_only遊標具有一定的內部效能最佳化。
(4). 使用read_only關鍵字定義唯讀遊標。這樣可以防止意外的修改，並且讓伺服器瞭解遊標移動時不會修改行。
(5). 小心交易處理中通過遊標進行的大量行修改。根據交易隔離等級，這些行在事務完成或復原前會保持鎖定，這可能造成伺服器上的資源爭奪。
(6). 小心動態游標的修改，尤其是建在非唯一叢集索引鍵的表上的遊標，因為他們會造成“Halloween”問題——對同一行或同一行的重複的錯誤的修改。

     因為SQL Server在內部會把某行的關鍵字修改成一個已經存在的值，並強迫伺服器追加下標，使它以後可以再結果集中移動。當從結果集的剩餘項中存取時，

     又會遇到那一行，然後程式會重複，結果造成死迴圈。
(7). 對於大結果集要考慮使用非同步遊標，儘可能地把控制權交給調用者。當返回相當大的結果集到可移動的表格時，非同步遊標特別有用，

     因為它們允許應用程式幾乎馬上就可以顯示行。