第十九章  游标管理

在数据库开发过程中，当所检索的数据只是一条记录时，所编写的事物语句代码往往使用SELECT或INSERT等语句。但是我们经常会遇到这种情况：即从某一结果集中逐一地读取记录。那么如何解决这类问题呢？游标为我们提供了一种极好的解决方案。

本章将介绍游标的概念、工作原理及如何使用游标等内容，具体包括：

n         什么是游标

n         游标的用途

n         游标的类型和特征

n         定义和使用游标的方法

n         游标的应用

19.1  游标概述

19.1.1  游标和游标的优点

游标（Cursor）是处理数据的一种方法。为了查看或者处理集中的数据，可利用游标在结果集中逐行向前或向后浏览数据。可以把游标看成一种指针，既可以指向当前位置，也可以指向结果集中的任一位置，允许用户对指定位置的数据进行处理。

在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索的数据进行操作的灵活手段，就本质而言，游标实际上是一种能包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL选择语句相关联。因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用C语言写过对文件进行处理的程序，那么游标就像打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

我们知道关系数据管理系统实质上是面向集合的，在SQL Server中并没有一种描述表中单一记录的表达形式，除非使用where子句来限制只有一条记录被选中。因此必须借助于游标来进行面向单条记录的数据处理。

由此可见，游标允许应用程序对查询select返回的行结果集中每一行进行相同或不同的操作，而不是一次对整体结果集进行同一操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。

19.1.2  游标的用途

使用游标可以完成许多工作。例如，利用它能建立可执行的命令文字串、提高脚本的可读性、执行多个不相关的数据操作、弥补数据库和应用程序的缺陷等等。

使用游标能建立可执行的命令文字串。使用游标可以传送表名，或者把变量传送到参数中，以便建立可执行的命令文字串。可以在存储过程中使用游标，以便对数据库中的相关对象执行Transact-SQL语句，例如UPDATE STATISTICS。

使用游标可以提高脚本的可读性。在使用查询语句时，如果使用了嵌套的SELECT语句，该查询语句的脚本就可能非常复杂，可读性比较差。但是，如果使用了游标，就可以提高该查询语句脚本的可读性。

使用游标可以执行多个不相关的数据操作。如果要在产生结果集之后，对结果集中的数据进行多种不相关的数据操作，最好使用游标。例如，可以用游标为用户提供查看结果集中数据的多种方法，或者进行复杂的处理。

19.1.3  游标的种类

SQL Server支持三种类型的游标：Transact_SQL游标、API服务器游标和客户游标。

1．Transact_SQL游标

Transact_SQL游标是由DECLARE CURSOR语法定义，主要用在Transact_SQL脚本、存储过程和触发器中。Transact_SQL游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL语句或是批处理、存储过程、触发器中的Transact_SQL进行管理。Transact_SQL游标不支持提取数据块或多行数据。

2．API游标

API游标支持在OLE DB、ODBC以及DB_library中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API游标函数，MS SQL SEVER的OLE DB提供者、ODBC驱动器或DB_library的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。

3．客户游标

客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact-SQL语句或批处理，所以客户的游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。

由于API游标和Transact-SQL游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器（后台）游标。

19.2  声明游标

每一个游标必须有四个组成部分，这四个关键部分必须符合下面的顺序：

（1）DECLARE游标

（2）OPEN游标

（3）从一个游标中FETCH信息

（4）CLOSE或DEALLOCATE游标

通常我们使用DECLARE来声明一个游标，声明一个游标主要包括以下主要内容：

n         游标的名字

n         数据来源（表和列）

n         选取条件

n         属性（仅读或可修改）

其语法格式如下：

DECLARE cursor_name[INSENSITIVE][SCROLL]CURSOR

FOR select_statement

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

其中：

n         cursor_name

指游标的名字

n         INSENSITIVE

表明SQL Server会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb数据库下）。对该游标的读取操作由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。

另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE选项。

u          在SELECT语句中使用DISTINCT、GROUP BY、HAVING、UNION语句；

u          使用OUTER JOIN；

u          所选取的任意表没有索引；

u          将实数值当作选取的列。

