什么是游標(biāo)

結(jié)果集，結(jié)果集就是select查詢之后返回的所有行數(shù)據(jù)的集合。

游標(biāo)則是處理結(jié)果集的一種機(jī)制吧，它可以定位到結(jié)果集中的某一行，多數(shù)據(jù)進(jìn)行讀寫(xiě)，也可以移動(dòng)游標(biāo)定位到你所需要的行中進(jìn)行操作數(shù)據(jù)。

一般復(fù)雜的存儲(chǔ)過(guò)程，都會(huì)有游標(biāo)的出現(xiàn)，他的用處主要有：

1. 定位到結(jié)果集中的某一行。

2. 對(duì)當(dāng)前位置的數(shù)據(jù)進(jìn)行讀寫(xiě)。

3. 可以對(duì)結(jié)果集中的數(shù)據(jù)單獨(dú)操作，而不是整行執(zhí)行相同的操作。

4. 是面向集合的數(shù)據(jù)庫(kù)管理系統(tǒng)和面向行的程序設(shè)計(jì)之間的橋梁。

類型：

　　1.普通游標(biāo)   只有NEXT操作

　　2.滾動(dòng)游標(biāo) 有多種操作

1.普通游標(biāo)

DECLARE @username varchar(20),@UserId varchar(100)
DECLARE cursor_name CURSOR FOR --定義游標(biāo)
    SELECT TOP 10 UserId,UserName FROM UserInfo
    ORDER BY UserId DESC
OPEN cursor_name --打開(kāi)游標(biāo)
FETCH NEXT FROM cursor_name INTO  @UserId,@username  --抓取下一行游標(biāo)數(shù)據(jù)
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '用戶ID：'+@UserId+'            '+'用戶名：'+@username
        FETCH NEXT FROM cursor_name INTO @UserId,@username
    END
CLOSE cursor_name --關(guān)閉游標(biāo)
DEALLOCATE cursor_name --釋放游標(biāo)

結(jié)果：

用戶ID：zhizhi            用戶名：鄧?guó)欀?
用戶ID：yuyu            用戶名：魏雨
用戶ID：yujie            用戶名：李玉杰
用戶ID：yuanyuan            用戶名：王夢(mèng)緣
用戶ID：YOUYOU            用戶名：lisi
用戶ID：yiyiren            用戶名：任毅
用戶ID：yanbo            用戶名：王艷波
用戶ID：xuxu            用戶名：陳佳緒
用戶ID：xiangxiang            用戶名：李慶祥
用戶ID：wenwen            用戶名：魏文文

2.滾動(dòng)游標(biāo)

用戶ID：zhizhi            用戶名：鄧?guó)欀?br/>用戶ID：yuyu            用戶名：魏雨
用戶ID：yujie            用戶名：李玉杰
用戶ID：yuanyuan            用戶名：王夢(mèng)緣
用戶ID：YOUYOU            用戶名：lisi
用戶ID：yiyiren            用戶名：任毅
用戶ID：yanbo            用戶名：王艷波
用戶ID：xuxu            用戶名：陳佳緒
用戶ID：xiangxiang            用戶名：李慶祥
用戶ID：wenwen            用戶名：魏文文

結(jié)果（可以參考第一個(gè)結(jié)果分析）：

具體FETCH用法：

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

游標(biāo)的分類

根據(jù)游標(biāo)檢測(cè)結(jié)果集變化的能力和消耗資源的情況不同，SQL Server支持的API服務(wù)器游標(biāo)分為一下4種：

• 靜態(tài)游標(biāo): 靜態(tài)游標(biāo)的結(jié)果集，在游標(biāo)打開(kāi)的時(shí)候建立在TempDB中，不論你在操作游標(biāo)的時(shí)候，如何操作數(shù)據(jù)庫(kù)，游標(biāo)中的數(shù)據(jù)集都不會(huì)變。例如你在游標(biāo)打開(kāi)的時(shí)候，對(duì)游標(biāo)查詢的數(shù)據(jù)表數(shù)據(jù)進(jìn)行增刪改，操作之后，靜態(tài)游標(biāo)中select的數(shù)據(jù)依舊顯示的為沒(méi)有操作之前的數(shù)據(jù)。如果想與操作之后的數(shù)據(jù)一致，則重新關(guān)閉打開(kāi)游標(biāo)即可。

• 動(dòng)態(tài)游標(biāo):這個(gè)則與靜態(tài)游標(biāo)相對(duì)，滾動(dòng)游標(biāo)時(shí)，動(dòng)態(tài)游標(biāo)反應(yīng)結(jié)果集中的所有更改。結(jié)果集中的行數(shù)據(jù)值、順序和成員在每次提取時(shí)都會(huì)變化。所有用戶做的增刪改語(yǔ)句通過(guò)游標(biāo)均可見(jiàn)。如果使用API函數(shù)或T-SQL Where Current of子句通過(guò)游標(biāo)進(jìn)行更新，他們將立即可見(jiàn)。在游標(biāo)外部所做的更新直到提交時(shí)才可見(jiàn)。

