【什么是游标】

sqlServer是关系型数据库，在关系型数据库中，我们处理的对象都是面向集合的。假设我们有一个Tabel叫Test，id是主键，如下图左所示，当我们用SELECT TOP 5 [id],[name] FROM [Test] 执行时，会得到右图所示的数据集，这个数据集就是我们关注的对象。

抛开数据库中的游标不说，我们先看看下面这个东西：

中学物理课都用过的，游标卡尺。在游标卡尺中，游标是可以在尺子上滑动的，用来指示尺子上的刻度。数据库中的游标也是如此，尺子就是数据集DataSet，刻度就是数据集中的每一条数据，游标就是用来指示每一条数据的。所以游标是面向行的。

那么我们就知道游标的作用是：

把面向行的设计和面向集合的数据库系统结合起来，是一种灵活的手段。

但细细想一下，我们查到一个DataSet把它保存在内存中并用游标来控制，这不是增加了内存的开支么？如果是面向集合的，我们一次取1000条记录，如果是游标面向行的，我们要一次取1条，执行1000次。这好比我们去银行取钱，一次取1000块和取1000次每次取1块，哪个效率高呢？

所以我们又知道了游标的缺点：

性能差，效率低。

那为什么要使用游标呢？

• 使用数据库系统中现存的游标，我们只能通过游标来读取
• 当while，子查询，临时表等技术不能解决一些特殊问题时，使用游标作为一种灵活的备选方式

【如何使用游标】

游标的生命周期分成5个步骤：定义——打开——使用——关闭——释放。下面将一一解释：

1.定义

（PS：因为还没涉及到游标关闭释放，所以每一个测试请新建查询）

定义有两种方式，定义后直接赋值和先定义，后赋值：

declare cursor1 cursor for 
select * from Test

declare @cursor2 cursor
set @cursor2 = cursor for 
select * from Test

先不用弄明白上面的例子，继续往下看。

参数：Local和Global

local参数表示游标的生命周期只能存在于当前批处理、函数或存储过程，而global代表全局有效。

其中使用Global时，只支持定义后直接赋值，并且游标变量不能加@符号。当使用定义后直接赋值时，global是默认作用域。所以上面例子中，第一个游标是定义后直接赋值，没有用@符号，也没有声明作用域。它等同于

declare cursor1 cursor global for 
select * from Test

如果是先定义后使用，需要给游标变量加上@符号，同时它的作用域将默认是local，即使使用强制的global也只能是局部作用域，因为global不支持先定义后使用。所以上面例子中，第二个游标等同于

declare @cursor2 cursor 
set @cursor2 = cursor local for 
select * from Test

当然你也可以直接定义后赋值，然后用local作用域约束

declare cursor2 cursor local for 
select * from Test

也许看到这里还是有些迷糊，不要紧，通过一个实验来彻底弄清楚：

declare cursor1 cursor for 
select * from Test

declare cursor2 cursor global for 
select * from Test

declare cursor3 cursor local for 
select * from Test

declare @cursor4 cursor 
set @cursor4 = cursor for 
select * from Test

declare @cursor5 cursor 
set @cursor5 = cursor global for 
select * from Test

declare @cursor6 cursor 
set @cursor6 = cursor local for 
select * from Test

go

open cursor1
open cursor2
open cursor3
open cursor4
open cursor5
open cursor6

通过go来结束作用域，然后打开这些游标，运行结果如下

所以总结一下：

• Global参数只能定义后直接赋值

• Local参数既能定义后直接赋值也能够先定义后赋值，当使用先定义后赋值时需要给游标变量加上@符号，并且它的作用域不管填写global还是local，都只能是局部作用

参数：FORWARD_ONLY和SCROLL

FORWARD_ONLY表示游标只能由开始向结束的方向读取，也是默认参数，而SCROLL表示游标可以任意移动。

declare cursor1 cursor forward_only for 
select * from Test

declare cursor2 cursor scroll for 
select * from Test

open cursor1
open cursor2

fetch next from cursor1
fetch prior from cursor1

fetch next from cursor2
fetch prior from cursor2

