风舞残阳 2008-4-2 17:54
sql server 2000 游标用法小例
[size=3][/size]SqlServer2000游标用法小例翻弹押尾桑Cannon(卡侬)%26raquo;
--------------------------------------------------------------------------------
DECLARECURSOR(T-[wiki]SQL[/wiki])创建游标
September14th,2006byOoperMan(1votes,average:5outof5)Loading...
SQLServer2005联机丛书
DECLARECURSOR(Transact-SQL)
更新日期：2005年12月5日
定义Transact-SQL[wiki]服务器[/wiki]游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。DECLARECURSOR接受基于SQL-92[wiki]标准[/wiki]的语法和使用一组Transact-SQL扩展插件的语法。
Transact-SQL语法约定
语法
SQL92Syntax
DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR
FORselect_statement
[FOR...{READONLY│UPDATE[OFcolumn_name[,...n]]}]
[;]
Transact-SQLExtendedSyntax
DECLAREcursor_nameCURSOR[LOCAL│GLOBAL]
[FORWARD_ONLY│SCROLL]
[STATIC│KEYSET│DYNAMIC│FAST_FORWARD]
[READ_ONLY│SCROLL_LOCKS│OPTIMISTIC]
[TYPE_WARNING]
FORselect_statement
[FORUPDATE[OFcolumn_name[,...n]]]
[;]
参数
cursor_name
所定义的Transact-SQL服务器游标的名称。cursor_name必须符合标识符规则。有关标识符规则的详细信息，请参阅使用标识符作为对象名称。
INSENSITIVE
定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用SQL-92语法时，如果省略INSENSITIVE，则已提交的（任何用户）对基础表的删除和更新都反映在后面的提取中。
SCROLL
指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未在SQL-92DECLARECURSOR中指定SCROLL，则NEXT是唯一支持的提取选项。如果也指定了FAST_FORWARD，则不能指定SCROLL。
select_statement
定义游标结果集的标准SELECT语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTEBY、FORBROWSE和INTO。
Microsoft如果select_statement中的子句与所请求的游标类型的功能有冲突，则SQLServer会将游标隐式转换为其他类型。有关详细信息，请参阅使用隐式游标转换。
READONLY
禁止通过该游标进行更新。在UPDATE或DELETE语句的WHERECURRENTOF子句中不能引用游标。该选项优于要更新的游标的默认功能。
UPDATE[OFcolumn_name[,…n]]
定义游标中可更新的列。如果指定了OFcolumn_name[,…n]，则只允许修改列出的列。如果指定了UPDATE，但未指定列的列表，则可以更新所有列。
cursor_name
所定义的Transact-SQL服务器游标的名称。cursor_name必须符合标识符规则。有关标识符规则的详细信息，请参阅使用标识符作为对象名称。
LOCAL
指定对于在其中创建的批处理、存储过程或触发器来说，该游标的作用域是局部的。该游标名称仅在这个作用域内有效。在批处理、存储过程、触发器或存储过程OUTPUT参数中，该游标可由局部游标变量引用。OUTPUT参数用于将局部游标传递回调用批处理、存储过程或触发器，它们可在存储过程终止后给游标变量分配参数使其引用游标。除非OUTPUT参数将游标传递回来，否则游标将在批处理、存储过程或触发器终止时隐式释放。如果OUTPUT参数将游标传递回来，则游标在最后引用它的变量释放或离开作用域时释放。
GLOBAL
指定该游标的作用域对来说连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在断开连接时隐式释放。
注意：
如果GLOBAL和LOCAL参数都未指定，则默认值由defaulttolocalcursor数据库选项的设置控制。在SQLServer7.0版中，该选项默认为FALSE，以便与SQLServer的早期版本相匹配，在早期版本中，所有游标都是全局的。该选项的默认值在以后的SQLServer版本中可能会更改。有关详细信息，请参阅“设置数据库选项”。
FORWARD_ONLY
指定游标只能从第一行滚动到最后一行。FETCHNEXT是唯一支持的提取选项。如果在指定FORWARD_ONLY时不指定STATIC、KEYSET和DYNAMIC关键字，则游标作为DYNAMIC游标进行操作。如果FORWARD_ONLY和SCROLL均未指定，则除非指定STATIC、KEYSET或DYNAMIC关键字，否则默认为FORWARD_ONLY。STATIC、KEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不同，STATIC、KEYSET和DYNAMICTransact-SQL游标支持FORWARD_ONLY。
STATIC
定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。
KEYSET
指定当游标打开时，游标中行的成员身份和顺序已经固定。对行进行唯一标识的键集内置在tempdb内一个称为keyset的表中。
注意：
如果查询引用了至少一个无唯一索引的表，则键集游标将转换为静态游标。
对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交）可以在用户滚动游标时看到。其他用户执行的插入是不可见的（不能通过Transact-SQL服务器游标执行插入）。如果删除行，则在尝试提取行时返回值为-2的@@FETCH_STATUS。从游标以外更新键值类似于删除旧行然后再插入新行。具有新值的行是不可见的，并在尝试提取具有旧值的行时，将返回值为-2的@@FETCH_STATUS。如果通过指定WHERECURRENTOF子句利用游标来完成更新，则新值是可见的。
DYNAMIC
定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持ABSOLUTE提取选项。
FAST_FORWARD
指定启用了性能优化的FORWARD_ONLY、READ_ONLY游标。如果指定了SCROLL或FOR_UPDATE，则不能也指定FAST_FORWARD。
注意：
在SQLServer2000中，FAST_FORWARD和FORWARD_ONLY游标选项是互相排斥的。如果指定了二者，则会引发错误。在SQLServer2005中，这两个关键字可以用在同一个DECLARECURSOR语句中。
READ_ONLY
禁止通过该游标进行更新。在UPDATE或DELETE语句的WHERECURRENTOF子句中不能引用游标。该选项优于要更新的游标的默认功能。
SCROLL_LOCKS
指定通过游标进行的定位更新或删除保证会成功。将行读取到游标中以确保它们对随后的修改可用时，MicrosoftSQLServer将锁定这些行。如果还指定了FAST_FORWARD或STATIC，则不能指定SCROLL_LOCKS。
OPTIMISTIC
指定如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不会成功。当将行读入游标时SQLServer不会锁定行。相反，SQLServer使用timestamp列值的比较，或者如果表没有timestamp列，则使用校验和值，以确定将行读入游标后是否已修改该行。如果已修改该行，则尝试进行的定位更新或删除将失败。如果还指定了FAST_FORWARD，则不能指定OPTIMISTIC。
TYPE_WARNING
指定如果游标从所请求的类型隐式转换为另一种类型，则向客户端发送警告消息。
select_statement
定义游标结果集的标准SELECT语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTEBY、FORBROWSE和INTO。
注意：
您可以在游标声明中使用查询提示；但是，如果还使用FORUPDATEOF子句，则请在FORUPDATEOF之后指定OPTION(query_hint)。
如果select_statement中的子句与所请求的游标类型的功能有冲突，则SQLServer会将游标隐式转换为其他类型。有关详细信息，请参阅“隐式游标转换”。
FORUPDATE[OFcolumn_name[,…n]]
定义游标中可更新的列。如果提供了OFcolumn_name[,…n]，则只允许修改列出的列。如果指定了UPDATE，但未指定列的列表，则除非指定了READ_ONLY并发选项，否则可以更新所有的列。
备注
DECLARECURSOR定义Transact-SQL服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。OPEN语句填充结果集，FETCH从结果集返回行。CLOSE语句释放与游标关联的当前结果集。DEALLOCATE语句释放游标所使用的资源。
DECLARECURSOR语句的第一种格式使用SQL-92语法声明游标行为。DECLARECURSOR的第二种格式使用Transact-SQL扩展插件，这些扩展插件允许您使用在ODBC或ADO的数据库API游标函数中所使用的相同游标类型来定义游标。
不能混淆这两种格式。如果在CURSOR关键字的前面指定SCROLL或INSENSITIVE关键字，则不能在CURSOR和FORselect_statement关键字之间使用任何关键字。如果在CURSOR和FORselect_statement关键字之间指定任何关键字，则不能在CURSOR关键字的前面指定SCROLL或INSENSITIVE。
如果使用Transact-SQL语法的DECLARECURSOR不指定READ_ONLY、OPTIMISTIC或SCROLL_LOCKS，则默认值如下：
如果SELECT语句不支持更新（由于权限不够、访问的远程表不支持更新等等），则游标为READ_ONLY。
STATIC和FAST_FORWARD游标默认为READ_ONLY。
DYNAMIC和KEYSET游标默认为OPTIMISTIC。
游标名称只能被其他Transact-SQL语句引用。它们不能被数据库API函数引用。例如，声明游标之后，不能通过OLEDB、ODBC或ADO函数或方法引用游标名称。不能使用提取函数或API的方法来提取游标行；只能通过Transact-SQLFETCH语句提取这些行。
在声明游标后，可使用下列系统存储过程确定游标的特性。
系统存储过程说明
sp_cursor_list
返回当前在连接上可视的游标列表及其特性。
sp_describe_cursor
说明游标属性，例如是只前推的游标还是滚动游标。
sp_describe_cursor_columns
说明游标结果集中的列的属性。
sp_describe_cursor_tables
说明游标所访问的基表。
在声明游标的select_statement中可以使用变量。游标变量值在声明游标后不发生更改。在SQLServer版本6.5以及早期版本中，每次重新打开游标时都会重新刷新变量值。
权限
默认情况下，将DECLARECURSOR权限授予对游标中所使用的视图、表和列具有SELECT权限的任何用户。
示例
A.使用简单游标和语法
在打开该游标时所生成的结果集包括表中的所有行和所有列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为未指定SCROLL选项，所以FETCHNEXT是唯一可用的提取选项。
DECLAREvend_cursorCURSOR
FORSELECT*FROMPurchasing.Vendor
OPENvend_cursor
FETCHNEXTFROMvend_cursor
B.使用嵌套游标生成报表输出
以下示例显示如何嵌套游标以生成复杂的报表。为每个供应商声明内部游标。
SETNOCOUNTON
DECLARE@vendor_idint,@vendor_namenvarchar(50),
@messagevarchar(80),@productnvarchar(50)
PRINT'--------VendorProductsReport--------'
DECLAREvendor_cursorCURSORFOR
SELECTVendorID,Name
FROMPurchasing.Vendor
WHEREPreferredVendorStatus=1
ORDERBYVendorID
OPENvendor_cursor
FETCHNEXTFROMvendor_cursor
INTO@vendor_id,@vendor_name
WHILE@@FETCH_STATUS=0
BEGIN
PRINT''
SELECT@message='-----ProductsFromVendor:'+
@vendor_name
PRINT@message
--Declareaninnercursorbased
--onvendor_idfromtheoutercursor.
DECLAREproduct_cursorCURSORFOR
SELECTv.Name
FROMPurchasing.ProductVendorpv,Production.Productv
WHEREpv.ProductID=v.ProductIDAND
pv.VendorID=@vendor_id--Variablevaluefromtheoutercursor
OPENproduct_cursor
FETCHNEXTFROMproduct_cursorINTO@product
IF@@FETCH_STATUS%26lt;%26gt;0
PRINT'%26lt;%26lt;None%26gt;%26gt;'
WHILE@@FETCH_STATUS=0
BEGIN
SELECT@message=''+@product
PRINT@message
FETCHNEXTFROMproduct_cursorINTO@product
END
CLOSEproduct_cursor
DEALLOCATEproduct_cursor
--Getthenextvendor.
FETCHNEXTFROMvendor_cursor
INTO@vendor_id,@vendor_name
END
CLOSEvendor_cursor
DEALLOCATEvendor_cursor
