SQL Server基礎之遊標

查詢語句可能返回多條記錄，如果資料量非常大，需要使用遊標來逐條讀取查詢結果集中的記錄。應用程式可以根據需要滾動或瀏覽其中的資料。本篇介紹遊標的概念、分類、以及基本操作等內容。

一：認識遊標

  遊標是SQL Server的一種資料訪問機制，它允許使用者訪問單獨的資料行。使用者可以對每一行進行單獨的處理，從而降低系統開銷和潛在的阻隔情況，使用者也可以使用這些資料生成的SQL程式碼並立即執行或輸出。

1.遊標的概念

 遊標是一種處理資料的方法，主要用於儲存過程，觸發器和 T_SQL指令碼中，它們使結果集的內容可用於其它T_SQL語句。在檢視或處理結果集中向前或向後瀏覽資料的功能。類似與C語言中的指標，它可以指向結果集中的任意位置，當要對結果集進行逐條單獨處理時，必須宣告一個指向該結果集中的遊標變數。

 SQL Server 中的資料操作結果都是面向集合的，並沒有一種描述表中單一記錄的表達形式，除非使用WHERE子句限定查詢結果，使用遊標可以提供這種功能，並且遊標的使用和操作過程更加靈活、高效。

2.遊標的優點

 SELECT 語句返回的是一個結果集，但有時候應用程式並不總是能對整個結果集進行有效地處理，遊標便提供了這樣一種機制，它能從包括多條記錄的結果集中每次提取一條記錄，遊標總是與一跳SQL選擇語句相關聯，由結果集和指向特定記錄的遊標位置組成。使用遊標具有一下優點：

(1).允許程式對由SELECT查詢語句返回的行集中的每一次執行相同或不同的操作，而不是對整個集合執行同一個操作。

(2).提供對基於遊標位置中的行進行刪除和更新的能力。

(3).遊標作為資料庫管理系統和應用程式設計之間的橋樑，將兩種處理方式連線起來。

3.遊標的分類

  SQL Server支援3中游標實現：

(1).Transact_SQL遊標

  基於DECLARE CURSOR 語法，主要用於T_SQL指令碼，儲存過程和觸發器。T_SQL遊標在伺服器上實現，並由從客戶端傳送到伺服器的T_SQL語句管理，它們還可能包含在批處理，儲存過程或觸發器中。

(2).應用程式程式設計介面(API)伺服器遊標

  支援OLE DB和ODBC中的API遊標函式，API伺服器遊標在伺服器上實現。每次客戶端應用程式呼叫API遊標函式時，SQL Server Native Client OLE DB訪問介面或ODBC驅動程式會把請求傳輸到伺服器，以便對API伺服器遊標進行操作。

(3).客戶端遊標

  由SQL Server Native Client ODBC驅動程式和實現ADO API的DLL在內部實現。客戶端遊標通過在客戶端快取記憶體所有結果集中的行來實現。每次客戶端應用程式呼叫API遊標函式時，SQL Server Native Client ODBC驅動程式或ADO DLL會對客戶端上告訴快取的結果集中的行執行遊標操作。

  由於T_SQL遊標和伺服器遊標都在伺服器上實現，所以它們統稱為伺服器遊標。

  ODBC和ADO定義了 Microsoft SQL Server 支援的4種遊標型別，這樣就可以為T_SQL遊標指定4種遊標型別。

SQL Server支援的4種API伺服器遊標的型別是：

(i).只進遊標

 只進遊標不支援滾動，它只支援遊標從頭到尾順序提取。行只在從資料庫中提取出來後才能檢索。對所有又當前使用者發出或又其它使用者提交、並影響結果集中的行的INSERT，UPDATE和DELETE語句，其效果在這些行從遊標中提取是可見的。

 由於遊標無法向後滾動，則在提取行後對資料庫中的行進行的大多數更改通過遊標均不可見。當值用於確定所修改的結果集(例如更新聚集索引涵蓋的列)中行的位置時，修改後的值通過遊標可見。

