游标(Cursor)是SQL Server的一种数据访问机制,它使得程序可以逐行处理数据,即允许用户访问单独的数据行,对每一行数据进行单独的处理。
创建游标对象,注意不是游标变量,游标名称不需要带前导@:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
在创建游标时,需要配置游标的选项,从定义游标的语法中,可以看出,游标共有5个选项。
1,游标的作用域
LOCAL 和 GLOBAL用于定义游标对象的作用域,
GLOBAL游标的作用域是连接,由连接内的batch或存储过程中引用,GLOBAL 游标仅在连接断开时被释放。
2,游标滚动的方向
FORWARD_ONLY 和 SCROLL用于指定游标滚动的方向:
默认情况下,游标是FORWARD_ONLY(仅向前游标),只向前滚动。注意,向前一行是指下一行,向后一行是指上一行。当设置游标为FORWARD_ONLY和DYNAMIC时,游标在处理当前行时会检测到所有更改,这意味着对数据提交的任何修改操作(INSERT,UPDATE和DELETE)都会影响到获取的数据集,进而影响到FETCH NEXT语句。由于游标无法向后滚动,在某一行之后,上N行的数据修改无法查看,但是可以通过fetch查看下N行的数据修改。
3,游标是否对数据的修改可见
STATIC 指定静态游标,静态游标始终是只读的,在第一次打开游标时,在tempdb上创建游标的完整结果集,从临时表来响应游标的查询操作,因此对基表的修改操作,不会影响游标的结果集。在游标关闭之前,游标的结果集保持不变。
DYNAMIC 指定静态游标,动态游标反映结果集中的所有修改操作。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标可见。
FAST_FORWARD 指定启动了性能优化的FORWARD_ONLY、READ_ONLY游标。如果指定了SCROLL或FOR_UPDATE,则不能指定FAST_FORWARD。
KEYSET 指定游标按照键集来滚动,游标的结果集(result set)包含两部分:键+其他列,键用于唯一标识一行数据,这意味着键(key)是由唯一标识数据行的一列或多列构成的,键集(KyeSet)是指由一组键构成的集合。
在打开游标时,SQL Server在tempdb中创建临时表keyset,该表是只读的,游标的滚动是通过keyset来迭代的。因此,当游标打开时,行的顺序已经固定。在游标滚动时,按照键集对游标的结果集进行join,返回完整的一行数据。
获取由键集驱动的游标时:
4,游标是否只读,是否支持定位修改
READ_ONLY 指定游标是只读的,不能通过游标修改数据。
SCROLL_LOCKS 指定通过游标进行的定位更新或删除一定会成功,当把行读入游标时,SQL Server将锁定这些行,以确保随后可对它们进行修改,如果还指定了FAST_FORWARD或STATIC,则不能指定SCROLL_LOCKS。
OPTIMISTIC 指定当行被读入游标之后,如果数据行被修改了,那么通过游标进行的定位更新或定位删除不成功。
当将行读入游标后,SQL Server不锁定行,SQL Server 通过以下两种方式来检测数据行是否被修改:
如果检测到该行被修改,那么尝试进行的定位更新或删除将失败。如果还指定了FAST_FORWARD,则不能指定OPTIMISTIC。
5,是否通过游标更新数据
FOR UPDATE [OF column_name [,...n]] 用于在游标中定义可以更新的列,如果提供了 [OF column_name [,...n]] 选项,那么仅允许修改列出的列。如果仅指定了FOR UPDATE子句,那么可以修改所有的列。
通过FETCH命令来获取游标中的结果集,并把获取的一行数据的各列赋值给变量列表:
FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
在赋值时,变量列表 一 一 对应SELECT 子句的字段列表,FETCH 子句可以通过以下选项来设置获取一行数据的方向:
获取数据行时,是否获取成功,可以通过全局变量@@FETCH_STATUS来检测,注意,全局变量的作用域是当前的连接:
@@FETCH_STATUS
该全局变量返回当前连接中打开的任何游标发出的最后一个FETCH语句的状态,状态值是整数:
注意,由于@@FETCH_STATUS 用于检测当前连接上的所有游标对象的状态,在使用时,要谨慎。在执行FETCH语句后,必须立即对@@FETCH_STATUS进行测试,然后再对其他游标执行任何其他FETCH语句。
下面的脚本,定义了一个静态的只读游标,演示了游标打开、遍历和关闭的全过程:
declare @v1 int declare @v2 int declare cursor_name cursor forward_only static read_only for select c1,c2 from table_name; open cursor_name fetch next from cursor_name into @v1, @v2 while @@fetch_status=0 begin -- do something fetch next from cursor_name into @v1, @v2 end close cursor_name deallocate cursor_name
转自作者:悦光阴
出处:http://www.cnblogs.com/ljhdo/
参考文档:
DECLARE CURSOR (Transact-SQL)
SQL Server基础之游标