• 只進(jìn)游標(biāo)：只進(jìn)游標(biāo)不支持滾動(dòng)，只支持從頭到尾順序提取數(shù)據(jù)，數(shù)據(jù)庫(kù)執(zhí)行增刪改，在提取時(shí)是可見(jiàn)的，但由于該游標(biāo)只能進(jìn)不能向后滾動(dòng)，所以在行提取后對(duì)行做增刪改是不可見(jiàn)的。
  

• 鍵集驅(qū)動(dòng)游標(biāo)：打開(kāi)鍵集驅(qū)動(dòng)游標(biāo)時(shí)，該有表中的各個(gè)成員身份和順序是固定的。打開(kāi)游標(biāo)時(shí)，結(jié)果集這些行數(shù)據(jù)被一組唯一標(biāo)識(shí)符標(biāo)識(shí)，被標(biāo)識(shí)的列做刪改時(shí)，用戶滾動(dòng)游標(biāo)是可見(jiàn)的，如果沒(méi)被標(biāo)識(shí)的列增該，則不可見(jiàn)，比如insert一條數(shù)據(jù)，是不可見(jiàn)的，若可見(jiàn)，須關(guān)閉重新打開(kāi)游標(biāo)。

靜態(tài)游標(biāo)在滾動(dòng)時(shí)檢測(cè)不到表數(shù)據(jù)變化，但消耗的資源相對(duì)很少。動(dòng)態(tài)游標(biāo)在滾動(dòng)時(shí)能檢測(cè)到所有表數(shù)據(jù)變化，但消耗的資源卻較多。鍵集驅(qū)動(dòng)游標(biāo)則處于他們中間，所以根據(jù)需求建立適合自己的游標(biāo)，避免資源浪費(fèi)。。

游標(biāo)的生命周期

游標(biāo)的生命周期包含有五個(gè)階段：聲明游標(biāo)、打開(kāi)游標(biāo)、讀取游標(biāo)數(shù)據(jù)、關(guān)閉游標(biāo)、釋放游標(biāo)。

 1.聲明游標(biāo)，語(yǔ)法

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

參數(shù)說(shuō)明：

• cursor_name：游標(biāo)名稱。

• Local：作用域?yàn)榫植?，只在定義它的批處理，存儲(chǔ)過(guò)程或觸發(fā)器中有效。

• Global：作用域?yàn)槿?，由連接執(zhí)行的任何存儲(chǔ)過(guò)程或批處理中，都可以引用該游標(biāo)。

• [Local | Global]:默認(rèn)為local。

• Forward_Only:指定游標(biāo)智能從第一行滾到最后一行。Fetch Next是唯一支持的提取選項(xiàng)。如果在指定Forward_Only是不指定Static、KeySet、Dynamic關(guān)鍵字，默認(rèn)為Dynamic游標(biāo)。如果Forward_Only和Scroll沒(méi)有指定，Static、KeySet、Dynamic游標(biāo)默認(rèn)為Scroll，F(xiàn)ast_Forward默認(rèn)為Forward_Only

• Static:靜態(tài)游標(biāo)

• KeySet：鍵集游標(biāo)

• Dynamic：動(dòng)態(tài)游標(biāo)，不支持Absolute提取選項(xiàng)

• Fast_Forward：指定啟用了性能優(yōu)化的Forward_Only、Read_Only游標(biāo)。如果指定啦Scroll或For_Update，就不能指定他啦。

• Read_Only:不能通過(guò)游標(biāo)對(duì)數(shù)據(jù)進(jìn)行刪改。

• Scroll_Locks：將行讀入游標(biāo)是，鎖定這些行，確保刪除或更新一定會(huì)成功。如果指定啦Fast_Forward或Static，就不能指定他啦。

• Optimistic：指定如果行自讀入游標(biāo)以來(lái)已得到更新，則通過(guò)游標(biāo)進(jìn)行的定位更新或定位刪除不成功。當(dāng)將行讀入游標(biāo)時(shí)，sqlserver不鎖定行，它改用timestamp列值的比較結(jié)果來(lái)確定行讀入游標(biāo)后是否發(fā)生了修改，如果表不行timestamp列，它改用校驗(yàn)和值進(jìn)行確定。如果已修改改行，則嘗試進(jìn)行的定位更新或刪除將失敗。如果指定啦Fast_Forward,則不能指定他。

• Type_Warning:指定將游標(biāo)從所請(qǐng)求的類型隱式轉(zhuǎn)換為另一種類型時(shí)向客戶端發(fā)送警告信息。

• For Update[of column_name ,....] :定義游標(biāo)中可更新的列。

2.聲明一個(gè)動(dòng)態(tài)游標(biāo)

declare orderNum_02_cursor cursor scroll
for select OrderId from bigorder where orderNum='ZEORD003402'