(ii).靜態遊標

  SQL Server靜態遊標始終是隻讀的。其完整結果集在開啟遊標時建立在tempdb中，靜態遊標總是按照開啟遊標時的原樣顯示結果集。

  遊標不反映在資料庫中所做的任何影響結果整合員身份的更改，也不反映對組合成結果集的行的列值所做的更改，靜態遊標不會顯示開啟遊標以後在資料庫中新插入的行，即使這些行符合遊標SELECT語句的搜尋條件。如果組成結果集的行被其它使用者更新，則新的資料值不會顯示在靜態遊標中。靜態遊標會顯示開啟遊標以後從資料中刪除的行。靜態遊標中不反UPDATE、INSERT或者DELETE操作（除非關閉遊標然後重新開啟），甚至不反映使用開啟遊標的同一連線所做的修改。

(iii).由鍵驅動的遊標

 該遊標中各行的成員身份和順序是固定的。由鍵集驅動的遊標由一組唯一識別符號(鍵)控制，這組鍵成為鍵集。鍵是根據以唯一方式標識結果集各行的一組列生成的，鍵集是開啟遊標時來自符合SELECT語句要求的所有行中的一組鍵值。由鍵集驅動的遊標對應的鍵集是開啟遊標時在tempdb中生成的。

(IV).動態遊標

 動態遊標與靜態遊標相對。當滾動遊標時，動態遊標反映結果集中所做的所有更改。結果集中的行資料值、順序和成員在每次提取時都會改變。所有使用者做的全部UPDATE、INSERT和DELETE語句均通過遊標可見。如果使用API函式（如SQLSePos）或T_SQL WHERE CURRENT OF 子句通過遊標進行更新，它們將立即可見。在遊標外部所做的更新直到提交時才可見，除非將遊標的事物隔離級別設為未提交讀。

二：遊標的基本操作

1.宣告遊標

 遊標主要包括遊標結果集和遊標位置兩部分，遊標結果集是定義遊標的SELECT語句返回的行集合，遊標位置則是指向這個結果集中的某一行的指標。

 使用遊標之前，要宣告遊標，SQL Server中宣告使用DECLARE CURSOR語句，宣告遊標包括定義遊標的滾動行為和使用者生成遊標所操作的結果集的查詢，其語法格式如下：

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] ] ]

cursor_name:是所定義的T_SQL 伺服器遊標的名稱。

LOCAL：對於在其中建立批處理、儲存過程或觸發器來說，該遊標的作用域是區域性的。

GLOBAL：指定該遊標的作用域是全域性的

FORWARD_ONLY:指定遊標只能從第一行滾動到最後一行。FETCH NEXT是唯一支援的提取選項，如果在指定FORWARD_ONLY時不指定STATIC，KEYSET和DYNAMIC關鍵字，則遊標作為DYNAMIC遊標進行操作，如果FORWARD_ONLY和SCROLL均為指定，則除非指定STATIC，KEYSET和DYNAMIC關鍵字，否則預設為FORWARD_ONLY。STATIC，KEYSET和DYNAMIC遊標預設為SCROLL。與ODBC和ADO這類資料庫API不同，STATIC，KEYSET和DYNAMIC T_SQL遊標支援FORWARD_ONLY。

STATIC：定義一個遊標，以建立將又該遊標使用的資料臨時複本，對遊標的所有請求都從tempdb中的這以臨時表中不得到應答；因此，在對該遊標進行提取操作時返回的資料中不反映對基表所做的修改，並且該遊標不允許修改。

KEYSET:指定當遊標開啟時，遊標重的行的成員身份和順序已經固定。對行進行唯一標識的鍵值內建在tempdb內一個稱為keyset的表中。

DYNAMIC:定義一個遊標，以反映在滾動遊標時對結果集內的各行所做的所有資料更改。行的資料值、順序和成員身份在每次提取時都會更改，動態遊標不支援ABSOLUTE提取選項。

FAST_FORWARD：指定啟動了效能優化的FORWARD_ONLY、READ_ONLY遊標。如果指定了SCROLL或FOR_UPDATE,則不能指定FAST_FORWARD。

SCROLL_LOCKS：指定通過遊標進行的定位更新或刪除一定會成功。將行讀入遊標時SQL Server將鎖定這些行，以確保隨後可對它們進行修改，如果還指定了FAST_FORWARD或STATIC，則不能指定SCROLL_LOCKS。

OPTIMISTIC：指定如果行自讀入遊標以來已得到更新，則通過遊標進行的定位更新或定位刪除不成功。當將行讀入遊標時，SQL Server不鎖定行，它改用timestamp列值比較結果來確定行讀入遊標後是否發生了修改，如果表不包含timestamp列，它改用校驗和值進行確定，如果以修改該行，則嘗試進行的定位更新或刪除將失敗，如果還指定了FAST_FORWARD，則不能指定OPTIMISTIC。

