9.1.6 游标

关系数据库中的操作会对整个行集起作用。例如，由SELECT语句返回的行集包括满足该语句的WHERE子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来进行有效处理。这些应用程序需要一种每次处理一行或一部分行机制。游标就是提供这种机制的对结果集的一种扩展。

游标通过以下方式来扩展结果处理。

(1) 允许定位在结果集的特定行。

(2) 从结果集的当前位置检索一行或一部分行。

(3) 支持对结果集中当前位置的行进行数据修改。

(4) 由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

(5) 提供脚本、存储过程和触发器中用于访问结果集中的数据的T-SQL语句。

1. 游标种类

1）只进游标

只进游标不支持滚动，它只支持游标从头到尾顺序提取。行只有从数据库中提取出来后才能检索。对所有由当前用户发出或由其他用户提交并影响结果集中的行的 INSERT、UPDATE和DELETE 语句，其效果在这些行从游标中提取时是可见的。

2）静态游标

静态游标的完整结果集是打开游标时在tempdb中生成的。静态游标总是按照打开游标时的原样显示结果集。静态游标在滚动期间很少或根本检测不到变化，消耗的资源也相对较少。

3）动态游标

动态游标与静态游标是相对的。当滚动游标时，动态游标反映结果集中所做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE、INSERT 和DELETE语句均通过游标可见。

2. 游标操作

1）声明游标

语法格式如下：

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,…n ] ] } ]

[;]

参数说明：

· cursor_name：是所定义的T-SQL服务器游标的名称。cursor_name必须符合标识符规则。

· INSENSITIVE：定义一个游标，以创建由该游标使用的数据的临时副本。

如果省略INSENSITIVE，则已提交的（任何用户）对基本表的删除和更新会反映在后面的提取操作中。

· SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未指定SCROLL，则NEXT是唯一支持的提取选项。

· select_statement：定义游标结果集的标准SELECT语句。

在游标声明的select_statement中不允许使用关键字FOR BROWSE和INTO。

· READ ONLY：禁止通过该游标进行更新。

在UPDATE或DELETE语句的WHERE CURRENT OF子句中不能引用游标。该选项优先于要更新的游标的默认功能。

· UPDATE [OF column_name [,…n]]：定义游标中可更新的列。

如果指定了OF column_name [,…n]，则只允许修改所列出的列。如果指定了UPDATE，但未指定列的列表，则可以更新所有列。

例如，定义游标以s_st便于对学生表（student）中的数据进行处理，语句如下：

DECLARE s_st SCROLL CURSOR FOR select * from student

2）打开游标

语法格式如下：

OPEN {{ [ GLOBAL ] cursor_name }| cursor_variable_name }

参数说明：

· GLOBAL：指定 cursor_name为全局游标。

· cursor_name：已声明的游标的名称。

· cursor_variable_name：游标变量的名称，该变量引用一个游标。

注意：打开游标后，可以使用@@CURSOR_ROWS 函数在上次打开的游标中接收合格行的数目。

例如，OPEN s_st

3）检索游标

打开游标后，我们可以使用FETCH语句检索游标中的数据行。

语法格式如下：

FETCH

[ [ NEXT | PRIOR | FIRST | LAST

| ABSOLUTE { n |@nvar }

| RELATIVE { n |@nvar }

]

FROM

]

{{ [ GLOBAL ] cursor_name }|@cursor_variable_name }

[ INTO @variable_name [ ,...n ] ]

参数说明：

· NEXT：紧跟当前行返回结果行，且当前行递增为返回行。

· PRIOR：返回紧邻当前行前面的结果行，且当前行递减为返回行。

· FIRST：返回游标中的第一行并将其作为当前行。

· LAST：返回游标中的最后一行并将其作为当前行。

