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;

【例】聲明名稱為cursor_fruit的遊標

OPEN [ GLOBAL ] cursor_name | cursor_variable_name;

2.開啟遊標

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

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

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

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

GLreee

GLreee

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;