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;