Sql Server2000中的游标使用方法

引子： 游标（Cursor）是一个用来保存“数据集” 的容器。可以对其中的数据以记录为单位（利用循环技术）一笔、一笔地处理。
游标（Cursor）主要用于SQL批、存储过程和触发器中。

一、 Cursor的声明、打开、关闭和删除（4个必要的过程）
●DECLARE CURSOR
● DECLARE CURSOR 定义游标的滚动行为和用于生成游标对其进行操作的结果集的查询。
●DECLARE CURSOR 接受基于 SQL-92 标准的语法和使用一组 Transact-SQL 扩展的总共2种语法。
●OPEN 语句填充结果集，FETCH 从结果集返回行。
●CLOSE释放与游标关联的当前结果集。DEALLOCATE释放游标所使用的资源。

(一)、SQL-92 语法
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
FOR select_statement 
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
● SQL-92 参数
INSENSITIVE
　　●定义一个游标，在Tempdb中创建将由该游标使用的数据的临时复本。
　　●对游标的所有请求都从 tempdb 中的该临时表中得到应答；
　　●因此，当原始数据被别的用户改变时，内容并不会实时更新。
　　●如果省略 INSENSITIVE，用户对基表提交的变更都会动态更新。
　　●该游标不允许更改或删除数据。
　　●用该选项可以加快数据读取的速度，占用的系统资源也比较少。
SCROLL
　　●可以卷动的Cursor，指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用，即可以跳跃式地读取。
　　●如未指定 SCROLL，则只能笨读（Fetch Next：由前往后顺序读取下一条）
SELECT_STATEMENT
是定义游标结果集的标准 SELECT 语句。
select内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。
如果 select_statement 内的子句与所请求的游标类型冲突，SQL Server 将游标隐性转换成另一种类型。有关更多信息，请参见隐性游标转换。
READ ONLY
Prevents updates made through this cursor.
在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。
UPDATE [OF column_name [,...n]]
定义游标内可更新的列。如果指定 OF column_name [,...n] 参数，则只允许修改所列出的列。如果在 UPDATE 中未指定列的列表，则可以更新所有列。
说明：SQL Server默认是敏感而且所有字段都可以被修改或删除
Eg:
Use joindb
Declare mycursor1 Scroll cursor
For select * from buyers 
OPEN mycursor1 
FETCH FIRST FROM mycursor1 
FETCH NEXT FROM mycursor1 
FETCH RELATIVE 1 FROM mycursor1 
FETCH ABSOLUTE 2 FROM mycursor1 
FETCH LAST FROM mycursor1 
CLOSE mycursor1 
DEALLOCATE mycursor1

(二)、Transact-SQL 扩展语法
DECLARE cursor_name CURSOR 
[GLOBAL | LOCAL] 
[ FORWARD_ONLY | SCROLL ] 
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
[ TYPE_WARNING ] 
FOR select_statement 
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
Transact-SQL 扩展参数

GLOBAL
默认为Global,指定该游标的作用域对连接是全局的,可以在全体会话中使用。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。在会话结束时其生命期终止。

