SQLServer游标指南

【什么是游标】

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是性能较好的类型