在这个例子中fetch next表示让游标转到下一条记录，而fetch prior表示让游标指向到上一条记录。运行的结果如下

由于cursor1被定义为forward_only，所以它只能从前往后读取，当使用向前读取时就会报错。

参数：STATIC，KEYSET，DYNAMIC和FAST_FORWARD

Static表示游标对应的数据集是一个副本存放在tempdb，也就是说游标打开后，当表里的数据发生变化（insert，update，delete），不会影响游标的数据集。

Dynamic与static相反，游标打开后，当表的数据变化时（insert，update，delete），游标的数据集也会变化。

declare cursor1 cursor static for 
select * from Test

declare cursor2 cursor dynamic for 
select * from Test


open cursor1
open cursor2

update Test set name = '赵一' where id = 1

fetch next from cursor1
fetch next from cursor2

运行结果如下：（上为static的cursor1，下为dynamic的cursor2）

KEYSET是在游标打开后将数据集的主键作为副本存放在tempdb。当非主键的值发生改变（update，delete）时，游标的数据集会发生变化，而insert则不会影响。

declare cursor1 cursor KEYSET for 
select * from Test

open cursor1

update Test set name = '赵一' where id = 1
delete Test where id = 2
insert Test(name) values('polly')

fetch next from cursor1

先运行一下程序，然后将光标拖选fetch next from cursor1这一行，再将这行执行7次，结果如下：

发现KeySet是对update和delete敏感，而对insert是不敏感的。

FAST_FORWARD 是比较特殊的一个参数，它是FORWARD_ONLY的升级版，它会根据系统开支和性能，自动将游标设置成静态计划或者动态计划，性能比FORWARD_ONLY好。在下面几种情况时，fast_forward会做转换：

• 当SELECT语句连接包含trigger table(INSERTED/DELETED)的一个或多个表，游标被转换成static类型。
• 当SELECT语句查阅text、ntext或image列，如果sql Server OLE DB访问接口或使用了sql Server ODBC驱动器，游标被转换成dynamic类型。
• 当Fast Forward-only游标不是只读的，它会被转换成dynamic游标。动态游标是可以用来update当前滚动到的数据行并将更新回写到table中的。
• 当SELECT语句是一个查阅了链接服务器中一个或多个远程表进行分部查询，游标被转换成keyset-driven类型。
• 如果SELECT语句查阅text、ntext或image列以及TOP语句，游标被转换成keyset-driven类型。

参数： READ_ONLY，SCROLL_LOCKS，OPTIMISTIC

READ_ONLY表示游标只能够读取数据

declare cursor1  cursor read_only for 
select * from Test
open cursor1

fetch next from cursor1

delete from Test where CURRENT of cursor1

这段程序是在Test表中删除游标中的第一条数据，由于游标是read_only类型，所以不能够使用游标做其他操作。会报错：

SCROLL_LOCKS将游标所有数据锁定，防止其他程序修改

declare cursor1  cursor  SCROLL_LOCKS for 
select * from Test
open cursor1

fetch next from cursor1
begin
update Test set name='123' where CURRENT of cursor1
end

当我们执行完这个语句时再新建一个查询执行

update Test set name='456' where id = 1

那么这个查询是没有响应的，因为数据已经被锁，这时候我们回到第一段程序，执行一下 close cursor1将游标关闭，再执行第二段程序，数据才能被正常更新。

OPTIMISTIC没有SCROLL_LOCKS那么极端，如果外部数据被修改，则游标内的更新操作就失败，如果外部数据没有修改，则游标内的更新操作就成功。还是用上面的程序测试一下：

declare cursor1  cursor  OPTIMISTIC for 
select * from Test
open cursor1

fetch next from cursor1
begin
update Test set name='123' where CURRENT of cursor1
end

update Test set name='456' where id = 1

如果不执行第二段程序，则name被游标修改成123，如果执行，则被外部修改为456.

2.打开游标

上面的例子中已经使用过打开游标的操作了，只需要

open 游标名

就能打开指定的游标，需要注意的是当全局游标和局部游标变量重名时，默认打开的是局部变量游标。

