SQLServer游标指南

【什么是游标】

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

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

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

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

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

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

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

性能差,效率低。

那为什么要使用游标呢?

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

【如何使用游标】

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

1.定义

(PS:因为还没涉及到游标关闭释放,所以每一个测试请新建查询)

定义有两种方式,定义后直接赋值和先定义,后赋值:

1
2
3
4
5
6
declare cursor1 cursor for 
select * from Test

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

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

参数:Local和Global

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

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

1
2
declare cursor1 cursor global for 
select * from Test

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

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

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

1
2
declare cursor2 cursor local for 
select * from Test

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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表示游标可以任意移动。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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),游标的数据集也会变化。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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则不会影响。

1
2
3
4
5
6
7
8
9
10
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表示游标只能够读取数据

1
2
3
4
5
6
7
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将游标所有数据锁定,防止其他程序修改

1
2
3
4
5
6
7
8
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

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
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操作将游标的值读出来存入临时变量中:

1
2
3
4
5
6
7
8
9
10
11
12
13
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 游标名

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

1
2
3
4
5
declare cursor1 cursor local for 
select * from Test

declare cursor2 cursor global for
select * from Test

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

【使用游标建议】

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

Wu Rang

发布于

2014-03-21

更新于

2021-12-06

许可协议

评论