SQL_SERVER游标详解.doc

SQL SERVER游标详解 游标和游标的优点 在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。 我们知道关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。 由此可见，游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。 游标种类MS 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 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器（后台）游标。每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序；1.DECLARE 游标2.OPEN 游标3.从一个游标中FETCH 信息4.CLOSE 或DEALLOCATE 游标通常我们使用DECLARE 来声明一个游标声明一个游标主要包括以下主要内容： 游标名字 数据来源（表和列） 选取条件 属性（仅读或可修改）其语法格式如下：DECLARE cursor_name INSENSITIVE SCROLL CURSORFOR select_statementFOR READ ONLY | Update OF column_name ,.n其中： cursor_name：指游标的名字。 INSENSITIVE表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。在Select 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；使用OUTER JOIN；所选取的任意表没有索引；将实数值当作选取的列。 SCROLL表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再重开游标。 select_statement是定义结果集的Select 语句。应该注意的是，在游标中不能使用COMPUTE、COMPU- TE BY、 FOR BROWSE、 INTO 语句。 READ ONLY表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。而且在Update或Delete 语句的Where CURRENT OF 子句中，不允许对该游标进行引用。 Update OF column_name,n定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。当游标被成功创建后，游标名成为该游标的惟一标识，如果在以后的存储过程、触发器或Transact_SQL 脚本中使用游标，必须指定该游标的名字。上面介绍的是SQL_92 的游标语法规则。下面介绍MS SQL SERVER 提供的扩展了的游标声明语法，通过增加另外的保留字，使游标的功能进一步得到了增强其语法规则为；LOCAL定义游标的作用域仅限在其所在的存储过程、触发器或批处理中。当建立游标的存储过程执行结束后，游标会被自动释放。因此，我们常在存储过程中使用OUTPUT 保留字，将游标传递给该存储过程的调用者，这样在存储过程执行结束后，可以引用该游标变量，在该种情况下，直到引用该游标的最后一个就是被释放时，游标才会自动释放。 GLOBAL定义游标的作用域是整个会话层，指用户的连接时间它包括从用户登录到SQLSERVER 到脱离数据库的整段时间。选择GLOBAL 表明在整个会话层的任何存储过程、触发器或批处理中都可以使用该游标，只有当用户脱离数据库、时该游标才会被自动释放。注意：如果既未使用GLOBAL也未使用LOCAL，那么SQL SERVER将使用default local cursor数据库选项，为了与以前的版本兼容，该选项常设置为FALSE。 FORWARD_ONLY选项指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用FETCH NEXT 操作。除非使用STATIC， KEYSET 和DYNAMIC 关键字，否则如果未指明是使用FORWARD_ONLY 还是使用SCROLL， 那么FORWARD_ONLY 将成为缺省选项，因为若使用STATIC KEYSET 和DYNAMIC 关键字，则变成了SCROLL 游标。另外如果使用了FORWARD_ONLY， 便不能使用FAST_FORWARD。 STATIC选项的含义与INSENSITIVE 选项一样，MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此对基本表的修改并不影响游标中的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。 KEYSET指出当游标被打开时，游标中列的顺序是固定的，并且MS SQL SERVER 会在tempdb内建立一个表，该表即为KEYSET 的键值可惟一识别游标中的某行数据。当游标拥有者或其它用户对基本表中的非键值数据进行修改时，这种变化能够反映到游标中，所以游标用户或所有者可以通过滚动游标这显示这些数据。 当其它用户增加一条新的符合所定义的游标范围的数据时，无法由此游标读到该数据。因为Transact-SQL 服务器游标不支持INSERT 语句。 如果在游标中的某一行被删除掉，那么当通过游标来提取该删除行时，FETCH_STATUS 的返回值为-2。 FETCH_STATUS 是用来判断读取游标是否成功的系统全局变量。 由于更新操作包括两部分：删除原数据插入新数据，所以如果读取原数据，FETCH_STATUS 的返回值为-2； 而且无法通过游标来读取新插入的数据。但是如果使用了WHERE CURRENT OF 子句时，该新插入行数据便是可见的。注意：如果基础表未包含惟一的索引或主键，则一个KEYSET游标将回复成STATIC游标。 DYNAMIC指明基础表的变化将反映到游标中，使用这个选项会最大程度上保证数据的一致性。然而，与KEYSET 和STATIC 类型游标相比较，此类型游标需要大量的游标资源。 FAST_FORWARD指明一个FORWARD_ONLY, READ_ONLY 型游标。此选项已为执行进行了优化。如果SCROLL 或FOR_UPDATE 选项被定义，则FAST_FORWARD 选项不能被定义。 SCROLL_LOCKS指明锁被放置在游标结果集所使用的数据上。数据被读入游标中时，就会出现锁。这个选项确保对一个游标进行的更新和删除操作总能被成功执行。如果FAST_FORWARD选项被定义，则不能选择该选项。另外，由于数据被游标锁定，所以当考虑数据并发处理时，应避免使用该选项。 OPTIMISTIC指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。如果使用了FAST_FORWARD 选项，则不能使用该选项。 TYPE_WARNING指明若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端。注意：不可以将SQL_92的游标语法规则与MS SQL SERVER的游标扩展用法混合在一起使用。 下面我们将总结一下声明游标时应注意的一些问题。 如果在CURSOR 前使用了SCROLL 或INSENSITIVE 保留字，则不能在CURSOR 和FOR select_statement 之间使用任何的保留字。反之同理。 如果用DECLARE CURSOR 声明游标时，没有选择READ_ONLY、 OPTIMISTIC 或SCROLL_LOCKS 选项时，游标的缺省情况为： 如果SELECT 语句不支持更新，则游标为READ_ONLY； STATIC 和FAST_FORWARD 类型的游标缺省为READ_ONLY； DYNAMIC 和KEYSET 游标缺省为OPTIMISTIC。 我们仅能在Transact-SQL 语句中引用游标，而不能在数据库API 函数中引用。 游标被声明以后，可以通过系统过程对其特性进行设置。 对那些有权限对视图、表或某些列执行SELECT 语句的用户而言，它也具有使用游标的缺省权限。打开游标游标在声明以后，如果要从游标中读取数据必须打开游标。打开一个Transact-SQL服务器游标使用OPEN 命令，其语法规则为： OPEN GLOBAL cursor_name | cursor_variable_name 各参数说明如下： GLOBAL 定义游标为一全局游标。 cursor_name 为声明的游标名字。如果一个全局游标和一个局部游标都使用同一个游标名，则如果使用GLOBAL 便表明其为全局游标，否则表明其为局部游标。 cursor_variable_name 为游标变量。当打开一个游标后时，MS SQL SERVER 首先检查声明游标的语法是否正确，如果游标声明中有变量，则将变量值带入。 在打开游标时，如果游标声明语句中使用了INSENSITIVE 或STATIC 保留字，则OPEN产生一个临时表来存放结果集；如果在结果集中任何一行数据的大小超过MS SQL SERVER定义的最大行尺寸时，OPEN 命令将失败；如果声明游标时作用了KEYSET 选项，则OPEN 产生一个临时表来存放键值。所有的临时表都存在tempdb 数据库中。 在游标被成功打开之后，CURSOR_ROWS 全局变量将用来记录游标内数据行数。为了提高性能，MS SQL SERVER 允许以异步方式从基础表向KEYSET 或静态游标读入数据，即如果MS SQL SERVER 的查询优化器估计从基础表中返回给游标的数据行已经超过sp_configure cursor threshold 参数值，则MS SQL SERVER 将启动另外一个独立的线程来继续从基础表中读入符合游标定义的数据行，此时可以从游标。中读取数据进行处理而不必等到所有的符合游标定义的数据行都从基础表中读入游标 CURSOR_ROWS 变量存储的正是在调用CURSOR_ROWS 时，游标已从基础表读入的数据行。CURSOR_ROWS 的返回值有以下四个。 如果所打开的游标在声明时带有SCROLL 或INSENSITIVE 保留字，那么CURSOR_ROWS 的值为正数且为该游标的所有数据行。如果未加上这两个保留字中的一个，则CURSOR_ROWS 的值为-1， 说明该游标内只有一条数据记录。当游标被成功打开以后，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数据主要使用FETCH 命令。其语法规则为： 各参数含义说明如下：NEXT返回结果集中当前行的下一行，并增加当前行数为返回行行数。如果FETCH NEXT是第一次读取游标中数据，则返回结果集中的是第一行而不是第二行。PRIOR返回结果集中当前行的前一行，并减少当前行数为返回行行数。如果FETCH PRIOR是第一次读取游标中数据，则无数据记录返回，并把游标位置设为第一行。FIRST返回游标中第一行。LAST返回游标中的最后一行。ABSOLUTE n | nvar如果n 或nvar 为正数，则表示从游标中返回的数据行数。如果n 或nvar 为负数，则返回游标内从最后一行数据算起的第n 或nvar 行数据。若n 或nvar 超过游标的数据子集范畴，则FETCH_STARS 返回-1， 在该情况下，如果n 或nvar 为负数，则执行FETCH NEXT 命令会得到第一行数据，如果n 或nvar为正值，执行FETCH PRIOR 命令则会得到最后一行数据。n 或nvar 可以是一固定值也可以是一smallint, tinyint 或int 类型的变量。RELATIVE n | nvar若n 或nvar 为正数，则读取游标当前位置起向后的第n 或nvar 行数据；如果n 或nvar 为负数，则读取游标当前位置起向前的第n 或nvar 行数据。若n 或nvar 超过游标的数据子集范畴，则FETCH_STARS 返回-1， 在该情况下，如果n 或nvar 为负数，则执行FETCH NEXT 命令则会得到第一行数据；如果n 或nvar 为正值，执行FETCH PRIOR 命令则会得到最后一行数据。n 或nvar 可以是一固定值也可以是一smallint, tinyint或int 类型的变量。INTO variable_name,.n允许将使用FETCH 命令读取的数据存放在多个变量中。在变量行中的每个变量必须与游标结果集中相应的列相对应，每一变量的数据类型也要与游标中数据列的数据类型相匹配。 FETCH_STATUS 全局变量返回上次执行FETCH 命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH 操作是否成功，来决定如何进行下一步处理。FETCH_STATUS 变量有三个不同的返回值,如表13-2。 在使用FETCH 命令从游标中读取数据时，应该注意以下的情况： 当使用SQL-92 语法来声明一个游标时，没有选择SCROLL 选项时，只能使用FETCH NEXT 命令来从游标中读取数据，即只能从结果集第一行按顺序地每次读取一行，由于不能使用FIRST、 LAST、 PRIOR， 所以无法回滚读取以前的数据。如果选择了SCROLL 选项，则可能使用所有的FETCH 操作。 当使用MS SQL SERVER 的扩展语法时，必须注意以下约定：如果定义了FORWARD-ONLY 或FAST_FORWARD 选项，则只能使用FETCH NEXT命令；如果没有定义DYNAMIC, FORWARD_ONLY 或FAST_FORWARD 选项，而定义了KEYSET, STATIC 或SCROLL 中的任何一个，则可使用所有的FETCH 操作；DYNAMIC SCROLL 游标支持所有的FETCH， 选项但禁用ABSOLUTE 选项。 关闭游标 1、使用CLOSE 命令关闭游标 在处理完游标中数据之后必须关闭游标来释放数据结果集和定位于数据记录上的锁。CLOSE 语句关闭游标，但不释放游标占用的数据结构。如果准备在随后的使用中再次打开游标，则应使用CLOSE 命令。其关闭游标的语法规则为：CLOSE GLOBAL cursor_name | cursor_variable_name 2、自动关闭游标 我们已经了解到游标可应用在存储过程、触发器和Transact_SQL 脚本中。如果在声明游标与释放游标之间使用了事务结构，则在结束事务时游标会自动关闭。其具体的情况如下所示：(1)、声明一个游标(2)、打开游标(3)、读取游标(4)、BEGIN TRANSATION(5)、数据处理(6)、COMMIT TRANSATION(7)、回到步骤3 在这样的应用环境中。当从游标中读取一条数据记录进行以BEGIN TRANSATION为开头，COMMIT TRANSATION 或ROLLBACK 为结束的事务处理时，在程序开始运行后，第一行数据能够被正确返回，经由步骤7， 程序回到步骤3， 读取游标的下一行，此时常会发现游标未打开的错误信息。其原因就在于当一个事务结束时，不管其是以COMMIT TRANSATION 还是以ROLLBACK TRANSATION 结束，MS SQL SERVER 都会自动关闭游标，所以当继续从游标中读取数据时就会造成错误。 解决这种错误的方法就是使用SET 命令将CURSOR_CLOSE_ON_COMMIT 这一参数设置为OFF 状态。其目的就是让游标在事务结束时仍继续保持打开状态，而不会被关闭。使用SET 命令的格式为：SET CURSOR_CLOSE_ON_COMMIT OFF释放游标 在使用游标时，各种针对游标的操作或者引用游标名，或者引用指向游标的游标变量。当CLOSE 命令关闭游标时，并没有释放游标占用的数据结构。因此常使用DEALLOCATE 命令。通过该命令可以删除掉游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。其语法规则为： DEALLOCATE GLOBAL cursor_name | cursor_variable_name 各参数的含义参看13.3 打开游标一节。 当使用DEALLOCATE cursor_variable_name 来删除游标时，游标变量并不会被释放，除非超过使用该游标的存储过程、触发器的范围(即游标的作用域)。游标的概念游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的优点在于它可以定位到结果集中的某一行，并可以对该行数据执行特定操作，为用户在处理数据的过程中提供了很大方便。一个完整的游标由5部分组成，并且这5个部分应符合下面的顺序。（1）声明游标。（2）打开游标。（3）从一个游标中查找信息。（4）关闭游标。（5）释放游标。声明游标首先来学习如何声明一个游标，声明游标使用DECLARE CURSOR语句。此语句有两种语法声明格式，分别为SQL 92标准格式和SQL Server扩展（扩展了声明游标的参数）格式，下面将分别介绍声明游标的两种语法格式。1SQL-92语法格式语法：DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n 参数说明：l DECLARE cursor_name：指定一个游标名称，其游标名称必须符合标识符规则。l INSENSITIVE：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用SQL-92语法时，如果省略INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。l SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。 FIRST：取第一行数据。 LAST：取最后一行数据。 PRIOR：取前一行数据。 NEXT：取后一行数据。 RELATIVE：按相对位置取数据。 ABSOLUTE：按绝对位置取数据。如果未指定SCROLL，则NEXT是惟一支持的提取选项。l select_statement：定义游标结果集的标准SELECT语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE和INTO。l READ ONLY：表明不允许游标内的数据被更新，尽管在默认状态下游标是允许更新的。在UPDATE或DELETE语句的WHERE CURRENT OF子句中不允许引用游标。l UPDATE OF column_name ,.n ：定义游标内可更新的列。如果指定OF column_name ,.n参数，则只允许修改所列出的列。如果在UPDATE中未指定列的列表，则可以更新所有列。2SQL Server扩展格式语法：DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWARD_ONLY | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDATE OF column_name ,.n 参数说明：l DECLARE cursor_name：指定一个游标名称，其游标名称必须符合标识符规则。l LOCAL：定义游标的作用域仅限在其所在的批处理、存储过程或触发器中。当建立游标在存储过程执行结束后，游标会被自动释放。l GLOBAL：指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在脱接时隐性释放。l FORWARD_ONLY：指定游标只能从第一行滚动到最后一行。FETCH NEXT是惟一受支持的提取选项非指定STATIC、KEYSET或DYNAMIC关键字，否则默认为FORWARD_ONLY。STATIC、KEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不同，STATIC、KEYSET和DYNAMICTransact-SQL游标支持FORWARD_ONLY。FAST_FORWARD和FORWARD_ONLY是互斥的；如果指定一个，则不能指定另一个。l STATIC：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。l KEYSET：指定当游标打开时，游标中行的成员资格和顺序已经固定。对行进行惟一标识的键集内置在tempdb内一个称为keyset的表中。对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交）在用户滚动游标时是可视的。其他用户进行的插入是不可视的（不能通过Transact-SQL服务器游标进行插入）。如果某行已删除，则对该行的提取操作将返回FETCH_STATUS值-2。从游标外更新键值类似于删除旧行后接着插入新行的操作。含有新值的行不可视，对含有旧值的行的提取操作将返回FETCH_STATUS值-2。如果通过指定WHERE CURRENT OF子句用游标完成更新，则新值可视。l DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的行所做的所有数据的更改。行的数据值、顺序和成员在每次提取时都会更改。动态游标不支持ABSOLUTE提取选项。l FAST_FORWARD：指明一个FORWARD_ONLY、READ_ONLY型游标。l SCROLL_LOCKS：指定确保通过游标完成的定位更新或定位删除可以成功。将行读入游标以确保它们可用于以后的修改时，SQL Server会锁定这些行。如果还指定了FAST_FORWARD，则不能指定SCROLL_LOCKS。l OPTIMISTIC：指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。l TYPE_WARNING：指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。使用DECLARE CURSOR语句创建以下几种形式的游标。（1）示例：创建一个名为“MyCursor”的标准游标。USE 销售管理系统DECLARE MyCursor CURSOR FORSELECT * FROM 操作员信息表GO（2）示例：创建一个名为“MyCursor_01”的只读游标。USE 销售管理系统DECLARE MyCursor_01 CURSOR FORSELECT * FROM 操作员信息表FOR READ ONLY -只读游标GO（3）示例：创建一个名为“MyCursor_02”的更新游标。USE 销售管理系统DECLARE MyCursor_02 CURSOR FORSELECT 操作员编号,操作员姓名,操作员年龄 FROM 操作员信息表FOR UPDATE -更新游标GO打开游标打开一个声明的游标使用OPEN命令。语法：OPEN GLOBAL cursor_name | cursor_variable_name 参数说明：l GLOBAL：指定cursor_name为全局游标。l cursor_name：已声明的游标名称，如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，cursor_name指的是全局游标，否则，cursor_name指的是局部游标。l cursor_variable_name：游标变量的名称，该名称引用一个游标。说明：如果使用INSENSITIV或STATIC选项声明了游标，那么OPEN将创建一个临时表以保留结果集。如果结果集中任意行的大小超过SQL Server表的最大行大小，OPEN将失败。如果使用KEYSET选项声明了游标，那么OPEN将创建一个临时表以保留键集。临时表存储在tempdb中。首先声明一个名为MyCursor_001的游标，然后使用OPEN命令打开该游标。操作步骤如下：（1）在操作系统中选择“开始”“所有程序”“Microsoft SQL Server”“查询分析器”命令，打开查询分析器。（2）在查询分析器的工具栏中选择要连接的数据库，这里选择“销售管理系统”。（3）在代码编辑区中编写如下代码。SQL语句如下：USE 销售管理系统DECLARE MyCursor_001 CURSOR FOR -声明游标SELECT 操作员编号,操作员姓名,操作员年龄 FROM 操作员信息表WHERE 操作员编号 = CY20040604006OPEN MyCursor_001 -打开游标GO从游标中读取数据当打开一个游标之后，就可以读取游标中的数据了。可以使用FETCH命令读取游标中的某一行数据。语法：FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n | nvar FROM GLOBAL cursor_name | cursor_variable_name INTO variable_name ,.n 参数说明：l NEXT：返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果FETCH NEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。l PRIOR：返回紧临当前行前面的结果行，并且当前行递减为结果行。如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。l FIRST：返回游标中的第一行并将其作为当前行。l LAST：返回游标中的最后一行并将其作为当前行。l ABSOLUTE n | nvar：如果n或nvar为正数，返回从游标头开始的第n行，并将返回的行变成新的当前行。如果n或nvar为负数，返回游标尾之前的第n行，并将返回的行变成新的当前行。如果n或nvar为0，则没有行返回。l RELATIVE n | nvar：如果n或nvar为正数，返回当前行之后的第n行，并将返回的行变成新的当前行。如果n或nvar为负数，返回当前行之前的第n行，并将返回的行变成新的当前行。如果n或nvar为0，返回当前行。如果对游标的第一次提取操作时将FETCHRELATIVE的n或nvar指定为负数或0，则没有行返回。n必须为整型常量且nvar必须为smallint、tinyint或int。说明：在前两个参数中，包含了n和nvar其表示游标相对与作为基准的数据行所偏离的位置。l GLOBAL：指定cursor_name为全局游标。l cursor_name：要从中进行提取的开放游标的名称。如果同时有以cursor_name作为名称的全局和局部游标存在，若指定为GLOBAL，则cursor_name对应于全局游标，未指定GLOBAL，则对应于局部游标。l cursor_variable_name：游标变量名，引用要进行提取操作的打开的游标。l INTO variable_name,.n：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。l FETCH_STATUS：返回上次执行FETCH命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH操作是否成功，决定如何进行下一步处理。FETCH_STATUS变量有3个不同的返回值，说明如下： 返回值为0：FETCH 语句成功。 返回值为-1：FETCH 语句失败或此行不在结果集中。 返回值为-2：被提取的行不存在。说明：当使用SQL-92语法来声明一个游标时，没有选择SCROLL选项，则只能使用FETCH NEXT命令来从游标中读取数据，即只能从结果集第一行按顺序地每次读取一行。由于不能使用FIRST、LAST、PRIOR，所以无法回滚读取以前的数据。如果选择了SCROLL选项，则可以使用所有的FETCH操作。通常游标取数的操作与WHILE循环紧密结合，下面将使用FETCH_STATUS控制在一个WHILE循环中的游标活动。程序运行结果如图1所示。图1 从游标中读取数据SQL语句如下：USE 销售管理系统 -引入数据库DECLARE ReadCursor CURSOR FOR -声明一个游标SELECT 操作员编号,操作员姓名,操作员性别,操作员住址FROM 操作员信息表OPEN ReadCursor -打开游标FETCH NEXT FROM ReadCursor -执行取数操作WHILE FETCH_STATUS=0 -检查FETCH_STATUS，以确定是否还可以继续取数BEGIN FETCH NEXT FROM ReadCursorEND关闭游标当游标使用完毕之后，使用CLOSE语句可以关闭游标，但不释放游标占用的系统资源。语法：CLOSE GLOBAL cursor_name | cursor_variable_name 参数说明：l GLOBAL：指定cursor_name为全局游标。l cursor_name：开放游标的名称。如果全局游标和局部游标都使用cursor_name作为它们的名称，那么当指定GLOBAL时，cursor_name引用全局游标；否则，cursor_name引用局部游标。l cursor_variable_name：与开放游标关联的游标变量名称。示例：声明一个名为“CloseCursor”的游标，并使用Close语句关闭游标。SQL语句如下：USE 销售管理系统 -引入数据库DECLARE CloseCursor Cursor FOR -声明游标SELECT * FROM 销售表FOR READ ONLYOPEN CloseCursor -打开游标CLOSE CloseCursor -关闭游标释放游标当游标关闭之后，并没有在内存中释放所占用的系统资源，所以可以使用DEALLOCATE命令删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由SQL Server释放。语法：DEALLOCATE GLOBAL cursor_name | cursor_variable_name 参数说明：l cursor_name：已声明游标的名称。当全局和局部游标都以cursor_name作为它们的名称存在时，如果指定GLOBAL，则cursor_name引用全局游标，如果未指定GLOBAL，则cursor_name引用局部游标。l cursor_variable_name：cursor变量的名称。cursor_variable_name必须为cursor类型。当使用DEALLOCATE cursor_variable_name来删除游标时，游标变量并不会被释放，除非超过使用该游标的存储过程和触发器的范围。示例：使用DEALLOCATE命令释放名为“FreeCursor”的游标。SQL语句如下：USE 销售管理系统DECLARE FreeCursor Cursor FOR -声明游标SELECT * FROM 销售表OPEN FreeCursor -打开游标Close FreeCursor -关闭游标DEALLOCATE FreeCursor -释放游标创建游标变量在批处理或过程的正文中用DECLARE语句声明变量，并用SET或SELECT语句给其指派值。游标变量可通过该语句声明，并且可用在其他与游标相关的语句中。所有变量在声明后均初始化为NULL。首先创建一个游标并打开该游标，之后创建一个游标变量并将游标的值（SELECT * FROM Jobs）赋给游标变量，并通过FETCH语句读取游标变量中的值，最后关闭并释放游标。程序运行结果如图1所示。图1 创建游标变量SQL语句如下：USE pubsDECLARE MyCursor_001 Cursor FOR -创建游标SELECT * FROM JobsOPEN MyCursor_001 -打开游标DECLARE CursorVar Cursor -创建游标变量SET CursorVar = MyCursor_001 -为游标变量赋值FETCH NEXT FROM CursorVar -读取游标变量中的值 CLOSE MyCursor_001 -关闭游标DEALLOCATE MyCursor_001 -释放游标静态游标静态游标的完整结果集在游标打开时建立在tempdb中。静态游标总是按照游标打开时的原样显示结果集。静态游标在滚动期间很少或根本检测不到变化，虽然它在tempdb中存储了整个游标，但消耗的资源很少。尽管动态游标使用tempdb的程度最低，在滚动期间它能够检测到所有变化，但消耗的资源也更多。键集驱动游标介于二者之间，它能检测到大部分的变化，但比动态游标消耗更少的资源。动态游标动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标可见。只进游标只进游标不支持滚动，它只支持游标从头到尾顺序提取。只在从数据库中提取出来后才能行检索。对所有由当前用户发出或由其他用户提交、并影响结果集中的行的INSERT、UPDATE和DELETE语句，其效果在这些行从游标中提取时是可见的。键集驱动游标打开游标时，键集驱动游标中的成员和行顺序是固定的。键集驱动游标由一套被称为键集的惟一标识符（键）控制。键由以惟一方式在结果集中标识行的列构成。键集是游标打开时来自所有适合SELECT语句的行中的一系列键值。键集驱动游标的键集在游标打开时建立在tempdb中。对非键集列中的数据值所做的更改（由游标所有者更改或其他用户提交）在用户滚动游标时是可见的。在游标外对数据库所做的插入在游标内是不可见的，除非关闭并重新打开游标。使用游标修改数据本节主要介绍如何使用游标修改数据在查询分析器中声明变量，并设置id变量的值，然后声明一个游标并打开该游标，使用FETCH NEXT方法来获取游标的下一行数据，并将此数据赋值给变量，如果FETCH语句执行成功则判断当前游标所指定的操作员编号是否与变量id相等，如果相等则更新数据，最后关闭游标并释放游标所占用的资源。程序运行结果如图1所示。将编号为“CY20061010001”的操作员年龄修改为“30”岁图1 使用游标修改数据SQL语句如下：Declare id char(20) /*声明变量*/declare ids char(20)declare names char(20)set id=CY20061010001 -为变量赋值DECLARE authors_cursor CURSOR -声明游标 FOR SELECT 操作员编号,操作员姓名 FROM 操作员信息表OPEN authors_cursor -打开游标FETCH NEXT FROM authors_cursor -获取游标的下一行数据into ids,names -使变量获得当前游标指定行的操作员编号和操作员姓名WHILE FETCH_STATUS = 0 -FETCH语句执行成功BEGIN if id=ids -判断变量的值是否与游标指定的操作员编号相等 begin -更新指定条件的操作员年龄 update 操作员信息表 set 操作员年龄=30 where 操作员编号 = ids end FETCH NEXT FROM authors_cursor -获取游标的下一行into ids,names -使变量获得当前游标指定行的操作员编号和操作员姓名EndCL