· ABSOLUTE {n|@nvar}：如果n或@nvar为正，则返回从游标起始处开始向后的第n行，并将返回行变成新的当前行；如果n或@nvar为负，则返回从游标末尾处开始向前的第n行，并将返回行变成新的当前行；如果n或@nvar为0，则不返回行，n必须是整数常量，且@nvar的数据类型必须为smallint、tinyint或int。

· RELATIVE {n|@nvar}：如果n或@nvar为正，则返回从当前行开始向后的第n行，并将返回行变成新的当前行；如果n或@nvar为负，则返回从当前行开始向前的第n行，并将返回行变成新的当前行；如果n或@nvar为0，则返回当前行，n必须是整数常量，且@nvar的数据类型必须为smallint、tinyint或int。

· GLOBAL：指定cursor_name为全局游标。

· cursor_name：指定要从中进行提取的开放游标的名称。

· @cursor_variable_name：游标变量名，引用要从中进行提取操作的打开的游标。

· INTO @variable_name[ ,…n]：允许将提取操作的列数据放到局部变量中。

注意：实际处理中可以使用@@FETCH_STATUS全局变量判断数据提取的状态。

@@FETCH_STATUS返回 FETCH 语句执行后的游标最终状态。@@FETCH_STATUS值的具体含义如表9-11所示。

例如，

FETCH NEXT from s_st --首先提取第一行数据

WHILE(@@fetch_status=0)

BEGIN

SELECT '游标读取状态'=@@fetch_status --读取全局变量以得到游标的读取状态

FETCH NEXT FROM s_st --提取下一行数据

END

4）关闭游标

CLOSE用于关闭游标引用。在使用CLOSE语句关闭某游标后，系统并没有完全释放游标的资源，也没有改变游标的定义。当再次使用OPEN语句时，可以重新打开此游标，但在重新打开游标之前，不允许提取和定位更新。

语法格式如下：

CLOSE {{ [ GLOBAL ] cursor_name }| cursor_variable_name }

例如，CLOSE s_st

5）释放游标

DEALLOCATE用于释放游标引用。当释放最后的游标引用时，组成该游标的数据结构由SQL Server释放。

例如，DEALLOCATE s_st

6）利用游标处理数据

利用游标处理数据时，首先在定义游标时要指明处理的字段，然后在语句中指定通过游标进行处理。

【例9-14】使用游标s_st对学生表（student）中的数据进行逐行分析。

解：语句如下。

DECLARE s_st SCROLL CURSOR FOR SELECT * FROM student

OPEN s_st

SELECT '游标内数据数'=@@cursor_rows

FETCH NEXT from s_st --首先提取第一行数据

WHILE(@@fetch_status=0)

BEGIN

SELECT '游标读取状态'=@@fetch_status --读取全局变量以得到游标的读取状态

FETCH NEXT FROM s_st --提取下一行数据

END

CLOSE s_st

DEALLOCATE s_st

游标的执行结果如图9-12所示。

修改游标集中的当前行，语法格式如下：

UPDATE ＜表名＞ SET ＜字段名＞=表达式

WHERE CURRENT OF ＜游标名＞

删除游标集中的当前行，语法格式如下：

DELETE [ FROM ] ＜表名＞

[ WHERE CURRENT OF ＜游标名＞

【例9-15】利用所学的游标实现将所有成绩中不足80分的加10分，高于90分的减10分。

解：程序如下。

DEALLOCATE E

DECLARE @gra int

DECLARE E SCROLL CURSOR FOR SELECT grade FROM enroll

OPEN E

FETCH NEXT FROM E INTO @gra

WHILE (@@fetch_status＜＞-1)

BEGIN

IF @gra＞90

UPDATE enroll SET grade=grade—10 WHERE current OF E

IF @gra＜80

UPDATE enroll SET grade=grade+10 WHERE current OF E

FETCH NEXT FROM E INTO @gra

END

CLOSE E

执行以前的数据如图9-13（a）所示，执行以后的结果如图9-13（b）所示。