一：認識游標

游標是SQL Server的一種數(shù)據(jù)訪問機制，它允許用戶訪問單獨的數(shù)據(jù)行。用戶可以對每一行進行單獨的處理，從而降低系統(tǒng)開銷和潛在的阻隔情況，用戶也可以使用這些數(shù)據(jù)生成的SQL代碼并立即執(zhí)行或輸出。

1.游標的概念

游標是一種處理數(shù)據(jù)的方法，主要用于存儲過程，觸發(fā)器和 T_SQL腳本中，它們使結果集的內容可用于其它T_SQL語句。在查看或處理結果集中向前或向后瀏覽數(shù)據(jù)的功能。類似與C語言中的指針，它可以指向結果集中的任意位置，當要對結果集進行逐條單獨處理時，必須聲明一個指向該結果集中的游標變量。

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

2.游標的優(yōu)點

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

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

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

(3).游標作為數(shù)據(jù)庫管理系統(tǒng)和應用程序設計之間的橋梁，將兩種處理方式連接起來。

3.游標的分類

SQL Server支持3中游標實現(xiàn)：

(1).Transact_SQL游標

基于DECLARE CURSOR 語法，主要用于T_SQL腳本，存儲過程和觸發(fā)器。T_SQL游標在服務器上實現(xiàn)，并由從客戶端發(fā)送到服務器的T_SQL語句管理，它們還可能包含在批處理，存儲過程或觸發(fā)器中。

(2).應用程序編程接口(API)服務器游標

支持OLE DB和ODBC中的API游標函數(shù)，API服務器游標在服務器上實現(xiàn)。每次客戶端應用程序調用API游標函數(shù)時，SQL Server Native Client OLE DB訪問接口或ODBC驅動程序會把請求傳輸?shù)椒掌?，以便對API服務器游標進行操作。

(3).客戶端游標

由SQL Server Native Client ODBC驅動程序和實現(xiàn)ADO API的DLL在內部實現(xiàn)?？蛻舳擞螛送ㄟ^在客戶端高速緩存所有結果集中的行來實現(xiàn)。每次客戶端應用程序調用API游標函數(shù)時，SQL Server Native Client ODBC驅動程序或ADO DLL會對客戶端上告訴緩存的結果集中的行執(zhí)行游標操作。

由于T_SQL游標和服務器游標都在服務器上實現(xiàn)，所以它們統(tǒng)稱為服務器游標。

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

SQL Server支持的4種API服務器游標的類型是：

(i).只進游標

只進游標不支持滾動，它只支持游標從頭到尾順序提取。行只在從數(shù)據(jù)庫中提取出來后才能檢索。對所有又當前用戶發(fā)出或又其它用戶提交、并影響結果集中的行的INSERT，UPDATE和DELETE語句，其效果在這些行從游標中提取是可見的。

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

(ii).靜態(tài)游標

SQL Server靜態(tài)游標始終是只讀的。其完整結果集在打開游標時建立在tempdb中，靜態(tài)游標總是按照打開游標時的原樣顯示結果集。

游標不反映在數(shù)據(jù)庫中所做的任何影響結果集成員身份的更改，也不反映對組合成結果集的行的列值所做的更改，靜態(tài)游標不會顯示打開游標以后在數(shù)據(jù)庫中新插入的行，即使這些行符合游標SELECT語句的搜索條件。如果組成結果集的行被其它用戶更新，則新的數(shù)據(jù)值不會顯示在靜態(tài)游標中。靜態(tài)游標會顯示打開游標以后從數(shù)據(jù)中刪除的行。靜態(tài)游標中不反UPDATE、INSERT或者DELETE操作（除非關閉游標然后重新打開），甚至不反映使用打開游標的同一連接所做的修改。

(iii).由鍵驅動的游標

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

(IV).動態(tài)游標

動態(tài)游標與靜態(tài)游標相對。當滾動游標時，動態(tài)游標反映結果集中所做的所有更改。結果集中的行數(shù)據(jù)值、順序和成員在每次提取時都會改變。所有用戶做的全部UPDATE、INSERT和DELETE語句均通過游標可見。如果使用API函數(shù)（如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：對于在其中創(chuàng)建批處理、存儲過程或觸發(fā)器來說，該游標的作用域是局部的。

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這類數(shù)據(jù)庫API不同，STATIC，KEYSET和DYNAMIC T_SQL游標支持FORWARD_ONLY。

STATIC：定義一個游標，以創(chuàng)建將又該游標使用的數(shù)據(jù)臨時復本，對游標的所有請求都從tempdb中的這以臨時表中不得到應答；因此，在對該游標進行提取操作時返回的數(shù)據(jù)中不反映對基表所做的修改，并且該游標不允許修改。

KEYSET:指定當游標打開時，游標重的行的成員身份和順序已經(jīng)固定。對行進行唯一標識的鍵值內置在tempdb內一個稱為keyset的表中。

DYNAMIC:定義一個游標，以反映在滾動游標時對結果集內的各行所做的所有數(shù)據(jù)更改。行的數(shù)據(jù)值、順序和成員身份在每次提取時都會更改，動態(tài)游標不支持ABSOLUTE提取選項。

FAST_FORWARD：指定啟動了性能優(yōu)化的FORWARD_ONLY、READ_ONLY游標。如果指定了SCROLL或FOR_UPDATE,則不能指定FAST_FORWARD。

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

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