3.使用游标

使用

Fetch 操作 from 游标名

操作包括第一行（FirsT),最后一行(LAST),下一行(NEXT),上一行(PRIOR),跳到某行(ABSOLUTE(n))和相对于当前跳几行(RELATIVE(n))。

如果游标是forward_only类型，那么只能使用next。

我们还可以使用into操作将游标的值读出来存入临时变量中：

declare cursor1 cursor for 
select * from Test

open cursor1

declare @id int
declare @name nvarchar(50)

fetch next from cursor1 into @id,@name

print @id

print @name

4.关闭游标

使用

close 游标名

关闭操作和打开操作相对应，如果open了一个游标，在close操作执行前再次执行open操作会报错。 我们可以连续执行两个open操作看看，结果如下：

5.释放游标

使用

DEALLOCATE 游标名

释放操作和定义操作相对应，如果定义了一个游标，在释放操作执行前再次执行定义操作，当游标是全局类型时会报错。

declare cursor1 cursor local for 
select * from Test

declare cursor2 cursor global for 
select * from Test

当我们连续执行第一条语句时不会报错，而连续执行第二条语句会报错：

【使用游标建议】

• 尽可能的不适用游标
• 使用完游标一定要关闭和释放
• 不要在游标上更新数据
• 尽可能不适用static，keyset这种副本式的类型，因为他们占用了额外的空间
• FAST_FORWARD是性能较好的类型

SQLServer游标

什么是游标

结果集，结果集就是select查询之后返回的所有行数据的集合。

游标则是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。

一般复杂的存储过程，都会有游标的出现，他的用处主要有：

1. 定位到结果集中的某一行。
2. 对当前位置的数据进行读写。
3. 可以对结果集中的数据单独操作，而不是整行执行相同的操作。
4. 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

游标的分类

根据游标检测结果集变化的能力和消耗资源的情况不同，sqlServer支持的API服务器游标分为一下4种：

• 静态游标: 静态游标的结果集，在游标打开的时候建立在TempDB中，不论你在操作游标的时候，如何操作数据库，游标中的数据集都不会变。例如你在游标打开的时候，对游标查询的数据表数据进行增删改，操作之后，静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致，则重新关闭打开游标即可。
• 动态游标:这 个则与静态游标相对，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游 标均可见。如果使用API函数或T-sql Where Current of子句通过游标进行更新，他们将立即可见。在游标外部所做的更新直到提交时才可见。
• 只进游标：只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。
• 键集驱动游标：打开键集驱动游标时，该有表中的各个成员身份和顺序是固定的。打开游标时，结果集这些行数据被一组唯一标识符标识，被标识的列做删改时，用户滚动游标是可见的，如果没被标识的列增该，则不可见，比如insert一条数据，是不可见的，若可见，须关闭重新打开游标。

静态游标在滚动时检测不到表数据变化，但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化，但消耗的资源却较多。键集驱动游标则处于他们中间，所以根据需求建立适合自己的游标，避免资源浪费。。

游标的生命周期

游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。

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

参数说明：

• cursor_name：游标名称。
• Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
• Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
• [Local | Global]:默认为local。
• Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only
• Static:静态游标
• KeySet：键集游标
• Dynamic：动态游标，不支持Absolute提取选项
• Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
• Read_Only:不能通过游标对数据进行删改。
• Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
• Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver 不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已 修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
• Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
• For Update[of column_name,....] :定义游标中可更新的列。

2.声明一个动态游标

declare orderNum_02_cursor cursor scroll

for select OrderId from bigorder where orderNum='ZEORD003402'

3.打开游标

--打开游标语法

open [ Global ] cursor_name | cursor_variable_name

cursor_name:游标名，cursor_variable_name：游标变量名称，该变量引用了一个游标。

--打开游标

open orderNum_02_cursor

4.提取数据

--提取游标语法

Fetch

[ [Next|prior|Frist|Last|Absoute n|Relative n ]

from ]

[Global] cursor_name

[into @variable_name[,....]]

参数说明：