n         SCROLL

表明所有的提取操作（如FIRST，LAST，PRIOR，NEXT，RELATIVE，ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT提取操作。由此可见，SCROLL极大地增加了提取数据的灵活性，可以随意读取结果集中的任一数据记录，而不必关闭再重开游标。

n         select_statement

是定义结果集的SELECT语句。应该注意的是，在游标中不能使用COMPUTE、COMPU-TE BY、FOR BROWER、INTO语句。

n         READ ONLY

表明不允许游标内的数据被更新，尽管在缺省状态下游标是允许更新的。而且在UPDATE或DELETE语句的WHERE CURRENT OF子句中，不允许对该游标进行引用。

n         UPDATE [OF column_name[，…n]]

定义在游标中可被修改的列。如果不指出要更新的列，那么所有的列都将被更新。

当游标被成功创建后，游标名成为该游标的唯一标识，如果在以后的存储过程、触发器或Transact_SQL脚本中使用游标，必须指定该游标的名字。

 

例19-1：SQL-92的游标语法规则

declare cur_roysched cursor for

select lorange，hirange，royalty

from roysched

where title_id=‘BU1032’

上面介绍的是SQL-92的游标语法规则。下面介绍MS SQL SERVER提供的扩展了的游标声明语法，通过增加另外的保留字，使游标的功能进一步得到了增强。其语法规则为：

DECLARE cursor_name CURSOR

[LOCAL|GLOBAL]

[FORWARED_ONLY|SCROLL]

[STATIC|KEYSET|DYNAMIC|FAST_FORWARD]

[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]

[TYPE_WARNING]

FOR select_statement

[FOR UPDATE[OF column-name[，…n]]]

各参数含义说明如下：

n         LOCAL

定义游标的作用域权限在其所在的存储过程、触发器或批处理中。当建立游标的存储过程执行结束后，游标会自动释放。因此，我们常在存储过程中使用OUTPUT保留字，将游标传递给该存储过程的调用者，这样在存储过程执行结束后，可以引用该游标变量，在这种情况下，直到引用该游标的最后一个被释放时，游标才会自动释放。

n         GLOBAL

定义游标的作用域是整个会话层。会话层指用户连接时间，它包括从用户登录到SQL Server到脱离数据库的整段时间。选择GLOBAL表明在整个会话层的任何存储过程、触发器或批处理中都可以使用该游标，只有当用户脱离数据库时，该游标才会被自动释放。

@        如果既未使用GLOBAL也未使用LOCAL，那么SQL Server将使用default to local cursor数据库选项，为了与以前的版本相兼容，该选项常设置为FALSE。

n         FORWARD_ONLY

该选项指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用FETCH NEXT操作。除非使用STATIC、KEYSET和DYNAMIC关键字，否则如果未指定是使用FORWARD_ONLY还是使用SCROLL，那么FORWARD_ONLY将成为缺省选项，因为若使用STATIC、KEYSET和DYNAMIC关键字，则变成了SCROLL游标。另外如果使用了FORWARD_ONLY，便不能使用FAST_FORWARD。

n         STATIC

该选项的含义与INSENSITIVE选项一样，SQL Server会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb数据库下）。对该游标的读取操作皆由临时表来应答。因此对基本表的修改并不影响游标中的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。

 

n         KEYSET

指出游标被打开时，游标中列的顺序是固定的，并且SQL Server会在tempdb内建立一个表，该表即为KEYSET。KEYSET的键值可唯一识别游标中的某行数据。当游标拥有者或其他用户对基本表中的非键值进行修改时，这种变化能够反映到游标中，所以游标用户或拥有者可以通过滚动游标提取这些数据。

当其他用户增加一条新的符合所定义的游标范围的数据时，无法由此游标读到该数据。因为Transact-SQL服务器游标不支持INSERT语句。

如果在游标中的某一行被删除掉，那么当通过游标来提取该删除行时，＠＠FETCH_STATUS的返回值为-2。＠＠FETCH_STATUS是用来判断读取游标是否成功的系统全局变量。

