T-SQL 遊标的使用

遊标類似于java中的循環操作。

1.定義一個遊标

遊标其實可以了解成一個定義在特定資料集上的指針，我們可以控制這個指針周遊資料集，或者僅僅是指向特定的行，是以遊标是定義在以Select開始的資料集上的:

T-SQL中的遊标定義在MSDN中如下:
           

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

2、遊标定義的參數:

遊标參數有：LOCAL和GLOBAL二選一，FORWARD_ONLY 和 SCROLL二選一，STATIC KEYSET DYNAMIC 和 FAST_FORWARD四選一，READ_ONLY SCROLL_LOCKS OPTIMISTIC 三選一

① LOCAL和GLOBAL

LOCAL意味着遊标的生存周期隻在批處理或函數或存儲過程中可見，而GLOBAL意味着遊标對于特定連接配接作為上下文，全局内有效。如果不指定遊标作用域，預設作用域為GLOBAL。

--定義全局的遊标，預設就是全局的
DECLARE test_cursor cursor global for select * from EnSummation;

--定義局部的遊标
DECLARE test_cursor2 cursor local for select * from ExSummation;
--可以用來結束上面作用域
go

open test_cursor;

--此處打開會報錯,因為test_cursor2是定義為局部的,go結束作用域後，局部的會報錯
open test_cursor2;
           

② FORWARD_ONLY 和 SCROLL

FORWARD_ONLY意味着遊标隻能從資料集開始向資料集結束的方向讀取，FETCH NEXT是唯一的選項，而SCROLL支援遊标在定義的資料集中向任何方向，或任何位置移動。

--FORWARD_ONLY意味着遊标隻能從資料集開始向資料集結束的方向讀取,隻能搭配FETCH NEXT,預設為FORWARD_ONLY
DECLARE tst_cursor cursor  FORWARD_ONLY for select * from EnSummation;

--SCROLL支援遊标在定義的資料集中向任何方向，或任何位置移動
DECLARE tst_cursor2 cursor SCROLL for select * from ExSummation;

open tst_cursor

open tst_cursor2

fetch next from tst_cursor

fetch last from tst_cursor -- 會報錯,因為隻能與fetch next一起使用

fetch next from tst_cursor2

fetch last from tst_cursor2 
           

③ STATIC KEYSET DYNAMIC 和 FAST_FORWARD 四選一

這四個關鍵字是遊标所在資料集所反應的表内資料和遊标讀取出的資料的關系

STATIC意味着，當遊标被建立時，将會建立FOR後面的SELECT語句所包含資料集的副本存入tempdb資料庫中，任何對于底層表内資料的更改不會影響到遊标的内容.

DYNAMIC是和STATIC完全相反的選項,當底層資料庫更改時，遊标的内容也随之得到反映，在下一次fetch中，資料内容會随之改變

KEYSET可以了解為介于STATIC和DYNAMIC的折中方案。将遊标所在結果集的唯一能确定每一行的主鍵存入tempdb,當結果集中任何行改變或者删除時，@@FETCH_STATUS會為-2,KEYSET無法探測新加入的資料

FAST_FORWARD可以了解成FORWARD_ONLY的優化版本.FORWARD_ONLY執行的是靜态計劃，而FAST_FORWARD是根據情況進行選擇采用動态計劃還是靜态計劃，大多數情況下FAST_FORWARD要比FORWARD_ONLY性能略好.

④ READ_ONLY SCROLL_LOCKS OPTIMISTIC 三選一

READ_ONLY意味着聲明的遊标隻能讀取資料,遊标不能做任何更新操作

SCROLL_LOCKS是另一種極端，将讀入遊标的所有資料進行鎖定，防止其他程式進行更改，以確定更新的絕對成功

OPTIMISTIC是相對比較好的一個選擇，OPTIMISTIC不鎖定任何資料，當需要在遊标中更新資料時,如果底層表資料更新，則遊标内資料更新不成功，如果，底層表資料未更新，則遊标内表資料可以更新

3.打開遊标

當定義完遊标後，遊标需要打開後使用，隻有簡單一行代碼:

OPEN test_Cursor

注意，當全局遊标和局部遊标變量重名時，預設會打開局部變量遊标

4.關閉遊标

在遊标使用完之後，一定要記得關閉,隻需要一行代碼:CLOSE+遊标名稱

CLOSE test_Cursor

例子：

create  Procedure [dbo].[proc_lanelistsum_initdata](@begin_date datetime,@end_date datetime)
as
declare @date datetime,--用來處理結果的變量  
@formatSquad varchar(),
@v_desc varchar()
begin
    set @v_desc = '處理的出口工班日期有:';
  --聲明一個出口流水遊标  
  Declare curSquaddateFee Cursor for   
  select squaddate from laneexlist_id where SquadDate between @begin_date and @end_date group by squaddate  ---查詢語句（查詢目前的工班）  
   --打開遊标  
  Open curSquaddateFee   
  --循環并提取記錄  
  Fetch Next From curSquaddateFee Into @date--取第一條記錄存入@result中  
  While ( @@Fetch_Status= )     
        begin  
        set @formatSquad = CONVERT(varchar(), @date, );--擷取處理日期
        --删除出口彙總表目前工班為@@formatSquad的資料
        delete RepLaneExListSum where squaddate = @formatSquad;
        --從laneexlist表查詢彙總squaddate 下的資料重新插入到彙總表中
        exec proc_laneexenlistSquaddate @formatSquad,2;
        --删除通行卡彙總表目前工班為@@formatSquad的資料 
        delete ys_PasscardviewSum where squaddate = @formatSquad and listtype in(,,,)
         --從laneexlist表查詢彙總squaddate 下的資料重新插入到彙總表中
        exec proc_laneexenlistSquaddate @formatSquad,;
        set @v_desc = @v_desc + @formatSquad+',';
     Fetch Next From curSquaddateFee into @date----下一條  
       end   
  --關閉遊标     
   Close curSquaddateFee  
   insert into EXEC_PROC_LOG(id,name,[description],[status],createtime) 
   values(newid() ,'proc_lanelistsum-處理出口流水彙總表存儲過程',@v_desc,,GETDATE());
   set @v_desc = @v_desc + '入口工班日期有：';
  --釋放遊标  
   Deallocate curSquaddateFee 
 end;