SQL2005游标的使用

以前很少用到游标,因为有人提到用游标的效率比较低.不过我想既然有,应该自有他的用处.先从MSDN上来看游标的使用帮助.

SQL 92 Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
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 ] ] ]
[;]在声明游标后，可使用下列系统存储过程确定游标的特性。

系统存储过程  说明 
sp_cursor_list
 返回当前在连接上可视的游标列表及其特性。
 
sp_describe_cursor
 说明游标属性，例如是只前推的游标还是滚动游标。
 
sp_describe_cursor_columns
 说明游标结果集中的列的属性。
 
sp_describe_cursor_tables
 说明游标所访问的基表。
 

在声明游标的 select_statement 中可以使用变量。游标变量值在声明游标后不发生更改。在 SQL Server 版本 6.5 以及早期版本中，每次重新打开游标时都会重新刷新变量值。

cursor_name

所定义的 Transact-SQL 服务器游标的名称。cursor_name 必须符合标识符规则。有关标识符规则的详细信息，请参阅使用标识符作为对象名称。

INSENSITIVE

定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用 SQL-92 语法时，如果省略 INSENSITIVE，则已提交的（任何用户）对基础表的删除和更新都反映在后面的提取中。

SCROLL

指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未在 SQL-92 DECLARE CURSOR 中指定 SCROLL，则 NEXT 是唯一支持的提取选项。如果也指定了 FAST_FORWARD，则不能指定 SCROLL。

select_statement

定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

Microsoft 如果 select_statement 中的子句与所请求的游标类型的功能有冲突，则 SQL Server 会将游标隐式转换为其他类型。有关详细信息，请参阅使用隐式游标转换。

READ ONLY

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

UPDATE [OF column_name [,...n]]

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

cursor_name

所定义的 Transact-SQL 服务器游标的名称。cursor_name 必须符合标识符规则。有关标识符规则的详细信息，请参阅使用标识符作为对象名称。

LOCAL

指定对于在其中创建的批处理、存储过程或触发器来说，该游标的作用域是局部的。该游标名称仅在这个作用域内有效。在批处理、存储过程、触发器或存储过程 OUTPUT 参数中，该游标可由局部游标变量引用。OUTPUT 参数用于将局部游标传递回调用批处理、存储过程或触发器，它们可在存储过程终止后给游标变量分配参数使其引用游标。除非 OUTPUT 参数将游标传递回来，否则游标将在批处理、存储过程或触发器终止时隐式释放。如果 OUTPUT 参数将游标传递回来，则游标在最后引用它的变量释放或离开作用域时释放。

GLOBAL

指定该游标的作用域对来说连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在断开连接时隐式释放。

注意： 
如果 GLOBAL 和 LOCAL 参数都未指定，则默认值由 default to local cursor 数据库选项的设置控制。在 SQL Server 7.0 版中，该选项默认为 FALSE，以便与 SQL Server 的早期版本相匹配，在早期版本中，所有游标都是全局的。该选项的默认值在以后的 SQL Server 版本中可能会更改。有关详细信息，请参阅“设置数据库选项”。 

FORWARD_ONLY

指定游标只能从第一行滚动到最后一行。FETCH NEXT 是唯一受支持的提取选项。如果在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET 和 DYNAMIC 关键字，则游标作为 DYNAMIC 游标进行操作。如果 FORWARD_ONLY 和 SCROLL 均未指定，则除非指定 STATIC、KEYSET 或 DYNAMIC 关键字，否则默认为 FORWARD_ONLY。STATIC、KEYSET 和 DYNAMIC 游标默认为 SCROLL。与 ODBC 和 ADO 这类数据库 API 不同，STATIC、KEYSET 和 DYNAMIC Transact-SQL 游标支持 FORWARD_ONLY。

STATIC

定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。

KEYSET

指定当游标打开时，游标中行的成员身份和顺序已经固定。对行进行唯一标识的键集内置在 tempdb 内一个称为 keyset 的表中。

注意： 
如果查询引用了至少一个无唯一索引的表，则键集游标将转换为静态游标。 

对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交）可以在用户滚动游标时看到。其他用户执行的插入是不可见的（不能通过 Transact-SQL 服务器游标执行插入）。如果删除行，则在尝试提取行时返回值为 -2 的 @@FETCH_STATUS。从游标以外更新键值类似于删除旧行然后再插入新行。具有新值的行是不可见的，并在尝试提取具有旧值的行时，将返回值为 -2 的 @@FETCH_STATUS。如果通过指定 WHERE CURRENT OF 子句利用游标来完成更新，则新值是可见的。

DYNAMIC

定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持 ABSOLUTE 提取选项。

FAST_FORWARD

指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。如果指定了 SCROLL 或 FOR_UPDATE，则不能也指定 FAST_FORWARD。

注意： 
在 SQL Server 2000 中，FAST_FORWARD 和 FORWARD_ONLY 游标选项是相互排斥的。如果指定了其中的一个，则不能指定另一个，否则会引发错误。在 SQL Server 2005 中，这两个关键字可以用在同一个 DECLARE CURSOR 语句中。 

READ_ONLY

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

SCROLL_LOCKS

指定通过游标进行的定位更新或删除保证会成功。将行读取到游标中以确保它们对随后的修改可用时，Microsoft SQL Server 将锁定这些行。如果还指定了 FAST_FORWARD，则不能指定 SCROLL_LOCKS。

OPTIMISTIC

指定如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不会成功。当将行读入游标时 SQL Server 不会锁定行。相反，SQL Server 使用 timestamp 列值的比较，或者如果表没有 timestamp 列，则使用校验和值，以确定将行读入游标后是否已修改该行。如果已修改该行，则尝试进行的定位更新或删除将失败。如果还指定了 FAST_FORWARD，则不能指定 OPTIMISTIC。

TYPE_WARNING

指定如果游标从所请求的类型隐式转换为另一种类型，则向客户端发送警告消息。

select_statement

定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

如果 select_statement 中的子句与所请求的游标类型的功能有冲突，则 SQL Server 会将游标隐式转换为其他类型。有关详细信息，请参阅“隐式游标转换”。

FOR UPDATE [OF column_name [,...n]]

定义游标中可更新的列。如果提供了 OF column_name [,...n]，则只允许修改列出的列。如果指定了 UPDATE，但未指定列的列表，则除非指定了 READ_ONLY 并发选项，否则可以更新所有的列。

默认情况下，将 DECLARE CURSOR 权限授予对游标中所使用的视图、表和列具有 SELECT 权限的任何用户

A. 使用简单游标和语法

在打开该游标时所生成的结果集包括表中的所有行和所有列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为未指定 SCROLL 选项，所以 FETCH NEXT 是唯一可用的提取选项。

DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursorB. 使用嵌套游标生成报表输出

以下示例显示如何嵌套游标以生成复杂的报表。为每个供应商声明内部游标。

SET NOCOUNT ON

DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50)

PRINT '-------- Vendor Products Report --------'

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' +
@vendor_name

PRINT @message

-- Declare an inner cursor based  
-- on vendor_id from the outer cursor.

DECLARE product_cursor CURSOR FOR
SELECT v.Name
FROM Purchasing.ProductVendor pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id-- Variable value from the outer cursor

OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product

IF @@FETCH_STATUS <> 0
PRINT '         <<None>>'    

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = '         ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product

END

CLOSE product_cursor
DEALLOCATE product_cursor

-- Get the next vendor.
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor原来游标还可以嵌套使用..呵呵...

个人总结:

1.使用完后要记得close 掉

2.并释放掉资源 deallocate