由于更新操作包括两部分：删除原数据；插入新数据，所以如果读取原数据，＠＠FETCH_STATUS的返回值为-2；而且无法通过游标来读取新插入的数据。但是如果使用了WHERE CURRENT OF子句时，该新插入数据便是可见的。

@        如果基础表未包含唯一的索引或主键，则一个KEYSET游标将恢复成STATIC游标。

n         DYNAMIC

指明基础表的变化将反映到游标中。使用这个选项会最大程度上保证数据的一致性。然而，与KEYSET和STATIC类型游标相比较，此类型游标需要大量的游标资源。

n         FAST_FORWARD

指明一个FORWARD_ONLY，READ_ONLY游标型。此选项已为执行进行了优化。如果SCROLL或FOR_UPDATE选项被定义，则FAST_FORWARD选项不能被定义。

n         SCROLL_LOCKS

指明锁被放置在游标结果集所使用的数据上。当数据被读入游标中时，就会出现锁。这个选项确保对一个游标进行的更新和删除操作总能被成功执行。如果FAST-FORWARD选项被定义，则不能选择该选项。另外，由于数据被游标锁定，所以当考虑到数据并发处理时，应避免使用该选项。

n         OPTIMISTIC

指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。如果使用了FAST_FORWARD选项，则不能使用该选项。

n         TYPE_WARNING

指明若游标类型修改成与用户定义的类型不同时，将发送一个警告信息给客户端。

@        不可以将SQL_92的游标语法规则与SQL Server的游标扩展用法混合在一起使用。

下面我们将总结一下声明游标时应注意的一些问题。

如果在CURSOR前使用了SCROLL或INSENSITIVE保留字，则不能在CURSOR和FOR select_statement之间使用任何保留字。反之同理。

如果用DECLARE CURSOR声明游标时，没有选择READ_ONLY、OPTIMISTIC或SCROLL_LOCKS选项时，游标的缺省情况为：

n         如果SELECT语句不支持更新，则游标为READ_ONLY；

n         STATIC和FAST_FORWARD类型的游标缺省为READ_ONLY；

n         DYNAMIC和KEYSET游标缺省为OPTIMISTIC。

我们仅能在Transact-SQL语句中引用游标，而不能在数据库API函数中引用。

对那些有权限对视图、表或某列执行SELECT语句的用户而言，它也具有使用游标的缺省权限。

下面给出声明游标的三个例子。

例19-2：标准游标

declare  cur_authors cursor

for

select au_id，au_lname，phone，address，city，state，contract

from authors

例19-3：只读游标

declare cur_authors cursor

for

select au_lname，au_fname，phone，address，city，state

from authors

for read only

例19-4：更新游标

declare cur_authors cursor

for

select au_lname，au_fname

from authors

for update

19.3  打开游标

游标在声明以后，如果要从游标中读取数据必须打开游标。打开一个Transact-SQL服务器游标使用OPEN命令，其语法规则为：

OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}

各参数说明如下：

n         GLOBAL

定义游标为一全局游标。

n         cursor_name

为声明的游标名字。如果一个全局游标和一个局部游标都使用同一个游标名，则使用GLOBAL便表明其为全局游标，否则表明其为局部游标。

n         crusor_variable_name

为游标变量。当打开一个游标时，SQL Server首先检查声明游标的语法是否正确，如果游标声明中有变量，则将变量值带入。

在打开游标时，如果游标声明语句中使用了INSENSITIVE或STATIC保留字，则OPEN产生一个临时表来存入结果集；如果在结果集中任何一行数据的大小超过SQL Server定义的最大行尺寸时，OPEN命令将失败；如果声明游标时用了KEYSET选项，则OPEN产生一个临时表来存放键值。所有的临时表都存在tempdb数据库中。

在游标被成功打开以后，＠＠CURSOR_ROWS全局变量将用来记录游标内数据行数。为了提高性能，SQL Server允许以异步方式从基础表向KEYSET或静态游标读入数据，即如果SQL Server的查询优化器估计基础表中返回给游标的数据行已经超过sp_configure cursor threshold参数值，则SQL Server将启动另外一个独立的线程来继续从基础表中读入符合游标定义的数据行，此时可以从游标中读取数据进行处理而不必等到所有的符合游标定义的数据都从基础表中读入游标。＠＠CURSOR_ROWS变量存储的正是在调用＠＠CURSOR_ROWS时，游标已从基础表中读入的数据行。＠＠CURSOR_ROWS的返回值有以下四个，如表19-1所示。