3.打開(kāi)游標(biāo)

--打開(kāi)游標(biāo)語(yǔ)法
open [ Global ] cursor_name | cursor_variable_name

cursor_name:游標(biāo)名，cursor_variable_name：游標(biāo)變量名稱，該變量引用了一個(gè)游標(biāo)。

--打開(kāi)游標(biāo)
open orderNum_02_cursor

4.提取數(shù)據(jù)

--提取游標(biāo)語(yǔ)法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]

參數(shù)說(shuō)明：

• Frist：結(jié)果集的第一行

• Prior：當(dāng)前位置的上一行

• Next：當(dāng)前位置的下一行

• Last：最后一行

• Absoute n：從游標(biāo)的第一行開(kāi)始數(shù)，第n行。

• Relative n：從當(dāng)前位置數(shù)，第n行。

• Into @variable_name[,...] : 將提取到的數(shù)據(jù)存放到變量variable_name中。

例子：

--提取數(shù)據(jù)
fetch first from orderNum_02_cursor
fetch relative 3 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch absolute 4 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch last from orderNum_02_cursor 
fetch prior from orderNum_02_cursor
select * from bigorder where orderNum='ZEORD003402'

結(jié)果（對(duì)比一下，就明白啦）：

例子：

--提取數(shù)據(jù)賦值給變量declare @OrderId intfetch absolute 3 from orderNum_02_cursor into @OrderIdselect @OrderId as idselect * from bigorder where orderNum='ZEORD003402'

結(jié)果：

通過(guò)檢測(cè)全局變量@@Fetch_Status的值，獲得提取狀態(tài)信息，該狀態(tài)用于判斷Fetch語(yǔ)句返回?cái)?shù)據(jù)的有效性。當(dāng)執(zhí)行一條Fetch語(yǔ)句之后，@@Fetch_Status可能出現(xiàn)3種值：0，F(xiàn)etch語(yǔ)句成功。-1：Fetch語(yǔ)句失敗或行不在結(jié)果集中。-2：提取的行不存在。

這個(gè)狀態(tài)值可以幫你判斷提取數(shù)據(jù)的成功與否。

declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
while @@fetch_status=0  --提取成功，進(jìn)行下一條數(shù)據(jù)的提取操作
 begin
   select @OrderId as id
   fetch  next from orderNum_02_cursor into @OrderId  --移動(dòng)游標(biāo)
 end

5.利用游標(biāo)更新刪除數(shù)據(jù) 

--游標(biāo)修改當(dāng)前數(shù)據(jù)語(yǔ)法
Update 基表名 Set 列名=值[,...] Where Current of 游標(biāo)名
--游標(biāo)刪除當(dāng)前數(shù)據(jù)語(yǔ)法
Delete 基表名  Where Current of 游標(biāo)名

---游標(biāo)更新刪除當(dāng)前數(shù)據(jù)
---1.聲明游標(biāo)
declare orderNum_03_cursor cursor scroll
for select OrderId ,userId from bigorder where orderNum='ZEORD003402'
--2.打開(kāi)游標(biāo)
open orderNum_03_cursor
--3.聲明游標(biāo)提取數(shù)據(jù)所要存放的變量
declare @OrderId int ,@userId varchar(15)
--4.定位游標(biāo)到哪一行
fetch First from orderNum_03_cursor into @OrderId,@userId  --into的變量數(shù)量必須與游標(biāo)查詢結(jié)果集的列數(shù)相同
while @@fetch_status=0  --提取成功，進(jìn)行下一條數(shù)據(jù)的提取操作 
 begin
   if @OrderId=122182
     begin
     Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor  --修改當(dāng)前行
     end
   if @OrderId=154074
      begin
      Delete bigorder Where Current of  orderNum_03_cursor  --刪除當(dāng)前行
      end
   fetch next from orderNum_03_cursor into @OrderId ,@userId  --移動(dòng)游標(biāo)
 end

6.關(guān)閉游標(biāo)

 游標(biāo)打開(kāi)后，服務(wù)器會(huì)專門為游標(biāo)分配一定的內(nèi)存空間存放游標(biāo)操作的數(shù)據(jù)結(jié)果集，同時(shí)使用游標(biāo)也會(huì)對(duì)某些數(shù)據(jù)進(jìn)行封鎖。所以游標(biāo)一旦用過(guò)，應(yīng)及時(shí)關(guān)閉，避免服務(wù)器資源浪費(fèi)。

--關(guān)閉游標(biāo)語(yǔ)法
close [ Global ] cursor_name | cursor_variable_name
--關(guān)閉游標(biāo)
close orderNum_03_cursor

7.刪除游標(biāo)

刪除游標(biāo)，釋放資源

--釋放游標(biāo)語(yǔ)法
deallocate  [ Global ] cursor_name | cursor_variable_name
--釋放游標(biāo)
deallocate orderNum_03_cursor

• 游標(biāo)