DECLARE CURSOR (Transact-SQL)DECLARE CURSOR (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

定义 Transact-SQLTransact-SQL 服务器游标的属性,例如游标的滚动行为和用于生成游标所操作的结果集的查询。Defines the attributes of a Transact-SQLTransact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR 既接受基于 ISO 标准的语法,也接受使用一组 Transact-SQLTransact-SQL 扩展的语法。DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQLTransact-SQL extensions.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

ISO 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 ] ] ]  
[;]  

参数Arguments

cursor_name cursor_name
Transact-SQLTransact-SQL 服务器游标定义的名称。Is the name of the Transact-SQLTransact-SQL server cursor defined. cursor_name 必须符合有关标识符的规则 。cursor_name must conform to the rules for identifiers.

INSENSITIVEINSENSITIVE
定义一个游标,以创建将由该游标使用的数据的临时副本。Defines a cursor that makes a temporary copy of the data to be used by the cursor. 对游标的所有请求都从 tempdb 中的这一临时表中得到应答;因此,在对该游标进行提取操作时返回的数据中不反映对基表所做的修改,并且该游标不允许修改 。All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. 使用 ISO 语法时,如果省略 INSENSITIVE,则已提交的(任何用户)对基础表的删除和更新则会反映在后面的提取操作中。When ISO syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.

SCROLLSCROLL
指定所有的提取选项(FIRSTLASTPRIORNEXTRELATIVEABSOLUTE)均可用。Specifies that all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. 如果未在 ISO DECLARE CURSOR 中指定 SCROLL,则 NEXT 是唯一支持的提取选项。If SCROLL is not specified in an ISO DECLARE CURSOR, NEXT is the only fetch option supported. 如果还指定了 FAST_FORWARD,则无法指定 SCROLLSCROLL cannot be specified if FAST_FORWARD is also specified. 如果未指定 SCROLL,则只有提取选项 NEXT 可用,且游标将变为 FORWARD_ONLYIf SCROLL is not specified then only the fetch option NEXT is available and the cursor becomes FORWARD_ONLY.

select_statement select_statement
是定义游标结果集的标准 SELECT 语句。Is a standard SELECT statement that defines the result set of the cursor. 在游标声明的 select_statement 中不允许使用关键字 FOR BROWSEINTOThe keywords FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.

如果 select_statement 中的子句与所请求的游标类型的功能有冲突,则 SQL ServerSQL Server 会将游标隐式转换为其他类型 。SQL ServerSQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type.

READ ONLYREAD ONLY
禁止通过该游标进行更新。Prevents updates made through this cursor. 无法在 UPDATEDELETE 语句的 WHERE CURRENT OF 子句中引用游标。The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. 该选项优先于要更新的游标的默认功能。This option overrides the default capability of a cursor to be updated.

UPDATE [OF column_name [,...n]] UPDATE [OF column_name [,...n]]
定义游标中可更新的列。Defines updatable columns within the cursor. 如果指定了 OF <column_name> [, <... n>],则只允许修改所列出的列。If OF <column_name> [, <... n>] is specified, only the columns listed allow modifications. 如果指定了 UPDATE,但未指定列的列表,则可以更新所有列。If UPDATE is specified without a column list, all columns can be updated.

cursor_name cursor_name
Transact-SQLTransact-SQL 服务器游标定义的名称。Is the name of the Transact-SQLTransact-SQL server cursor defined. cursor_name 必须符合有关标识符的规则 。cursor_name must conform to the rules for identifiers.

LOCALLOCAL
指定该游标的范围对在其中创建它的批处理、存储过程或触发器是局部的。Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. 该游标名称仅在这个作用域内有效。The cursor name is only valid within this scope. 在批处理、存储过程、触发器或存储过程 OUTPUT 参数中,该游标可由局部游标变量引用。The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. OUTPUT 参数用于将局部游标传递回调用批处理、存储过程或触发器,它们可在存储过程终止后给游标变量分配参数使其引用游标。An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. 除非 OUTPUT 参数将游标传递回来,否则游标将在批处理、存储过程或触发器终止时隐式释放。The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. 如果 OUTPUT 参数将游标传递回来,则游标在最后引用它的变量释放或离开作用域时释放。If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

GLOBALGLOBAL
指定该游标范围对连接是全局的。Specifies that the scope of the cursor is global to the connection. 在由此连接执行的任何存储过程或批处理中,都可以引用该游标名称。The cursor name can be referenced in any stored procedure or batch executed by the connection. 该游标仅在断开连接时隐式释放。The cursor is only implicitly deallocated at disconnect.