表19-1  ＠＠CURSOR_ROWS变量

如果所打开的游标在声明时带有SCROLL或INSENSITIVE保留字，那么＠＠CURSOR_ROWS的值为正数且为该游标的所有数据行。如果未加上这两个保留字中的一个，则＠＠CUROR_ROWS的值为-1，说明该游标内只有一条数据记录。

19.4  读取游标中的数据

当游标被打开以后，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数据主要使用FETCH命令。其语法规则为：

FETCH

[   [  NEXT|PRIOR|FIRST|LAST

    |ABSOLUTE{n|@nvar}

    |RELATIVE{n|@nvar}]

FROM  ]

{{[GLOBAL]cursor_name}|@cursor-variable_name}

[INTO@variable_name[…n] ]

各参数含义说明如下：

n         NEXT

返回结果集中当前行的下一行，并增加当前行数为返回行行数。如果FETCH NEXT是第一次读取游标中数据，则返回结果集中的是第一行而不是第二行。

n         PRIOR

返回结果集中当前行的前一行，并减少当前行数为返回行行数。如果FETCH PRIOR是第一次读取游标中数据，则无数据记录返回，并把游标位置设为第一行。

n         FIRST

返回游标第一行。

n         LAST

返回游标中的最后一行。

n         ABSOLUTE{n|@nvar}

如果n或＠nvar为正数，则表示从游标中返回的数据行数。如果n或＠nvar为负数，则返回游标内从最后一行数据算起的第n或＠nvar行数据。

如果n或＠nvar超过游标的数据子集范畴，则＠＠FETCH_STATUS返回-1，在该情况下，如果n或＠nvar为负数，则执行FETCH NEXT命令会得到第一行数据，如果n或@nvar为正值，执行FETCH PRIOR命令则会得到最后一行数据。n或@nvar可以是一固定值也可以是一smallint，tinyint或int类型的变量。

n         RELATIVE{n|@nvar}

若n或＠nvar为正数，则读取游标当前位置起向后的第n或@nvar超过游标的数据子集范畴，则＠＠FETCH_STARS返回-1，在该情况下，如果n或@nvar为负数，则执行FETCH NEXT命令则会得到第一行数据；如果n或@nvar为正值，执行FETCH PRIOR命令则会得到最后一行数据。n或@nvar可以是一固定值可以是一smallint，tinyint或int类型的变量。

n         INTO@variable_name[，…n]

允许将使用FETCH命令读取的数据存放在多个变量中。在变量行中的每个变量必须与游标结果集中相应的列相对应，每一变量的数据类型也要与游标中数据类型相匹配。

@@FETCH_STATUS全局变量返回上次执行FETCH命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH操作是否成功，来决定如何进行下一步处理。@@FETCH_STATUS变量有三个不同的返回值，如表19-2所示。

表19-2  ＠＠FETCH_STATUS变量

在使用FETCH命令从游标中读取数据时，应该注意以下情况：

当使用SQL-92语法来声明一个游标时，没有选择SCROLL选项时，只能使用FETCH NEXT命令来从游标中读取数据，即只能从结果集第一行按顺序地每次读取一行，由于不能使用FIRST、LAST、PRIOR，所以无法回滚读取以前的数据。如果选择了SCROLL选项，则可能使用所有的FETCH操作。

当使用SQL Server的扩展语法时，必须注意以下约定：

 

n         如果定义了FORWARD-ONLY或FAST_FORWARD选项，则只能使用FETCH NEXT命令。

n         如果没有定义DYNAMIC、FORWARD_ONLY或FAST_FORWARD选项，而定义了KEYSET、STATIC或SCROLL中的任何一个，则可使用所有的FETCH操作。

n         DYNAMIC SCROLL游标支持所有的FETCH选项，但禁用ABSOLUTE选项。