TYPE_WARNING:指定遊標從所請求的型別隱式轉換為另一種型別時，向客戶端傳送警告訊息。

select_statement：是定義遊標結果集中的標準SELECT語句。

【例】宣告名稱為cursor_fruit的遊標

USE sample_db;
GO
DECLARE cursor_fruit CURSOR FOR
SELECT f_name,f_price FROM fruits;

2.開啟遊標

 在使用遊標之前必須先開啟遊標，開啟遊標的語法如下：

 OPEN [ GLOBAL ] cursor_name | cursor_variable_name;

GLOBAL:指定cursor_name是全域性遊標。

cursor_name：已宣告的遊標的名稱。如果全域性遊標和區域性遊標都使用cursor_name作為其名稱，那麼如果指定了GLOBAL，則cursor_name指的是全域性遊標，否則cursor_name指的是區域性遊標。

cursor_variable_name：遊標變數的名稱。

【例】開啟上例宣告的名稱為cursor_fruit的遊標  

USE sample_db;
GO
OPEN cursor_fruit;

3.讀取遊標中的資料

 開啟遊標之後就可以讀取遊標中的資料了，FETCH命令可以讀取遊標中的某一行資料。FETCH的語法如下：

ETCH 
[ [ NEXT | PRIOR | FIRST | LAST 

         | ABSOLUTE { n | @nvar }

         | RELATIVE { n | @nvar }
    ]
FROM
]
{ { [GLOBAL ] cursor_name } | @cursor_variable_name}
[ INTO @variable_name [ ,...n ] ]

NEXT:緊跟當前行返回結果行，並且當前行遞增為返回行，如果FETCH NEXT為對遊標的第一次提取操作，則返回結果集中的第一行。NEXT為預設的遊標提取選項。

PRIOR：返回緊鄰當前行前面的結果行，並且當前行遞減為返回行，如果FETCH PRIOR為對遊標的第一次提取操作，則沒有行返回並且遊標置於第一行之前。

FIRST：返回遊標中的第一行並將其作為當前行。

LAST:返回遊標中的最後一行並將其作為當前行。

ABSOLUTE { n | @nvar }：如果n或@nvar為正，則返回從遊標頭開始向後n行的第n行，並將返回行變成新的當前行。如果n或@nvar為負，則返回從遊標末尾開始向前的n行的第n行，並將返回行變成新的當前行。如果n或@nvar為0，則不返回行。n必須是整數常量，並且@nvar的資料型別必須為int、tinyint或smallint.

RELATIVE { n | @nvar }：如果n或@nvar為正，則返回從當前行開始向後的第n行。如果n或@nvar為負，則返回從當前行開始向前的第n行。如果n或@nvar為0，則返回當前行,對遊標第一次提取時，如果在將n或@nvar設定為負數或0的情況下指定FETCH RELATIVE,則不返回行，n必須是整數常量，@nvar的資料型別必須是int、tinyint或smallint.

GLOBAL：指定cursor_name是全域性遊標。

cursor_name：已宣告的遊標的名稱。如果全域性遊標和區域性遊標都使用cursor_name作為其名稱，那麼如果指定了GLOBAL，則cursor_name指的是全域性遊標，否則cursor_name指的是區域性遊標。

@cursor_variable_name：遊標變數名，引用要從中進行提取操作的開啟的遊標。

INTO @variable_name [ ,...n ]：允許將提取操作的列資料放到區域性變數中。列表中的各個變數從左到右與遊標結果集中的相應列相關聯。各變數的資料型別必須與相應的結果集列的資料型別相匹配，或是結果集列資料型別所支援的隱士轉換。變數的數目必須與遊標選擇列表中的列數一致。

【例】使用名稱為cursor_fruit的游標，檢索fruits表中的記錄，輸入如下： 

USE sample_db;
GO
FETCH NEXT FROM cursor_fruit
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM cursor_fruit
END;

4.關閉遊標

 SQL Server 在開啟遊標之後，伺服器會專門為遊標開闢一定的記憶體空間存放遊標操作的資料結果集，同時遊標的使用也會根據具體情況對某些資料進行封鎖。所以在不使用遊標的時候，可以將其關閉，以釋放遊標所佔用的伺服器資源，關閉遊標使用CLOSE語句。語法格式如下： 