备注

如果 GLOBALLOCAL 参数都未指定,则默认值由“默认为本地游标”数据库选项的设置控制 。If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option.

FORWARD_ONLYFORWARD_ONLY
指定游标只能向前移动,并从第一行滚动到最后一行。Specifies that the cursor can only move forward and be scrolled from the first to the last row. FETCH NEXT 是唯一支持的提取选项。FETCH NEXT is the only supported fetch option. 对所有由当前用户发出(或由其他用户提交)并影响结果集中的行的插入、更新和删除语句,其效果在提取这些行时是可见的。All insert, update, and delete statements made by the current user (or committed by other users) that affect rows in the result set are visible as the rows are fetched. 由于游标无法向后滚动,但是,在提取行后对数据库中的行进行的更改通过游标均不可见。Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor. 默认只进游标是动态的,这意味着处理当前行时会检测到所有更改。Forward-only cursors are dynamic by default, meaning that all changes are detected as the current row is processed. 这可实现更快速的游标打开,并使结果集能够显示对基础表所做的更新。This provides faster cursor opening and enables the result set to display updates made to the underlying tables. 尽管只进游标不支持向后滚动,但应用程序可通过关闭并重新打开游标返回到结果集的开头。While forward-only cursors do not support backward scrolling, applications can return to the beginning of the result set by closing and reopening the cursor. 如果指定了 FORWARD_ONLY 而没有指定 STATICKEYSETDYNAMIC 关键字,则游标作为动态游标进行操作。If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a dynamic cursor. 如果未指定 FORWARD_ONLYSCROLL,则默认为 FORWARD_ONLY,除非指定了关键字 STATICKEYSETDYNAMICWhen neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATICKEYSETDYNAMIC 游标默认为 SCROLLSTATIC, KEYSET, and DYNAMIC cursors default to SCROLL. 与 ODBC 和 ADO 等数据库 API 不同,STATICKEYSETDYNAMIC Transact-SQLTransact-SQL游标支持 FORWARD_ONLYUnlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQLTransact-SQL cursors.

STATICSTATIC
指定游标始终以第一次打开时的样式显示结果集,并制作数据的临时副本,供游标使用。Specifies that the cursor always displays the result set as it was when the cursor was first opened, and makes a temporary copy of the data to be used by the cursor. 对游标的所有请求都通过 tempdb 中的这个临时表进行答复 。All requests to the cursor are answered from this temporary table in tempdb. 因此,对基表所做的插入、更新和删除操作不在对此游标所做的提取操作返回的数据中反映,并且在该游标打开后,不会检测对结果集的成员、顺序或值所做的更改。Therefore inserts, updates, and deletes made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not detect changes made to the membership, order, or values of the result set after the cursor is opened. 尽管不需要,但静态游标可检测其自己的更新、删除和插入。Static cursors may detect their own updates, deletes, and inserts, although they are not required to do so. 例如,假定静态游标提取行,然后另一个应用程序将更新该行。For example, suppose a static cursor fetches a row, and another application then updates that row. 如果应用程序通过静态游标重新提取行,尽管更改由其他应用程序执行,但看到的值将保持不变。If the application refetches the row from the static cursor, the values it sees are unchanged, despite the changes made by the other application. 支持所有类型的滚动。All types of scrolling are supported.