• Frist：结果集的第一行
• Prior：当前位置的上一行
• Next：当前位置的下一行
• Last：最后一行
• Absoute n：从游标的第一行开始数，第n行。
• Relative n：从当前位置数，第n行。
• Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。

例子：

--提取数据

fetch first from orderNum_02_cursor

fetch relative 3 from orderNum_02_cursor

fetch next from orderNum_02_cursor

fetch absolute 4 from orderNum_02_cursor

fetch next from orderNum_02_cursor

fetch last from orderNum_02_cursor 

fetch prior from orderNum_02_cursor

select * from bigorder where orderNum='ZEORD003402'

结果（对比一下，就明白啦）：

例子：

--提取数据赋值给变量

declare @OrderId int

fetch absolute 3 from orderNum_02_cursor into @OrderId

select @OrderId as id

select * from bigorder where orderNum='ZEORD003402'

结果：

通过检测全局变量 @@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之 后，@@Fetch_Status可能出现3种值：0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。

这个状态值可以帮你判断提取数据的成功与否。

declare @OrderId int

fetch absolute 3 from orderNum_02_cursor into @OrderId

while @@fetch_status=0  --提取成功，进行下一条数据的提取操作

 begin

   select @OrderId as id

   fetch  next from orderNum_02_cursor into @OrderId  --移动游标

 end 

5.利用游标更新删除数据 

--游标修改当前数据语法

Update 基表名 Set 列名=值[,...] Where Current of游标名

--游标删除当前数据语法

Delete 基表名  Where Current of 游标名

---游标更新删除当前数据

---1.声明游标

declare orderNum_03_cursor cursor scroll

for select OrderId,userId from bigorder where orderNum='ZEORD003402'

--2.打开游标

open orderNum_03_cursor

--3.声明游标提取数据所要存放的变量

declare @OrderId int,@userId varchar(15)

--4.定位游标到哪一行

fetch First from orderNum_03_cursor into @OrderId,@userId  --into的变量数量必须与游标查询结果集的列数相同

while @@fetch_status=0             --判断游标读取下一条数据是否成功。 

 begin

   if @OrderId=122182

     begin

     Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor  --修改当前行

     end

   if @OrderId=154074

      begin

      Delete bigorder Where Current of  orderNum_03_cursor  --删除当前行

      end

   fetch next from orderNum_03_cursor into @OrderId,@userId  --移动游标

 end  

6.关闭游标

 游标打开后，服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集，同时使用游标也会对某些数据进行封锁。所以游标一旦用过，应及时关闭，避免服务器资源浪费。

--关闭游标语法

close [ Global ] cursor_name | cursor_variable_name

--关闭游标

close orderNum_03_cursor

7.删除游标

删除游标，释放资源

--释放游标语法

deallocate  [ Global ] cursor_name | cursor_variable_name

--释放游标

deallocate orderNum_03_cursor

游标是邪恶的！

 在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.对于类C的开发人员来着，这样的思考方式会更加舒服。

正常面向集合的思维方式是:

       而对于游标来说:

      这也是为什么游标是邪恶的，它会使开发人员变懒，懒得去想用面向集合的查询方式实现某些功能.

      同样的，在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源，当然还有更多的代码量……

      从游标对数据库的读取方式来说，不难看出游标为什么占用更多的资源，打个比方:

      当你从ATM取钱的时候，是一次取1000效率更高呢，还是取10次100？

• 既然游标这么“邪恶”，为什么还要学习游标

      我个人认为存在既是合理.归结来说，学习游标原因我归纳为以下2点

    1.现存系统有一些游标，我们查询必须通过游标来实现

    2.作为一个备用方式，当我们穷尽了while循环,子查询，临时表，表变量,自建函数或其他方式扔来无法实现某些查询的时候，使用游标实现.

• 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]

     FORselect_statement

     [ FOR UPDATE [ OFcolumn_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.打开游标

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

OPENtest_Cursor

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

3.使用游标

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

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

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

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

    比如下面代码:

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

4.关闭游标

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

CLOSEtest_Cursor

5.释放游标

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

DEALLOCATE test_Cursor

对于游标一些优化建议

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

总结

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