LOCAL
　●指定该游标的作用域对仅限在该批（或存储过程或触发器）中执行，是局部的。该游标名称仅在这个作用域内有效。
　●在存储过程中声明的局部OUTPUT 参数，若经由OUTPUT参数返回到调用它的程序中，则Cursor的生命可以延长至最后一个参照它的变量生命期结束为止。
说明 SQL Server 早期版本中所有游标都是全局的。
FORWARD_ONLY | SCROLL
　●FORWARD_ONLY：它是默认值。表示只能由前向后地读取下一条记录。
　●SCROLL与SQL-92语法中的相同。但如果指定了STATIC;DYNAMIC;KEYSET则默认为SCROLL。
　●FAST_FORWARD 和 FORWARD_ONLY 是互斥的； 
STATIC | KEYSET | DYNAMIC | FAST_FORWARD 
DYNAMIC＝SQL-92中取消了INSENSITIVE；游标内容随时动态更新。
是这4个选项中的默认值
STATIC＝SQL-92中的INSENSITIVE 
KEYSET
　●只将记录中具有唯一性的键值列存入tempdb数据库的 keyset 的表中。其他字段保持动态更新。
　●当原始数据被更改时，若未更改到键值列则可由游标读取到最新的数据。
　●若键值列被更改或记录被删除了，则经过游标去读取该条记录时会发生@@FETCH_STATUS 值为 –2的错误。
　●但若经由CURSOR去更改该键值列的内容则仍可已照读无误。
FAST_FORWARD
　● ＝FORWARD_ONLY+READ_ONLY＋性能优化，并使用DYNAMIC方式运作
　● 与 SCROLL、 FOR_UPDAT和 FORWARD_ONLY 都是互斥的。
READ_ONLY | SCROLL_LOCKS | OPTIMISTIC 
READ_ONLY
　●禁止通过该游标进行更新，即该游标中的数据为只读。
　●在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。
SCROLL_LOCKS
　●凡是经由游标读取到的数据都会被自动锁住，以确保通过游标完成的定位更新或定位删除可以成功。
　●与FAST_FORWARD互斥。
OPTIMISTIC
　●是前面2个选项的折中
　● 经由Cursor读取数据时不会被锁定记录
　●如果原始数据中的记录已被修改，则再用Cursor去删改该记录会失败。
TYPE_WARNING
如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。
SELECT_STATEMENT:同前述
UPDATE [OF column_name [,...n]]＝前述
Eg:
DECLARE MyCoursor2 CURSOR
GLOBAL
FOR SELECT * FROM BUYERS
（三）、打开Cursor
open cursor_name
（四）、关闭Cursor
CLOSE cursor_name
（五）、删除Cursor
DEALLOCATE cursor_name
(六) 、权限
　　默认情况下，将 DECLARE CURSOR 权限授予对游标中所使用的视图、表和列有 SELECT 权限的任何用户。
注释：
DECLARE CURSOR 语句的第一种格式使用 SQL-92 语法声明游标行为。DECLARE CURSOR 的第二种格式使用 Transact-SQL 扩展，使您得以使用在 ODBC、ADO 和 DB-Library的数据库 API 游标函数中的相同游标类型定义游标。
不能混淆这两种格式。如果在 CURSOR 关键字的前面指定 SCROLL 或 INSENSITIVE 关键字，则不能在 CURSOR 和 FOR select_statement 关键字之间使用任何关键字。如果在 CURSOR 和 FOR select_statement 关键字之间指定任何关键字，则不能在 CURSOR 关键字的前面指定 SCROLL 或 INSENSITIVE。
如果使用 Transact-SQL 语法的 DECLARE CURSOR 不指定 READ_ONLY、OPTIMISTIC 或 SCROLL_LOCKS，则默认设置如下： 
· 如果 SELECT 语句不支持更新（权限不够，访问的远程表不支持更新，等等），则游标是 READ_ONLY。
· STATIC 和FAST_FORWARD 游标默认为 READ_ONLY。
· DYNAMIC 和 KEYSET 游标默认为 OPTIMISTIC。 
游标名称只能由其它 Transact-SQL 语句引用，不能由数据库 API 函数引用。例如，在声明游标后，不能从 OLE DB、ODBC、ADO 或 DB-Library 函数或方法引用游标名称。游标行不能通过 API 提取函数或方法提取，而只能由 Transact-SQL FETCH 语句提取。

二、使用FETCH读中的记录
FETCH： Transact-SQL 服务器游标中检索特定的一行。
语法
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
] 
FROM
] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ]
参数
NEXT
返回紧跟当前行之后的一行结果，并且当前行递增为结果行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。
PRIOR
返回紧临当前行前面的结果行，并且当前行递减为结果行。如果 FETCH 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，返回当前行。如果对游标的第一次提取操作时将 FETCH RELATIVE 的 n 或 @nvar 指定为负数或 0，则没有行返回。n 必须为整型常量且 @nvar 必须为 smallint、tinyint 或 int。
GLOBAL
指定 cursor_name 指的是全局游标。
CURSOR_NAME：
要从中进行提取的开放游标的名称。如果同时有以 cursor_name 作为名称的全局和局部游标存在，若指定为 GLOBAL 则 cursor_name 对应于全局游标，未指定 GLOBAL 则对应于局部游标。
@cursor_variable_name：
游标变量名，引用要进行提取操作的打开的游标。
INTO @variable_name[,...n]
允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。
注释
如果在 SQL-92 式的 DECLARE CURSOR 语句中未指定SCROLL，则 FETCH 中是唯一支持NEXT 选项。如果在 SQL-92 样式的 DECLARE CURSOR 语句中指定了 SCROLL 选项，则支持所有的 FETCH 选项。
如果使用 Transact_SQL DECLARE 游标扩展，以下规则适用： 
· 如果指定了 FORWARD-ONLY 或 FAST_FORWARD，NEXT 是唯一受支持的 FETCH 选项。
· 如果未指定 DYNAMIC、FORWARD-ONLY 或 FAST_FORWARD 选项，并且指定了 KEYSET、STATIC 或 SCROLL 中的某一个，则支持所有 FETCH 选项。
· DYNAMIC SCROLL 支持除 ABSOLUTE 之外的所有 FETCH 选项。 
@@FETCH_STATUS 函数报告上一个 FETCH 语句的状态。相同的信息记录于由 sp_describe_cursor 返回的游标中的 fetch_status 列中。这些状态信息应该用于在对由 FETCH 语句返回的数据进行任何操作之前，以确定这些数据的有效性。 
权限
FETCH 的默认权限为任何合法用户。

三、在声明游标后，可使用下列系统存储过程确定游标的特性。