KEYSETKEYSET
指定当游标打开时,游标中行的成员身份和顺序已经固定。Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. 对行进行唯一标识的键集内置在 tempdb 内一个称为 keyset 的表中 。The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. 此游标在其检测更改的功能方面,提供介于静态和动态游标之间的功能。This cursor provides functionality between a static and a dynamic cursor in its ability to detect changes. 比如静态游标,它不会始终检测对结果集的成员身份和顺序的更改。Like a static cursor, it does not always detect changes to the membership and order of the result set. 比如动态游标,它会检测对结果集中的行值的更改。Like a dynamic cursor, it does detect changes to the values of rows in the result set. 由键集驱动的游标由一组唯一标识符(键)控制,这组键称为键集。Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. 键是根据以唯一方式标识结果集中各行的一组列生成的。The keys are built from a set of columns that uniquely identify the rows in the result set. 键集是查询语句返回的所有行中的一组键值。The keyset is the set of key values from all the rows returned by the query statement. 使用由键集驱动的游标,为游标中的每行生成和保存一个键,并将其存储在客户端工作站或服务器上。With keyset-driven cursors, a key is built and saved for each row in the cursor and stored either on the client workstation or on the server. 访问每行时,存储的密钥可用来从数据源提取当前数据值。When you access each row, the stored key is used to fetch the current data values from the data source. 在由键集驱动的游标中,如果键集完全填充,将冻结结果集成员身份。In a keyset-driven cursor, result set membership is frozen when the keyset is fully populated. 此后,在重新打开结果集前,都不会执行影响成员身份的添加或更新操作。Thereafter, additions or updates that affect membership are not a part of the result set until it is reopened. 用户滚动浏览结果集时,对数据值的更改(由键集所有者或其他进程执行)是可见的:Changes to data values (made either by the keyset owner or other processes) are visible as the user scrolls through the result set:

  • 如果删除某一行,尝试提取该行时将返回为 -2 的 @@FETCH_STATUS,因为已删除的行在结果集中将显示为空白。If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2 because the deleted row appears as a gap in the result set. 行键存在于键集中,但行不再存在于结果集中。The key for the row exists in the keyset, but the row no longer exists in the result set.
  • 游标外所做的插入(由其他进程执行)仅在关闭并重新打开游标后可见。Inserts made outside the cursor (by other processes) are visible only if the cursor is closed and reopened. 游标内部所做的插入在结果集的末尾可见。Inserts made from inside the cursor are visible at the end of the result set.
  • 从游标外部更新键值类似于删除旧行后再插入新行。Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. 具有新值的行不可见,且尝试提取具有旧值的行时返回的 @@FETCH_STATUS 为 -2。The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. 如果通过指定 WHERE CURRENT OF 子句来通过游标执行更新,则新值可见。The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

备注

如果查询引用了至少一个无唯一索引的表,则键集游标将转换为静态游标。If the query references at least one table without a unique index, the keyset cursor is converted to a static cursor.

DYNAMICDYNAMIC
定义一个游标,无论更改是发生于游标内部还是由游标外的其他用户执行,在你四处滚动游标并提取新纪录时,该游标均能反映对其结果集中的行所做的所有数据更改。Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor and fetch a new record, regardless of whether the changes occur from inside the cursor or by other users outside the cursor. 因此,所有用户做的全部 UPDATE、INSERT 和 DELETE 语句均通过游标可见。Therefore all insert, update, and delete statements made by all users are visible through the cursor. 行的数据值、顺序和成员身份在每次提取时都会更改。The data values, order, and membership of the rows can change on each fetch. 动态游标不支持 ABSOLUTE 提取选项。The ABSOLUTE fetch option is not supported with dynamic cursors. 在游标外部所做的更新直到提交时才可见(除非将游标的事务隔离级别设为 UNCOMMITTED)。Updates made outside the cursor are not visible until they are committed (unless the cursor transaction isolation level is set to UNCOMMITTED). 例如,假设动态游标提取两行,然后另一个应用程序将更新这两行之一并删除另一行。For example, suppose a dynamic cursor fetches two rows and another application then updates one of those rows and deletes the other. 然后,如果动态游标提取这两行,它将找不到已删除的行,但会显示已更新行的新值。If the dynamic cursor then fetches those rows, it will not find the deleted row, but it will display the new values for the updated row.

FAST_FORWARDFAST_FORWARD
指定已启用了性能优化的 FORWARD_ONLYREAD_ONLY 游标。Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. 如果还指定了 SCROLLFOR_UPDATE,则无法指定 FAST_FORWARDFAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. 此类型的游标不允许从游标内修改数据。This type of cursor does not allow data modifications from inside the cursor.

备注

可以在相同的 DECLARE CURSOR 语句中使用 FAST_FORWARDFORWARD_ONLYBoth FAST_FORWARD and FORWARD_ONLY can be used in the same DECLARE CURSOR statement.

READ_ONLYREAD_ONLY
禁止通过该游标进行更新。Prevents updates made through this cursor. 无法在 UPDATEDELETE 语句的 WHERE CURRENT OF 子句中引用游标。The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. 该选项优先于要更新的游标的默认功能。This option overrides the default capability of a cursor to be updated.

