SQLServer游标指南

概述【什么是游标】 SQLServer是关系型数据库，在关系型数据库中，我们处理的对象都是面向集合的。假设我们有一个Tabel叫Test，id是主键，如下图左所示，当我们用SELECT TOP 5 [id],[name] FROM [Test] 执行时，会得到右图所示的数据集，这个数据集就是我们关注的对象。 抛开数据库中的游标不说，我们先看看下面这个东西： 中学物理课都用过的，游标卡尺。在游标卡尺中， 【什么是游标】

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 Testdeclare @cursor2 cursorset @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 Testdeclare cursor2 cursor global for select * from Testdeclare cursor3 cursor local for select * from Testdeclare @cursor4 cursor set @cursor4 = cursor for select * from Testdeclare @cursor5 cursor set @cursor5 = cursor global for select * from Testdeclare @cursor6 cursor set @cursor6 = cursor local for select * from Testgoopen cursor1open cursor2open cursor3open cursor4open cursor5open cursor6

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

所以总结一下：

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

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

参数：FORWARD_ONLY和SCRolL

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

declare cursor1 cursor forward_only for select * from Testdeclare cursor2 cursor scroll for select * from Testopen cursor1open cursor2fetch next from cursor1fetch prior from cursor1fetch next from cursor2fetch 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 Testdeclare cursor2 cursor dynamic for select * from Testopen cursor1open cursor2update Test set name = '赵一' where ID = 1fetch next from cursor1fetch next from cursor2

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

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

declare cursor1 cursor KEYSET for select * from Testopen cursor1update Test set name = '赵一' where ID = 1delete Test where ID = 2insert 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 Testopen cursor1fetch next from cursor1delete from Test where CURRENT of cursor1

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

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

declare cursor1  cursor  SCRolL_LOCKS for select * from Testopen cursor1fetch next from cursor1beginupdate Test set name='123' where CURRENT of cursor1end

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

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

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

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

declare cursor1  cursor  OPTIMISTIC for select * from Testopen cursor1fetch next from cursor1beginupdate Test set name='123' where CURRENT of cursor1end

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 Testopen cursor1declare @ID intdeclare @name nvarchar(50)fetch next from cursor1 into @ID,@nameprint @IDprint @name

4.关闭游标

使用

close 游标名

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

5.释放游标

使用

DEALLOCATE 游标名

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

declare cursor1 cursor local for select * from Testdeclare cursor2 cursor global for select * from Test

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

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

以上是内存溢出为你收集整理的SQLServer游标指南全部内容，希望文章能够帮你解决SQLServer游标指南所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。