【轉】SQL SERVER 游標

什么是游標

結果集，結果集就是select查詢之后返回的所有行數據的集合。

游標則是處理結果集的一種機制吧，它可以定位到結果集中的某一行，多數據進行讀寫，也可以移動游標定位到你所需要的行中進行操作數據。

一般復雜的存儲過程，都會有游標的出現，他的用處主要有：

定位到結果集中的某一行。
對當前位置的數據進行讀寫。
可以對結果集中的數據單獨操作，而不是整行執行相同的操作。
是面向集合的數據庫管理系統和面向行的程序設計之間的橋梁。

游標的分類

根據游標檢測結果集變化的能力和消耗資源的情況不同，SQL Server支持的API服務器游標分為以下4種：

靜態游標: 靜態游標的結果集，在游標打開的時候建立在TempDB中，不論你在操作游標的時候，如何操作數據庫，游標中的數據集都不會變。例如你在游標打開的時候，對游標查詢的數據表數據進行增刪改，操作之后，靜態游標中select的數據依舊顯示的為沒有操作之前的數據。如果想與操作之后的數據一致，則重新關閉打開游標即可。
動態游標:這個則與靜態游標相對，滾動游標時，動態游標反應結果集中的所有更改。結果集中的行數據值、順序和成員在每次提取時都會變化。所有用戶做的增刪改語句通過游標均可見。如果使用API函數或T-SQL Where Current of子句通過游標進行更新，他們將立即可見。在游標外部所做的更新直到提交時才可見。
只進游標：只進游標不支持滾動，只支持從頭到尾順序提取數據，數據庫執行增刪改，在提取時是可見的，但由于該游標只能進不能向后滾動，所以在行提取后對行做增刪改是不可見的。
鍵集驅動游標：打開鍵集驅動游標時，該游標中的各個成員身份和順序是固定的。打開游標時，結果集這些行數據被一組唯一標識符標識，被標識的列做刪改時，用戶滾動游標是可見的，如果沒被標識的列增該，則不可見，比如insert一條數據，是不可見的，若可見，須關閉重新打開游標。

靜態游標在滾動時檢測不到表數據變化，但消耗的資源相對很少。動態游標在滾動時能檢測到所有表數據變化，但消耗的資源卻較多。鍵集驅動游標則處于他們中間，所以根據需求建立適合自己的游標，避免資源浪費。。

游標的生命周期

游標的生命周期包含有五個階段：聲明游標、打開游標、讀取游標數據、關閉游標、釋放游標。

1.聲明游標，語法

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：游標名稱。
Local：作用域為局部，只在定義它的批處理，存儲過程或觸發器中有效。
Global：作用域為全局，由連接執行的任何存儲過程或批處理中，都可以引用該游標。
[Local | Global]:默認為local。
Forward_Only:指定游標只能從第一行滾到最后一行。Fetch Next是唯一支持的提取選項。如果在指定Forward_Only是不指定Static、KeySet、Dynamic關鍵字，默認為Dynamic游標。如果Forward_Only和Scroll沒有指定，Static、KeySet、Dynamic游標默認為Scroll，Fast_Forward默認為Forward_Only
Static:靜態游標
KeySet：鍵集游標
Dynamic：動態游標，不支持Absolute提取選項
Fast_Forward：指定啟用了性能優化的Forward_Only、Read_Only游標。如果指定了Scroll或For_Update，就不能指定他啦。
Read_Only:不能通過游標對數據進行刪改。
Scroll_Locks：將行讀入游標是，鎖定這些行，確保刪除或更新一定會成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
Optimistic：指定如果行自讀入游標以來已得到更新，則通過游標進行的定位更新或定位刪除不成功。當將行讀入游標時，sqlserver不鎖定行，它改用timestamp列值的比較結果來確定行讀入游標后是否發生了修改，如果表不行timestamp列，它改用校驗和值進行確定。如果已修改改行，則嘗試進行的定位更新或刪除將失敗。如果指定啦Fast_Forward,則不能指定他。
Type_Warning:指定將游標從所請求的類型隱式轉換為另一種類型時向客戶端發送警告信息。
For Update[of column_name ,....] :定義游標中可更新的列。

2.聲明一個動態游標

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

3.打開游標

--打開游標語法
open [ Global ] cursor_name | cursor_variable_name

cursor_name:游標名，cursor_variable_name：游標變量名稱，該變量引用了一個游標。

--打開游標
open orderNum_02_cursor

4.提取數據

--提取游標語法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]

參數說明：

Frist：結果集的第一行
Prior：當前位置的上一行
Next：當前位置的下一行
Last：最后一行
Absoute n：從游標的第一行開始數，第n行。
Relative n：從當前位置數，第n行。
Into @variable_name[,...] : 將提取到的數據存放到變量variable_name中。

例子：

--提取數據
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'

結果（對比一下，就明白啦）：

例子：

--提取數據賦值給變量
declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
select @OrderId as id
select * from bigorder where orderNum='ZEORD003402'

結果：

通過檢測全局變量@@Fetch_Status的值，獲得提取狀態信息，該狀態用于判斷Fetch語句返回數據的有效性。當執行一條Fetch語句之后，@@Fetch_Status可能出現3種值：0，Fetch語句成功。-1：Fetch語句失敗或行不在結果集中。-2：提取的行不存在。

這個狀態值可以幫你判斷提取數據的成功與否。

declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
while @@fetch_status=0  --提取成功，進行下一條數據的提取操作
 begin
   select @OrderId as id
   fetch  next from orderNum_02_cursor into @OrderId  --移動游標
 end

5.利用游標更新刪除數據

--游標修改當前數據語法
Update 基表名 Set 列名=值[,...] Where Current of 游標名
--游標刪除當前數據語法
Delete 基表名  Where Current of 游標名

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

6.關閉游標

游標打開后，服務器會專門為游標分配一定的內存空間存放游標操作的數據結果集，同時使用游標也會對某些數據進行封鎖。所以游標一旦用過，應及時關閉，避免服務器資源浪費。

--關閉游標語法
close [ Global ] cursor_name | cursor_variable_name
--關閉游標
close orderNum_03_cursor

7.刪除游標

刪除游標，釋放資源

--釋放游標語法
deallocate  [ Global ] cursor_name | cursor_variable_name
--釋放游標
deallocate orderNum_03_cursor

轉自：http://www.rzrgm.cn/knowledgesea/p/3699851.html

posted @ 2022-06-27 21:27 竹樓風雨聲閱讀(157) 評論(0) 收藏舉報

刷新頁面返回頂部

竹樓風雨聲

【轉】SQL SERVER 游標

什么是游標

游標的分類

游標的生命周期

公告