Transact-SQL 游标

项目
12/15/2008

Transact-SQL 游标主要用于存储过程、触发器和 Transact-SQL 脚本中，它们使结果集的内容可用于其他 Transact-SQL 语句。

在存储过程或触发器中使用 Transact-SQL 游标的典型过程为：

声明 Transact-SQL 变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从列数据类型隐式转换得到的数据类型。
使用 DECLARE CURSOR 语句将 Transact-SQL 游标与 SELECT 语句相关联。另外，DECLARE CURSOR 语句还定义游标的特性，例如游标名称以及游标是只读还是只进。
使用 OPEN 语句执行 SELECT 语句并填充游标。
使用 FETCH INTO 语句提取单个行，并将每列中的数据移至指定的变量中。然后，其他 Transact-SQL 语句可以引用那些变量来访问提取的数据值。Transact-SQL 游标不支持提取行块。
使用 CLOSE 语句结束游标的使用。关闭游标可以释放某些资源，例如游标结果集及其对当前行的锁定，但如果重新发出一个 OPEN 语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用该游标的名称。DEALLOCATE 语句则完全释放分配给游标的资源，包括游标名称。释放游标后，必须使用 DECLARE 语句来重新生成游标。

监视 Transact-SQL 游标的活动

可以使用 sp_cursor_list 系统存储过程来获得对当前连接可见的游标列表，使用sp_describe_cursor、sp_describe_cursor_columns 和 sp_describe_cursor_tables 来确定游标的特性。

打开游标后，@@CURSOR_ROWS 函数或者由 sp_cursor_list 或 sp_describe_cursor 返回的 cursor_rows 列会指明游标中的行数。

在执行每个 FETCH 语句后，@@FETCH_STATUS 都会更新以反映最后一次提取的状态。也可以从由 sp_describe_cursor 返回的 fetch_status 列中获取该状态信息。@@FETCH_STATUS 会报告诸如超出游标中第一行或最后一行的提取之类的状态。@@FETCH_STATUS 对于连接来说是全局性的，并在为连接打开了游标时由每次提取进行重置。如果必须在以后了解状态，就需要在连接中执行其他语句之前，将 @@FETCH_STATUS 保存在一个用户变量中。即使下一语句不是 FETCH，也可能是 INSERT、UPDATE 或 DELETE 这些语句，它们能够触发包含重置 @@FETCH_STATUS 的 FETCH 语句的触发器。sp_describe_cursor 返回的 fetch_status 列对于指定的游标来说是确定的，不受那些引用其他游标的 FETCH 语句的影响，但 sp_describe_cursor 会受引用相同游标的 FETCH 语句的影响，因此使用时必须注意。

完成 FETCH 后，游标将定位在提取的行上。提取的行称为当前行。如果游标没有声明为只读游标，就可以执行包含 WHERE CURRENT OF cursor_name 子句的 UPDATE 语句或 DELETE 语句来修改当前行。

由 DECLARE CURSOR 语句赋给 Transact-SQL 游标的名称可以是全局名称，也可以是局部名称。全局游标名称可由任何位于同一连接中的批处理、存储过程或触发器引用。局部游标名称不能在声明游标的批处理、存储过程或触发器之外被引用。触发器和存储过程中的局部游标因而可以避免来自存储过程或触发器外部的无意引用。

使用游标变量

Microsoft SQL Server 2005 也支持 cursor 数据类型的变量。可以通过两种方法将游标与 cursor 变量相关联：

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR

DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks.Person.Contact

SET @MyVariable = MyCursor
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR

SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks.Person.Contact;
DEALLOCATE MyCursor;

游标与 cursor 变量相关联之后，在 Transact-SQL 游标语句中就可以使用 cursor 变量而非使用游标名称。存储过程输出参数也可指定为 cursor 数据类型，并与游标相关联。这就允许存储过程有节制地公开其局部游标。

引用 Transact-SQL 游标

Transact-SQL 游标名称和变量只能由 Transact-SQL 语句引用，而不能由 OLE DB、ODBC 和 ADO 的 API 函数引用。例如，如果使用 DECLARE CURSOR 和 OPEN 定义并打开 Transact-SQL 游标，就无法使用 ODBC SQLFetch 函数或 SQLFetchScroll 函数来从 Transact-SQL 游标中提取行。需要游标处理且使用这些 API 的应用程序应该使用在数据库 API 中生成的游标支持而非使用 Transact-SQL 游标。

可以通过使用 FETCH 并将 FETCH 返回的每列绑定到程序变量，在应用程序中使用 Transact-SQL 游标。但是，Transact-SQL FETCH 不支持批处理，因此，这是将数据返回给应用程序的效率最低的方法。每提取一行均需往返服务器一次。使用在数据库 API（支持多行提取）中生成的游标功能更为有效。

包含在存储过程和触发器中的 Transact-SQL 游标效率极高。这是因为所有操作都编译到服务器上的一个执行计划内，不存在与行提取关联的网络流量。