SCROLL_LOCKSSCROLL_LOCKS
指定通过游标进行的定位更新或删除一定会成功。Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. 将行读入游标时 SQL ServerSQL Server 将锁定这些行,以确保随后可对它们进行修改。SQL ServerSQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. 如果还指定了 FAST_FORWARDSTATIC,则无法指定 SCROLL_LOCKSSCROLL_LOCKS cannot be specified if FAST_FORWARD or STATIC is also specified.

OPTIMISTICOPTIMISTIC
指定如果行自读入游标以来已得到更新,则通过游标进行的定位更新或定位删除不成功。Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. 当将行读入游标时,SQL ServerSQL Server 不锁定行。SQL ServerSQL Server does not lock rows as they are read into the cursor. 相反,它使用 timestamp 列值的比较,或者如果表没有 timestamp 列则使用校验和值,以确定将行读入游标后是否已修改该行 。It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. 如果已修改该行,尝试进行的定位更新或定位删除将失败。If the row was modified, the attempted positioned update or delete fails. 如果还指定了 FAST_FORWARD,则无法指定 OPTIMISTICOPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNINGTYPE_WARNING
指定如果游标从所请求的类型隐式转换为另一种类型,则向客户端发送警告消息。Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another.

select_statement select_statement
定义游标结果集的标准 SELECT 语句。Is a standard SELECT statement that defines the result set of the cursor. 在游标声明的 select_statement 中不允许使用关键字 COMPUTECOMPUTE BYFOR BROWSEINTOThe keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.

备注

可以在游标声明中使用查询提示;但如果还使用 FOR UPDATE OF 子句,请在 FOR UPDATE OF 之后指定 OPTION (<query_hint>)You can use a query hint within a cursor declaration; however, if you also use the FOR UPDATE OF clause, specify OPTION (<query_hint>) after FOR UPDATE OF.

如果 select_statement 中的子句与所请求的游标类型的功能有冲突,则 SQL ServerSQL Server 会将游标隐式转换为其他类型 。SQL ServerSQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. 有关详细信息,请参阅“隐式游标转换”。For more information, see Implicit Cursor Conversions.

FOR UPDATE [OF column_name [,...n]] FOR UPDATE [OF column_name [,...n]]
定义游标中可更新的列。Defines updatable columns within the cursor. 如果提供了 OF <column_name> [, <... n>],则只允许修改所列出的列。If OF <column_name> [, <... n>] is supplied, only the columns listed allow modifications. 如果指定了 UPDATE,但未指定列的列表,则除非指定了 READ_ONLY 并发选项,否则可以更新所有的列。If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

RemarksRemarks

DECLARE CURSOR 定义了 Transact-SQLTransact-SQL 服务器游标的属性,例如游标的滚动行为和用于生成游标所操作的结果集的查询。DECLARE CURSOR defines the attributes of a Transact-SQLTransact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. OPEN 语句填充结果集,FETCH 返回结果集中的行。The OPEN statement populates the result set, and FETCH returns a row from the result set. CLOSE 语句释放与游标关联的当前结果集。The CLOSE statement releases the current result set associated with the cursor. DEALLOCATE 语句释放游标所使用的资源。The DEALLOCATE statement releases the resources used by the cursor.

DECLARE CURSOR 语句的第一种格式采用 ISO 语法来声明游标行为。The first form of the DECLARE CURSOR statement uses the ISO syntax for declaring cursor behaviors. DECLARE CURSOR 的第二种格式使用 Transact-SQLTransact-SQL 扩展插件,这些扩展插件允许使用在 ODBC 或 ADO 的数据库 API 游标函数中所使用的相同游标类型来定义游标。The second form of DECLARE CURSOR uses Transact-SQLTransact-SQL extensions that allow you to define cursors using the same cursor types used in the database API cursor functions of ODBC or ADO.

不能混淆这两种格式。You cannot mix the two forms. 如果在 CURSOR 关键字前指定 SCROLLINSENSITIVE 关键字,则不能在 CURSORFOR <select_statement> 关键字之间使用任何关键字。If you specify the SCROLL or INSENSITIVE keywords before the CURSOR keyword, you cannot use any keywords between the CURSOR and FOR <select_statement> keywords. 如果在 CURSORFOR <select_statement> 关键字之间指定了任何关键字,则无法在 CURSOR 关键字前指定 SCROLLINSENSITIVEIf you specify any keywords between the CURSOR and FOR <select_statement> keywords, you cannot specify SCROLL or INSENSITIVE before the CURSOR keyword.

