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表中的記錄，輸入如下：?

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

4.關閉游標

?SQL?Server?在打開游標之后，服務器會專門為游標開辟一定的內存空間存放游標操作的數據結果集，同時游標的使用也會根據具體情況對某些數據進行封鎖。所以在不使用游標的時候，可以將其關閉，以釋放游標所占用的服務器資源，關閉游標使用CLOSE語句。語法格式如下：?

CLOSE [ GLOBAL ] cursor_name | cursor_variable_name

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

1	`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語句是否執行成功BEGINFETCH NEXT FROM @varCursor --讀取游標變量中的數據ENDCLOSE @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
BEGINFETCH 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語句是否執行成功
BEGINFETCH 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
BEGINIF @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
BEGINIF @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;