【SqlServer】SqlServer程式設計語言T-SQL的遊標使用

標籤：使用者   bar   隱式轉換   first   定位   分配   set   size   獲得   

什麼是遊標

結果集，結果集就是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 scrollfor 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_cursorfetch relative 3 from orderNum_02_cursorfetch next from orderNum_02_cursorfetch absolute 4 from orderNum_02_cursorfetch next from orderNum_02_cursorfetch last from orderNum_02_cursor fetch prior from orderNum_02_cursorselect * from bigorder where orderNum=‘ZEORD003402‘

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

例子：

--提取資料賦值給變數declare @OrderId intfetch absolute 3 from orderNum_02_cursor into @OrderIdselect @OrderId as idselect * from bigorder where orderNum=‘ZEORD003402‘

結果：

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

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

declare @OrderId intfetch absolute 3 from orderNum_02_cursor into @OrderIdwhile @@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 scrollfor 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

【SqlServer】SqlServer程式設計語言T-SQL的遊標使用