CLOSE [ GLOBAL ] cursor_name | cursor_variable_name

【例】關閉名稱為cursor_fruit的遊標

CLOSE cursor_fruit;　　

5.釋放遊標

 遊標操作的結果集空間雖然被釋放了，但是遊標本身也會佔用一定的計算集資源，所以使用完遊標之後，為了收回被遊標佔用的資源，應該將遊標釋放。釋放遊標使用DEALLOCATE語句，語法格式如下：

 DEALLOCATE [GLOBAL] cursor_name | @ccursor_variable_name

@ccursor_variable_name：遊標變數的名稱，@ccursor_variable_name必須為cursor型別。

DEALLOCATE @ccursor_variable_name 語句只刪除對遊標變數名稱的引用，直到批處理、儲存過程或觸發器結束時變數離開作用域，才釋放變數。

【例】使用DEALLOCATE語句釋放名稱為cursor_fruit的變數，輸入如下：

 DEALLOCATE cursor_fruit;

三:遊標的運用

1.使用遊標變數

 宣告變數用DECLARE，為變數賦值可以用set或SELECT語句，對於遊標變數的宣告和賦值，其操作基本相同。在具體使用時，首先要建立一個遊標，將其開啟後，將遊標的值賦給遊標變數，並通過FETCH語句從遊標變數中讀取值，最後關閉釋放遊標。

【例】宣告名稱為@varCursor的遊標變數，輸入如下：

DECLARE @varCursor Cursor --宣告遊標變數

DECLARE cursor_fruit CURSOR FOR --建立遊標

SELECT f_name,f_price FROM fruits;

OPEN cursor_fruit --開啟遊標

SET @varCursor=cursor_fruit --為遊標變數賦值

FETCH NEXT FROM @varCursor --從遊標變數中讀取值

WHILE @@FETCH_STATUS=0 --判斷FETCH語句是否執行成功

BEGIN

FETCH NEXT FROM @varCursor --讀取遊標變數中的資料

END

CLOSE @varCursor --關閉遊標

DEALLOCATE @varCursor; --釋放遊標

2.用遊標為變數賦值

 在遊標的操作過程中，可以使用FETCH語句將資料值存入變數，這些保持表中列值的變數可以在後面的程式中使用。

【例】建立遊標cursor_variable,將fruits表中的記錄f_name,f_price值賦給變數@fruitName和@fruitPrice，並列印輸出。

3.用ORDER BY 子句改變遊標中的執行順序

  遊標是一個查詢結果集，那麼能不能對結果進行排序呢？答案是否定的。與基本的SELECT語句中的排序方法相同，ORDER BY子句新增到查詢中可以對遊標查詢的結果排序。

 注意：只有出現在遊標中的SELECT語句中的列才能作為ORDER BY 子句的排序列，而對與非遊標的SELECT語句中，表中任何列都可以作為ORDER BY 的排序列，即使該列沒有出現在SELECT語句的查詢結果列中。

【例】宣告名稱為cursor_order的遊標，對fruits表中的記錄按照價格欄位降序排列，輸入語句如下： 

4.用遊標修改資料

【例】宣告整型變數@sid=101,然後宣告一個對fruits表進行操作的遊標，開啟該遊標，使用FETCH NEXT方法來獲取遊標中的每一行的資料，如果獲取到的記錄的s_id的欄位值與@sid值相同，將s_id=@sid的記錄中的f_price修改為12.2，最後關閉釋放遊標，輸入如下：

5.用遊標刪除資料

 使用遊標刪除資料時，既可以刪除遊標結果集中的資料，也可以刪除基本表中的資料

【例】使用遊標刪除fruits表中s_id=102的記錄，如下

 以上例子的sql指令碼：

USE sample_db;
create TABLE fruits(
f_id int IDENTITY(1,1) PRIMARY KEY,--水果id
s_id int not null,        --供應商id
f_name varchar(255) not null,--水果名稱
f_price decimal(8,2) not null --水果價格
);
insert into fruits (s_id,f_name,f_price) 
        values
        (101,'apple',5.8),
        (102,'blackberry',6.8),
        (105,'orange',4.5),
        (102,'banana',3.5),
        (103,'lemon',8.0),
        (104,'grape',7.6),
        (101,'melon',10.5);
