SQL Server遊標

張龍豪發表於2014-05-05
SQLServer

什麼是遊標

結果集，結果集就是select查詢之後返回的所有行資料的集合。

遊標則是處理結果集的一種機制吧，它可以定位到結果集中的某一行，多資料進行讀寫，也可以移動遊標定位到你所需要的行中進行運算元據。

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

  1. 定位到結果集中的某一行。
  2. 對當前位置的資料進行讀寫。
  3. 可以對結果集中的資料單獨操作，而不是整行執行相同的操作。
  4. 是面向集合的資料庫管理系統和麵向行的程式設計之間的橋樑。

遊標的分類

根據遊標檢測結果集變化的能力和消耗資源的情況不同，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

 

 

 

相關文章