SQL Server游标的使用

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

下面我们来看游标定义的参数:

     LOCAL和GLOBAL二选一

     LOCAL意味着游标的生存周期只在批处理或函数或存储过程中可见，而GLOBAL意味着游标对于特定连接作为上下文，全局内有效,例如:

    

     如果不指定游标作用域，默认作用域为GLOBAL

FORWARD_ONLY 和 SCROLL 二选一

SCROLL 

表明所有的提取操作（如

FIRST

、

 

LAST

、

 

PRIOR

、

 

NEXT

、

 

RELATIVE

、

 

ABSOLUTE

）

都可用。如果不使用该保留字，那么只能进行

NEXT 

提取操作。由此可见，

SCROLL 

极大地增

加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再

 

重开游标。

表明所有的提取操作（如

FIRST

、

 

LAST

、

 

PRIOR

、

 

NEXT

、

 

RELATIVE

、

 

ABSOLUTE

）

都可用。如果不使用该保留字，那么只能进行

NEXT 

提取操作。由此可见，

SCROLL 

极大地增

加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再

 

重开游标。

表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再 重开游标。

表明所有的提取操作（如

FIRST

、

 

LAST

、

 

PRIOR

、

 

NEXT

、

 

RELATIVE

、

 

ABSOLUTE

）

都可用。如果不使用该保留字，那么只能进行

NEXT 

提取操作。由此可见，

SCROLL 

极大地增

加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再

 

重开游标。

     FORWARD_ONLY意味着游标只能从数据集开始向数据集结束的方向读取，FETCH NEXT是唯一的选项，而SCROLL支持游标在定义的数据集中向任何方向，或任何位置移动，如下图:

    

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不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新

  

2.打开游标

    当定义完游标后，游标需要打开后使用，只有简单一行代码:

OPEN test_Cursor

    注意，当全局游标和局部游标变量重名时，默认会打开局部变量游标

3.使用游标

 

   游标的使用分为两部分,一部分是操作游标在数据集内的指向，另一部分是将游标所指向的行的部分或全部内容进行操作

   只有支持6种移动选项,分别为到第一行（FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),例如:

    

    对于未指定SCROLL选项的游标来说，只支持NEXT取值.

    第一步操作完成后，就通过INTO关键字将这行的值传入局部变量:

    比如下面代码:

   

   

 游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的,例如：

   

更​新​或​删​除​

 declare Update_cursor11 cursor for   select 联系地址 from tb_ygxx  where 年龄>25 for update                          --声明为"可更新游标"

update tb_ygxx set 联系地址='贵州市' where current of Update_cursor11 更新上一条数据
      fetch from Update_cursor11 into @lxdz                                      --提取游标中的数据存储到变量中去

update tb_ygxx set 联系地址='贵州市' where current of Update_cursor11 fetch from Update_cursor11 into @lxdz                                      --提取游标中的数据存储到变量中去

fetch from delete_cursor
delete from 员工备份 where current of delete_cursor      /*删除第一行记录*/
fetch from delete_cursor
fetch from delete_cursor
delete from 员工备份 where current of delete_cursor      /*删除第三行记录*/

4.关闭游标

    在游标使用完之后，一定要记得关闭,只需要一行代码:CLOSE+游标名称

CLOSE test_Cursor

 

 

 

 

 

5.释放游标

    当游标不再需要被使用后，释放游标，只需要一行代码:DEALLOCATE+游标名称

DEALLOCATE test_Cursor

对于游标一些优化建议

•      如果能不用游标，尽量不要使用游标
•      用完用完之后一定要关闭和释放
•      尽量不要在大量数据上定义游标
•      尽量不要使用游标上更新数据
•      尽量不要使用insensitive, static和keyset这些参数定义游标
•      如果可以，尽量使用FAST_FORWARD关键字定义游标
•      如果只对数据进行读取，当读取时只用到FETCH NEXT选项，则最好使用FORWARD_ONLY参数

 

总结

     本文从游标的基本概念，到生命周期来谈游标。游标是非常邪恶的一种存在，使用游标经常会比使用面向集合的方法慢2-3倍，当游标定义在大数据量时，这个比例还会增加。如果可能，尽量使用while,子查询，临时表，函数，表变量等来替代游标，记住，游标永远只是你最后无奈之下的选择，而不是首选。

     游标是邪恶的！