--1.宣告名稱為cursor_fruit的遊標
USE sample_db;
GO
DECLARE cursor_fruit CURSOR FOR
SELECT  f_name,f_price FROM fruits;
--2.開啟遊標
OPEN cursor_fruit;
--3.讀取遊標中的資料
--【例】使用名稱為cursor_fruit的游標，檢索fruits表中的記錄，輸入如下：
USE sample_db;
GO
FETCH NEXT  FROM cursor_fruit 
WHILE  @@FETCH_STATUS=0
BEGIN
    FETCH NEXT  FROM cursor_fruit
END;
--4.關閉關閉名稱為cursor_fruit的遊標
CLOSE cursor_fruit
--5.釋放遊標
DEALLOCATE cursor_fruit;
--遊標的運用
--1.使用遊標變數
--宣告名稱為@varCursor的遊標變數
DECLARE @varCursor Cursor  --宣告遊標變數
DECLARE cursor_fruit CURSOR FOR    --建立遊標
SELECT  f_name,f_price FROM fruits;
OPEN cursor_fruit  --開啟遊標
SET  @varCursor=cursor_fruit  --為遊標變數賦值
FETCH NEXT FROM @varCursor   --從遊標變數中讀取值
WHILE @@FETCH_STATUS=0   --判斷FETCH語句是否執行成功
BEGIN
    FETCH NEXT FROM @varCursor   --讀取遊標變數中的資料
END
CLOSE @varCursor    --關閉遊標
DEALLOCATE @varCursor;    --釋放遊標 
--2.用遊標為變數賦值
--建立遊標cursor_variable,將fruits表中的記錄f_name,f_price值賦給變數@fruitName和@fruitPrice，並列印輸出。
DECLARE @fruitName varchar(50),@fruitPrice DECIMAL(8,2)
DECLARE cursor_variable CURSOR FOR
SELECT f_name,f_price FROM fruits
WHERE s_id=101;
OPEN cursor_variable
FETCH NEXT FROM cursor_variable
INTO @fruitName,@fruitPrice
PRINT '編號為101的供應商提供的水果種類和價格為：'
WHILE @@FETCH_STATUS=0
BEGIN 
    PRINT @fruitName+' '+STR(@fruitPrice,8,2)
FETCH NEXT FROM cursor_variable
INTO @fruitName,@fruitPrice
END
CLOSE cursor_variable
DEALLOCATE cursor_variable;
--3.用ORDER BY子句改變遊標中的執行順序
--宣告名稱為cursor_order的遊標，對fruits表中的記錄按照價格欄位降序排列，輸入語句如下：
DECLARE cursor_order CURSOR FOR
SELECT f_id,f_name,f_price FROM fruits
ORDER BY f_price DESC
OPEN cursor_order
FETCH NEXT FROM cursor_order
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM cursor_order
CLOSE cursor_order
DEALLOCATE cursor_order;
--4.用遊標修改資料
--【例】宣告整型變數@sid=101,然後宣告一個對fruits表進行操作的遊標，開啟該遊標，
--使用FETCH NEXT方法來獲取遊標中的每一行的資料，
--如果獲取到的記錄的s_id的欄位值與@sid值相同，將s_id=@sid的記錄中的f_price修改為12.2，最後關閉釋放遊標，輸入如下：
DECLARE @sid INT,@id INT =101
DECLARE cursor_fruit CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_fruit
FETCH NEXT FROM cursor_fruit INTO @sid
WHILE @@FETCH_STATUS=0
BEGIN
    IF @sid=@id
BEGIN 
    UPDATE fruits SET f_price=11.1 WHERE s_id=@id
END
FETCH NEXT FROM cursor_fruit INTO @sid
END
CLOSE cursor_fruit
DEALLOCATE cursor_fruit;
SELECT * FROM fruits where s_id=101;
--5.使用遊標刪除資料
--【例】使用遊標刪除fruits表中s_id=102的記錄，如下
DECLARE @sid1 INT,@id1 int=102
DECLARE cursor_delete CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_delete
FETCH NEXT FROM cursor_delete INTO @sid1
WHILE @@FETCH_STATUS=0
BEGIN
    IF @sid1=@id1
BEGIN 
    DELETE FROM fruits where s_id=@id1
END
FETCH NEXT FROM cursor_delete INTO @sid1
END
CLOSE cursor_delete
DEALLOCATE cursor_delete;
SELECT * FROM fruits where s_id=102;