TYPE_WARNING:指定游標從所請求的類型隱式轉換為另一種類型時，向客戶端發(fā)送警告消息。

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.讀取游標中的數(shù)據(jù)

打開游標之后就可以讀取游標中的數(shù)據(jù)了，F(xiàn)ETCH命令可以讀取游標中的某一行數(shù)據(jù)。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必須是整數(shù)常量，并且@nvar的數(shù)據(jù)類型必須為int、tinyint或smallint.

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

GLOBAL：指定cursor_name是全局游標。

cursor_name：已聲明的游標的名稱。如果全局游標和局部游標都使用cursor_name作為其名稱，那么如果指定了GLOBAL，則cursor_name指的是全局游標，否則cursor_name指的是局部游標。

@cursor_variable_name：游標變量名，引用要從中進行提取操作的打開的游標。

INTO @variable_name [ ,...n ]：允許將提取操作的列數(shù)據(jù)放到局部變量中。列表中的各個變量從左到右與游標結果集中的相應列相關聯(lián)。各變量的數(shù)據(jù)類型必須與相應的結果集列的數(shù)據(jù)類型相匹配，或是結果集列數(shù)據(jù)類型所支持的隱士轉換。變量的數(shù)目必須與游標選擇列表中的列數(shù)一致。

【例】使用名稱為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 在打開游標之后，服務器會專門為游標開辟一定的內存空間存放游標操作的數(shù)據(jù)結果集，同時游標的使用也會根據(jù)具體情況對某些數(shù)據(jù)進行封鎖。所以在不使用游標的時候，可以將其關閉，以釋放游標所占用的服務器資源，關閉游標使用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 語句只刪除對游標變量名稱的引用，直到批處理、存儲過程或觸發(fā)器結束時變量離開作用域，才釋放變量。

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

DEALLOCATE cursor_fruit;

三:游標的運用

1.使用游標變量

聲明變量用DECLARE，為變量賦值可以用set或SELECT語句，對于游標變量的聲明和賦值，其操作基本相同。在具體使用時，首先要創(chuàng)建一個游標，將其打開后，將游標的值賦給游標變量，并通過FETCH語句從游標變量中讀取值，最后關閉釋放游標。

【例】聲明名稱為@varCursor的游標變量，輸入如下：

DECLARE @varCursor Cursor --聲明游標變量
DECLARE cursor_fruit CURSOR FOR --創(chuàng)建游標
SELECT f_name,f_price FROM fruits;
OPEN cursor_fruit --打開游標
SET @varCursor=cursor_fruit --為游標變量賦值
FETCH NEXT FROM @varCursor --從游標變量中讀取值
WHILE @@FETCH_STATUS=0 --判斷FETCH語句是否執(zhí)行成功
BEGIN
FETCH NEXT FROM @varCursor --讀取游標變量中的數(shù)據(jù)
END
CLOSE @varCursor --關閉游標
DEALLOCATE @varCursor; --釋放游標

2.用游標為變量賦值

在游標的操作過程中，可以使用FETCH語句將數(shù)據(jù)值存入變量，這些保持表中列值的變量可以在后面的程序中使用。

【例】創(chuàng)建游標cursor_variable,將fruits表中的記錄f_name,f_price值賦給變量@fruitName和@fruitPrice，并打印輸出。

3.用ORDER BY 子句改變游標中的執(zhí)行順序

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

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

【例】聲明名稱為cursor_order的游標，對fruits表中的記錄按照價格字段降序排列，輸入語句如下： 

4.用游標修改數(shù)據(jù)

【例】聲明整型變量@sid=101,然后聲明一個對fruits表進行操作的游標，打開該游標，使用FETCH NEXT方法來獲取游標中的每一行的數(shù)據(jù)，如果獲取到的記錄的s_id的字段值與@sid值相同，將s_id=@sid的記錄中的f_price修改為12.2，最后關閉釋放游標，輸入如下：

5.用游標刪除數(shù)據(jù)

使用游標刪除數(shù)據(jù)時，既可以刪除游標結果集中的數(shù)據(jù)，也可以刪除基本表中的數(shù)據(jù)

【例】使用游標刪除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.讀取游標中的數(shù)據(jù)
--【例】使用名稱為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    --創(chuàng)建游標
SELECT  f_name,f_price FROM fruits;
OPEN cursor_fruit  --打開游標
SET  @varCursor=cursor_fruit  --為游標變量賦值
FETCH NEXT FROM @varCursor   --從游標變量中讀取值
WHILE @@FETCH_STATUS=0   --判斷FETCH語句是否執(zhí)行成功
BEGIN
    FETCH NEXT FROM @varCursor   --讀取游標變量中的數(shù)據(jù)
END
CLOSE @varCursor    --關閉游標
DEALLOCATE @varCursor;    --釋放游標 
--2.用游標為變量賦值
--創(chuàng)建游標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子句改變游標中的執(zhí)行順序
--聲明名稱為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.用游標修改數(shù)據(jù)
--【例】聲明整型變量@sid=101,然后聲明一個對fruits表進行操作的游標，打開該游標，
--使用FETCH NEXT方法來獲取游標中的每一行的數(shù)據(jù)，
--如果獲取到的記錄的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.使用游標刪除數(shù)據(jù)
--【例】使用游標刪除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;

總結

到此這篇關于SQL Server基礎教程之游標(Cursor)的文章就介紹到這了,更多相關SQLServer游標內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論