SqlServer 游标（一）

1.简述

在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_Sql选择语句相关联因为游标由结果集（可以是0条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用C语言写过对文件进行处理的程序，那么游标就像你打开文件多得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈献给程序。 我们知道关系数据库管理系统实质是面向集合的，在MS Sql Server中并没有一种描述表中单一记录的表达式，除非使用where子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。   2.游标种类 MS Sql Server支持三种类型的游标：Transact_Sq游标，API服务器游标和客户游标 （1）Transact_Sql游标 Transact_Sq;游标是由Declare Cursor语法定义、主要用在Transact_Sql脚本、存储过程和触发器中。Transact_Sql主要用在服务器上，有客户端的Transact_Sql语句或是批处理、存储过程、触发器中的Transact_Sql进行管理。Transact_Sql游标不支持提取数据块或多行数据。 （2）API游标 API游标支持在OLE DB，ODBC以及DB_library中使用游标函数，主要在服务器上。每次客户端应用程序调用API游标函数，MS Sql Server的OLE DB提供者、ODBC驱动器或DB_library的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。 （3）客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被调用来自客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact_Sql语句或批处理，所以客户游标常常仅被用作服务器的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。 由于API游标和Transact_Sql游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器游标。   3.Transact_Sql游标语法 每个游标必须有四个组成部分这四个关键部分必须符合下面的顺序： 1.Declare 游标 2.Open  游标 3.从一个游标中Fetch信息 4.Close或Deallocate 游标   其中:

• INSENSITIVE

     表明MS Sqlserver会将游标定义所选取出来的记录存放在一临时表内（建立tempdb数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基表的修改并不影响游标提取数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不适用该保留字，那么对基本表的更新、删除都会反映到游标中另外应该指出，当遇到遇到以下情况发生时，游标将自动设定INSENSITIVE选项。      在select语句中使用DISTINCT、group by、having union语句；使用Outer join所选取的任意表没有索引；将实数值当作选取的列。

• SCROLL

     表名所有的提取操作（如FIRST,LAST,FRIOR,NEXT,RELATIVE,ABSOLUTE）都可用。如果不使用该保留字，那么只能进行Next提取操作。由此可见，SCROLL极大的增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再重打开游标。 select_statement 是定义结果集的select语句。应该注意的是，在游标中不能使用Compute，compu_teby，for  browse，into语句。  

• Read Only

     表名不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。而且在update或delete语句的where current of子句中，不允许对该游标进行引用。 update [Of  column_name[,....n]]     定义在游标中可被修改的列，如果不能指出要更新的列，那么所有的列都将可被更新。当游标被成功创建后，游标名成为该游标的唯一标识，如果在以后的存储过程、触发器或Transact_Sql脚本中使用游标，必须指定该游标的名字。  

4. T-SQL中游标的生命周期以及实现

    在T-SQL中，游标的生命周期由5部分组成

1.定义一个游标

     在T-SQL中，定义一个游标可以是非常简单，也可以相对复杂，取决于游标的参数.而游标的参数设置取决于你对游标原理的了解程度.

     游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行，所以游标是定义在以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 ] ] ]
[;]

   看起来很让人头痛是吧.下面仔细讲一下如何定义游标:

   游标分为游标类型和游标变量，对于游标变量来说，遵循T-SQL变量的定义方法（啥，不知道T-SQL变量定义的规则？参考我前面的博文）.游标变量支持两种方式赋值，定义时赋值和先定义后赋值，定义游标变量像定义其他局部变量一样，在游标前加”@”,注意，如果定义全局的游标，只支持定义时直接赋值，并且不能在游标名称前面加“@”，两种定义方式如下:

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

     LOCAL和GLOBAL二选一

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

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

     FORWARD_ONLY 和 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循环来共同使用,以达到遍历游标所在数据集的目的,例如：

    

  --循环读取数据
  fetch next from moveCursor into @moveid;
  while @@FETCH_STATUS=0  --如果检索到了数据继续循环
  begin
   print @moveid;
   fetch next from moveCursor into @moveid;
  end

4.关闭游标

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

CLOSE test_Cursor

5.释放游标

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

DEALLOCATE test_Cursor

对于游标一些优化建议

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

 总结

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

  更多： SqlServer数据查找距离指定坐标附近的数据（一） sqlserver计算时间差DATEDIFF 函数 SqlServer中 SET DATEFIRST