SQL Server 游标

游标(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用于定义游标对象的作用域,

  • LOCAL指定游标的作用域是本地,默认值是LOCAL。
  • GLOBAL指定游标的作用域是全局的。

GLOBAL游标的作用域是连接,由连接内的batch或存储过程中引用,GLOBAL 游标仅在连接断开时被释放。

2,游标滚动的方向

FORWARD_ONLY 和 SCROLL用于指定游标滚动的方向:

  • FORWARD_ONLY 指定游标只能向前移动,即只能从第一行滚动到最后一行。FETCH NEXT 语句是唯一受支持的提取方法。
  • 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,返回完整的一行数据。

获取由键集驱动的游标时:

  • 当一个数据行被删除时,其key仍然存在于临时表keyset中,但是不再存在于结果集(result set)中。对该key进行fetch操作,@@FETCH_STATUS返回 -2
  • 当插入一个新的数据行时,如果插入操作发生在游标作用域之外,那么插入的数据对当前游标不可见;如果插入操作发生在游标作用域之内,那么插入的数据存在于结果集的末尾。
  • 更新键值的操作,等价于先删除旧的数据行,再插入一个新的数据行。

4,游标是否只读,是否支持定位修改

READ_ONLY 指定游标是只读的,不能通过游标修改数据。

SCROLL_LOCKS 指定通过游标进行的定位更新或删除一定会成功,当把行读入游标时,SQL Server将锁定这些行,以确保随后可对它们进行修改,如果还指定了FAST_FORWARD或STATIC,则不能指定SCROLL_LOCKS。

OPTIMISTIC 指定当行被读入游标之后,如果数据行被修改了,那么通过游标进行的定位更新或定位删除不成功。

当将行读入游标后,SQL Server不锁定行,SQL Server 通过以下两种方式来检测数据行是否被修改:

  • SQL Server使用表的timestamp列值的比较结果来确定行在读入游标后是否发生了变化。
  • 如果基表不包含timestamp列,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 子句可以通过以下选项来设置获取一行数据的方向:

  • NEXT:表示获取下一行,
  • PRIOR:表示获取上一行,
  • FIRST:表示获取第一行,
  • LAST:表示获取最后一行
  • ABSOLUTE n:表示获取从第一行开始的第n行,n表示第n行的绝对位置,
  • RELATIVE n:表示获取从当前行开始的第n行,n表示第n行的相对位置,

获取数据行时,是否获取成功,可以通过全局变量@@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

 

 

参考文档:

DECLARE CURSOR (Transact-SQL)

SQL Server基础之游标

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
posted @ 2020-09-28 17:11  悦光阴  阅读(438)  评论(0编辑  收藏  举报