如果使用 Transact-SQLTransact-SQL 语法的 DECLARE CURSOR 未指定 READ_ONLYOPTIMISTICSCROLL_LOCKS,则默认值如下所示:If a DECLARE CURSOR using Transact-SQLTransact-SQL syntax does not specify READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS, the default is as follows:

  • 如果 SELECT 语句不支持更新(由于权限不够、访问的远程表不支持更新等等),则游标为 READ_ONLYIf the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.

  • STATICFAST_FORWARD 游标默认为 READ_ONLYSTATIC and FAST_FORWARD cursors default to READ_ONLY.

  • DYNAMICKEYSET 游标默认为 OPTIMISTICDYNAMIC and KEYSET cursors default to OPTIMISTIC.

游标名称只能被其他 Transact-SQLTransact-SQL 语句引用。Cursor names can be referenced only by other Transact-SQLTransact-SQL statements. 它们不能被数据库 API 函数引用。They cannot be referenced by database API functions. 例如,声明游标之后,不能通过 OLE DB、ODBC 或 ADO 函数或方法引用游标名称。For example, after declaring a cursor, the cursor name cannot be referenced from OLE DB, ODBC or ADO functions or methods. 不能使用 API 的提取函数或方法来提取游标行;只能通过 Transact-SQLTransact-SQL FETCH 语句提取这些行。The cursor rows cannot be fetched using the fetch functions or methods of the APIs; the rows can be fetched only by Transact-SQLTransact-SQL FETCH statements.

在声明游标后,可使用下列系统存储过程确定游标的特性。After a cursor has been declared, these system stored procedures can be used to determine the characteristics of the cursor.

系统存储过程System stored procedures 描述Description
sp_cursor_listsp_cursor_list 返回当前在连接上可视的游标列表及其特性。Returns a list of cursors currently visible on the connection and their attributes.
sp_describe_cursorsp_describe_cursor 说明游标属性,例如是只前推的游标还是滚动游标。Describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.
sp_describe_cursor_columnssp_describe_cursor_columns 说明游标结果集中的列的属性。Describes the attributes of the columns in the cursor result set.
sp_describe_cursor_tablessp_describe_cursor_tables 说明游标所访问的基表。Describes the base tables accessed by the cursor.

在声明游标的 select_statement 中可以使用变量 。Variables may be used as part of the select_statement that declares a cursor. 游标变量值在声明游标后不发生更改。Cursor variable values do not change after a cursor is declared.

权限Permissions

DECLARE CURSOR 的权限默认授予对游标中使用的视图、表和列具有 SELECT 权限的任何用户。Permissions of DECLARE CURSOR default to any user that has SELECT permissions on the views, tables, and columns used in the cursor.

限制和局限Limitations and Restrictions

不能在具有聚集列存储索引的表中使用游标或触发器。You cannot use cursors or triggers on a table with a clustered columnstore index. 此限制不适用于非聚集列存储索引;可以在具有非聚集列存储索引的表中使用游标和触发器。This restriction does not apply to nonclustered columnstore indexes; you can use cursors and triggers on a table with a nonclustered columnstore index.

示例Examples

A.A. 使用简单的游标和语法Using simple cursor and syntax

在打开该游标时所生成的结果集包括表中的所有行和所有列。The result set generated at the opening of this cursor includes all rows and all columns in the table. 可以更新该游标,并且所有的更新和删除都会在对该游标所做的提取操作中表现出来。This cursor can be updated, and all updates and deletes are represented in fetches made against this cursor. FETCH NEXT 是唯一可用的提取选项,因为未指定 SCROLL 选项。FETCH NEXT is the only fetch available because the SCROLL option has not been specified.

DECLARE vend_cursor CURSOR  
    FOR SELECT * FROM Purchasing.Vendor  
OPEN vend_cursor  
FETCH NEXT FROM vend_cursor;  

B.B. 使用嵌套游标生成报表输出Using nested cursors to produce report output

下例说明如何通过嵌套游标生成复杂的报表。The following example shows how cursors can be nested to produce complex reports. 为每个供应商声明内部游标。The inner cursor is declared for each vendor.

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;  

另请参阅See Also

@@FETCH_STATUS (Transact-SQL) @@FETCH_STATUS (Transact-SQL)
CLOSE (Transact-SQL) CLOSE (Transact-SQL)
游标 (Transact-SQL) Cursors (Transact-SQL)
DEALLOCATE (Transact-SQL) DEALLOCATE (Transact-SQL)
FETCH (Transact-SQL) FETCH (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)