sql server 游标

　　今天研究一下罪恶的游标，说它罪恶，是因为它打破了关系型数据库面向集合的思想，迫使我们用行的方式思考，而且其实现方式注定了其低效率。但是我们还是要学习它，存在即合理嘛，总有用得到的时候。但是其实很多使用游标的代码中，是不需要使用游标的，我们可以通过WHILE循环、表变量、临时表、子查询等等的方式来实现相同的功能。但是如果实在没办法了，那就用游标吧。

　　首先还是看看什么是游标。游标是系统为用户开辟的一个数据缓冲区，用来存放sql语句的执行结果。每个游标都有一个名字，用户可以用sql语句逐一从游标中获取记录，并赋值给主变量，交由主语言进一步处理。

 

T-SQL中游标的实现

　　游标可以理解成定义在特定数据集上的指针，我们控制这个指针遍历数据集，或者仅仅指向特定的行，所以游标是定义在以select开始的数据集上的。

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

参数：

LOCAL 和 GLOBAL：显然，Local是局部的，只在当前批处理、触发器、存储过程中可见；Global是全局的，在整个连接中可见，当关闭连接后，游标会隐式地释放。如果不指定，则由 default to local cursor 数据库中的配置指定（有谁知道这个default to local cursor是什么吗）.

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

READ_ONLY 和 SCROLL_LOCKS 和 OPTIMISTIC ： 

    READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作；

    SCROLL_LOCKS是另一种极端，不止可以更新，还将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功；

    OPTIMISTIC是相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新；

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性能略好.

 

游标变量支持两种方式赋值，定义时赋值和先定义后赋值。(注意，如果定义全局的游标，只支持定义时赋值)

如果是定义时赋值，变量前无须加"@",代码如下：

DECLARE test_cur CURSOR FOR
select * from dbo.class

 

如果是先定义后赋值，则要加"@",并在定义后用SET赋值，代码如下：

DECLARE @test_cur CURSOR
SET @test_cur = CURSOR FOR
select * from dbo.class

2.打开游标

　　OPEN test_cur 或 OPEN @test_cur

3.使用游标

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

　　共支持6种选项,分别为到第一行（FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n))（n可正可负）

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

4.关闭游标

　　CLOSE test_cur

5.释放游标

　　DEALLOCATE test_cur

 

对于游标一些优化建议

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