DECLARE CURSOR
DECLARE CURSOR
定义 Transact-SQL 服务器游标的特性，例如游标的滚动行为和用于生成游标对其进行操作的结果集的查询。DECLARE CURSOR 接受基于 SQL-92 标准的语法和使用一组 Transact-SQL 扩展的语法。
SQL-92 语法
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
Transact-SQL 扩展语法
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 ] ] ]
SQL-92 参数
cursor_name
是所定义的 Transact-SQL 服务器游标名称。cursor_name 必须遵从标识符规则。有关标识符规则的更多信息，请参见使用标识符。
INSENSITIVE
定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用 SQL-92 语法时，如果省略 INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。
SCROLL
指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果在 SQL-92 DECLARE CURSOR 中未指定 SCROLL，则 NEXT 是唯一支持的提取选项。如果指定 SCROLL，则不能也指定 FAST_FORWARD。
select_statement
是定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。
如果 select_statement 中的子句与所请求的游标类型的功能发生冲突，则 Microsoft® 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 中未指定列的列表，则可以更新所有列。
Transact-SQL 扩展参数
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 早期版本中所有游标都是全局的。该选项的默认值在以后的 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。FAST_FORWARD 和 FORWARD_ONLY 是互斥的；如果指定一个，则不能指定另一个。
STATIC
定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。
KEYSET
指定当游标打开时，游标中行的成员资格和顺序已经固定。对行进行唯一标识的键集内置在 tempdb 内一个称为 keyset 的表中。对基表中的非键值所做的更改（由游标所有者更改或由其它用户提交）在用户滚动游标时是可视的。其他用户进行的插入是不可视的（不能通过 Transact-SQL 服务器游标进行插入）。如果某行已删除，则对该行的提取操作将返回 @@FETCH_STATUS 值 -2。从游标外更新键值类似于删除旧行后接着插入新行的操作。含有新值的行不可视，对含有旧值的行的提取操作将返回 @@FETCH_STATUS 值 -2。如果通过指定 WHERE CURRENT OF 子句用游标完成更新，则新值可视。
DYNAMIC
定义一个游标，以反映在滚动游标时对结果集内的行所做的所有数据更改。行的数据值、顺序和成员在每次提取时都会更改。动态游标不支持 ABSOLUTE 提取选项。
FAST_FORWARD
指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。如果指定 FAST_FORWARD，则不能也指定 SCROLL 或 FOR_UPDATE。FAST_FORWARD 和 FORWARD_ONLY 是互斥的；如果指定一个，则不能指定另一个。
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 将游标隐性转换成另一种类型。有关更多信息，请参见隐性游标转换。
UPDATE [OF column_name [,...n]]
定义游标内可更新的列。如果提供了 OF column_name [,...n]，则只允许修改列出的列。如果在 UPDATE 中未指定列的列表，除非指定了 READ_ONLY 并发选项，否则所有列均可更新。
注释
DECLARE CURSOR 定义 Transact-SQL 服务器游标的特性，例如游标的滚动行为和用于生成游标对其进行操作的结果集的查询。OPEN 语句填充结果集，FETCH 从结果集返回行。CLOSE 语句释放与游标关联的当前结果集。DEALLOCATE 语句释放游标所使用的资源。
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 语句提取。
在声明游标后，可使用下列系统存储过程确定游标的特性。
| 系统存储过程 | 描述 |
|---|---|
| sp_cursor_list | 返回当前在连接上可视的游标列表及其特性。 |
| sp_describe_cursor | 描述游标特性，比如是只进游标还是滚动游标。 |
| sp_describe_cursor_columns | 描述游标结果集中的列的特性。 |
| sp_describe_cursor_tables | 描述游标所访问的基表。 |
变量可作为声明游标的 select_statement 的一部分使用。然而，在游标声明之后对那些变量的更改将不会对游标的操作产生影响。
权限
默认情况下，将 DECLARE CURSOR 权限授予对游标中所使用的视图、表和列有 SELECT 权限的任何用户。
示例
A. 使用简单游标和语法
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。
DECLARE authors_cursor CURSOR FOR SELECT * FROM authorsOPEN authors_cursorFETCH NEXT FROM authors_cursor
B. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。
SET NOCOUNT ONDECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40), @message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lnameFROM authorsWHERE state = "UT"ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0BEGIN PRINT " " SELECT @message = "----- Books by Author: " + @au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based -- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR SELECT t.title FROM titleauthor ta, titles t WHERE ta.title_id = t.title_id AND ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0 PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @title PRINT @message FETCH NEXT FROM titles_cursor INTO @title END CLOSE titles_cursor DEALLOCATE titles_cursor -- Get the next author. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameENDCLOSE authors_cursorDEALLOCATE authors_cursorGO-------- Utah Authors report -------- ----- Books by Author: Anne Ringer The Gourmet Microwave Is Anger the Enemy? ----- Books by Author: Albert Ringer Is Anger the Enemy? Life Without Fear
使用临时表
临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。 临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。
扫一扫
169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
