游标Cursors

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

关系数据库中的操作会对整个行集起作用。Operations in a relational database act on a complete set of rows. 例如,由 SELECT 语句返回的行集包括满足该语句的 WHERE 子句中条件的所有行。For example, the set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. 这种由语句返回的完整行集称为结果集。This complete set of rows returned by the statement is known as the result set. 应用程序,特别是交互式联机应用程序,并不总能将整个结果集作为一个单元来有效地处理。Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. 这些应用程序需要一种机制以便每次处理一行或一部分行。These applications need a mechanism to work with one row or a small block of rows at a time. 游标就是提供这种机制的对结果集的一种扩展。Cursors are an extension to result sets that provide that mechanism.

游标通过以下方式来扩展结果处理:Cursors extend result processing by:

  • 允许定位在结果集的特定行。Allowing positioning at specific rows of the result set.

  • 从结果集的当前位置检索一行或一部分行。Retrieving one row or block of rows from the current position in the result set.

  • 支持对结果集中当前位置的行进行数据修改。Supporting data modifications to the rows at the current position in the result set.

  • 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.

  • 提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQLTransact-SQL 语句。Providing Transact-SQLTransact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.

提示

在某些情况下,如果表上有一个主键,可以使用 WHILE 循环代替游标,而不会产生游标的开销。In some scenarios, if there is a primary key on a table, a WHILE loop can be used instead of a cursor, without incurring in the overhead of a cursor. 然而,在某些情况下,游标不仅是不可避免的,而且实际上是不可或缺的。However, there are scenarios where cursors are not only unavoidable, they are actually needed. 在这种情况下,如果不需要基于游标来更新表,则使用“firehose”游标,即快进和只读游标 。When that is the case, if there is no requirement to update tables based on the cursor, then use firehose cursors, meaning fast-forward and read-only cursors.

游标实现Cursor Implementations

SQL ServerSQL Server 支持三种游标实现。supports three cursor implementations.

Transact-SQL 游标Transact-SQL cursors

Transact-SQLTransact-SQL 游标基于 DECLARE CURSOR 语法,主要用于 Transact-SQLTransact-SQL 脚本、存储过程和触发器。cursors are based on the DECLARE CURSOR syntax and used mainly in Transact-SQLTransact-SQL scripts, stored procedures, and triggers. Transact-SQLTransact-SQL 游标在服务器上实现,由从客户端发送到服务器的 Transact-SQLTransact-SQL 语句管理。cursors are implemented on the server and are managed by Transact-SQLTransact-SQL statements sent from the client to the server. 它们还可能包含在批处理、存储过程或触发器中。They may also be contained in batches, stored procedures, or triggers.

应用程序编程接口 (API) 服务器游标Application programming interface (API) server cursors

API 游标支持 OLE DB 和 ODBC 中的 API 游标函数。API cursors support the API cursor functions in OLE DB and ODBC. API 服务器游标在服务器上实现。API server cursors are implemented on the server. 每次客户端应用程序调用 API 游标函数时, SQL ServerSQL Server Native Client OLE DB 访问接口或 ODBC 驱动程序会把请求传输到服务器,以便对 API 服务器游标进行操作。Each time a client application calls an API cursor function, the SQL ServerSQL Server Native Client OLE DB provider or ODBC driver transmits the request to the server for action against the API server cursor.

客户端游标Client cursors

客户端游标由 SQL ServerSQL Server Native Client ODBC 驱动程序和实现 ADO API 的 DLL 在内部实现。Client cursors are implemented internally by the SQL ServerSQL Server Native Client ODBC driver and by the DLL that implements the ADO API. 客户端游标通过在客户端高速缓存所有结果集行来实现。Client cursors are implemented by caching all the result set rows on the client. 每次客户端应用程序调用 API 游标函数时, SQL ServerSQL Server Native Client ODBC 驱动程序或 ADO DLL 会对客户端上高速缓存的结果集行执行游标操作。Each time a client application calls an API cursor function, the SQL ServerSQL Server Native Client ODBC driver or the ADO DLL performs the cursor operation on the result set rows cached on the client.

游标类型Type of Cursors

SQL ServerSQL Server 支持四种游标类型。supports four cursor types.

备注

游标可以利用 tempdb 工作表。Cursors may leverage tempdb worktables. 就像溢出的聚合或排序操作一样,这些操作在 I/O 中产生,是潜在的性能瓶颈。Just like aggregation or sort operations that spill, these incur in I/O, and are a potential performance bottleneck. STATIC 游标从一开始就使用工作表。STATIC cursors use worktables from its inception. 有关详细信息,请参阅查询处理体系结构指南中的工作表For more information, see worktables in the Query Processing Architecture Guide.

只进Forward-only

只进游标指定为 FORWARD_ONLYREAD_ONLY,不支持滚动。A forward-only cursor is specified as FORWARD_ONLY and READ_ONLY and does not support scrolling. 这些游标也称为“firehose”游标,并且只支持从游标的开始到结束连续提取行 。These are also called firehose cursors and support only fetching the rows serially from the start to the end of the cursor. 行只在从数据库中提取出来后才能检索。The rows are not retrieved from the database until they are fetched. 对所有由当前用户发出或由其他用户提交、并影响结果集中的行的 INSERTUPDATEDELETE 语句,其效果在这些行从游标中提取时是可见的。The effects of 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 from the cursor.

由于游标无法向后滚动,则在提取行后对数据库中的行进行的大多数更改通过游标均不可见。Because the cursor cannot be scrolled backward, most changes made to rows in the database after the row was fetched are not visible through the cursor. 当值用于确定所修改的结果集(例如更新聚集索引涵盖的列)中行的位置时,修改后的值通过游标可见。In cases where a value used to determine the location of the row within the result set is modified, such as updating a column covered by a clustered index, the modified value is visible through the cursor.

尽管数据库 API 游标模型将只进游标视为一种游标类型,但是 SQL ServerSQL Server 不这样。Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL ServerSQL Server does not. SQL ServerSQL Server 将只进和滚动视为可应用于静态游标、键集驱动游标和动态游标的选项。considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors. Transact-SQLTransact-SQL 游标支持只进静态游标、键集驱动游标和动态游标。cursors support forward-only static, keyset-driven, and dynamic cursors. 数据库 API 游标模型则假定静态游标、键集驱动游标和动态游标都是可滚动的。The database API cursor models assume that static, keyset-driven, and dynamic cursors are always scrollable. 当数据库 API 游标属性设置为只进时, SQL ServerSQL Server 将此游标作为只进动态游标实现。When a database API cursor attribute or property is set to forward-only, SQL ServerSQL Server implements this as a forward-only dynamic cursor.

静态Static

静态游标的完整结果集是打开游标时在 tempdb 中生成的。The complete result set of a static cursor is built in tempdb when the cursor is opened. 静态游标总是按照打开游标时的原样显示结果集。A static cursor always displays the result set as it was when the cursor was opened. 静态游标在滚动期间很少或根本检测不到变化,但消耗的资源相对很少。Static cursors detect few or no changes, but consume relatively few resources while scrolling.

游标不反映在数据库中所做的任何影响结果集成员身份的更改,也不反映对组成结果集的行的列值所做的更改。The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set. 静态游标不会显示打开游标以后在数据库中新插入的行,即使这些行符合游标 SELECT 语句的搜索条件。A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement. 如果组成结果集的行被其他用户更新,则新的数据值不会显示在静态游标中。If rows making up the result set are updated by other users, the new data values are not displayed in the static cursor. 静态游标会显示打开游标以后从数据库中删除的行。The static cursor displays rows deleted from the database after the cursor was opened. 静态游标中不反映 UPDATEINSERT 或者 DELETE 操作(除非关闭游标然后重新打开),甚至不反映使用打开游标的同一连接所做的修改。No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made using the same connection that opened the cursor.

备注

SQL ServerSQL Server 静态游标始终是只读的。static cursors are always read-only.

备注

由于静态游标的结果集存储在“tempdb”中的一个工作表中,因此结果集中的行大小不能超过 SQL ServerSQL Server 表的最大行大小 。Because the result set of a static cursor is stored in a worktable in tempdb, the size of the rows in the result set cannot exceed the maximum row size for a SQL ServerSQL Server table.
有关详细信息,请参阅查询处理体系结构指南中的工作表For more information, see worktables in the Query Processing Architecture Guide. 有关最大行大小的详细信息,请参阅 SQL Server 最大容量规范For more information on max row size, see Maximum Capacity Specifications for SQL Server.

Transact-SQLTransact-SQL 称静态游标为不敏感游标。uses the term insensitive for static cursors. 一些数据库 API 将这类游标识别为快照游标。Some database APIs identify them as snapshot cursors.

KeysetKeyset

打开由键集驱动的游标时,该游标中各行的成员身份和顺序是固定的。The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. 由键集驱动的游标由一组唯一标识符(键)控制,这组键称为键集。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. 键集是打开游标时来自符合 SELECT 语句要求的所有行中的一组键值。The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. 由键集驱动的游标对应的键集是打开该游标时在 tempdb 中生成的。The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.

DynamicDynamic

动态游标与静态游标相对。Dynamic cursors are the opposite of static cursors. 当滚动游标时,动态游标反映结果集中所做的所有更改。Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. 结果集中的行数据值、顺序和成员在每次提取时都会改变。The data values, order, and membership of the rows in the result set can change on each fetch. 所有用户做的全部 UPDATEINSERTDELETE 语句均通过游标可见。All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. 如果使用 API 函数(如“SQLSetPos”)或 Transact-SQLTransact-SQL WHERE CURRENT OF 子句通过游标进行更新,它们将立即可见 。Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQLTransact-SQL WHERE CURRENT OF clause. 在游标外部所做的更新直到提交时才可见,除非将游标的事务隔离级别设为未提交读。Updates made outside the cursor are not visible until they are committed, unless the cursor transaction isolation level is set to read uncommitted. 有关隔离级别的详细信息,请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)For more information on isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

备注

动态游标计划从不使用空间索引。Dynamic cursor plans never use spatial indexes.

请求游标Requesting a Cursor

SQL ServerSQL Server 支持两种请求游标的方法:supports two methods for requesting a cursor:

  • Transact-SQLTransact-SQL

    Transact-SQLTransact-SQL 语言支持在 ISO 游标语法之后制定的用于使用游标的语法。The Transact-SQLTransact-SQL language supports a syntax for using cursors modeled after the ISO cursor syntax.

  • 数据库应用程序编程接口(API)游标函数Database application programming interface (API) cursor functions

    SQL ServerSQL Server 支持以下数据库 API 的游标功能:supports the cursor functionality of these database APIs:

    • ADO(MicrosoftMicrosoft ActiveX 数据对象)ADO (MicrosoftMicrosoft ActiveX Data Object)

    • OLE DBOLE DB

    • ODBC(开放式数据库连接)ODBC (Open Database Connectivity)

应用程序不能混合使用这两种请求游标的方法。An application should never mix these two methods of requesting a cursor. 已经使用 API 指定游标行为的应用程序不能再执行 Transact-SQLTransact-SQL DECLARE CURSOR 语句请求一个 Transact-SQLTransact-SQL 游标。An application that has used the API to specify cursor behaviors should not then execute a Transact-SQLTransact-SQL DECLARE CURSOR statement to also request a Transact-SQLTransact-SQL cursor. 应用程序只有在将所有的 API 游标特性设置回默认值后,才可以执行 DECLARE CURSOR。An application should only execute DECLARE CURSOR if it has set all the API cursor attributes back to their defaults.

如果既未请求 Transact-SQLTransact-SQL 游标也未请求 API 游标,则默认情况下 SQL ServerSQL Server 将向应用程序返回一个完整的结果集,这个结果集称为默认结果集。If neither a Transact-SQLTransact-SQL nor API cursor has been requested, SQL ServerSQL Server defaults to returning a complete result set, known as a default result set, to the application.

游标进程Cursor Process

Transact-SQLTransact-SQL 游标和 API 游标有不同的语法,但下列一般进程适用于所有 SQL ServerSQL Server 游标:cursors and API cursors have different syntax, but the following general process is used with all SQL ServerSQL Server cursors:

  1. 将游标与 Transact-SQLTransact-SQL 语句的结果集相关联,并且定义该游标的特性,例如是否能够更新游标中的行。Associate a cursor with the result set of a Transact-SQLTransact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.

  2. 执行 Transact-SQLTransact-SQL 语句以填充游标。Execute the Transact-SQLTransact-SQL statement to populate the cursor.

  3. 从游标中检索您想要查看的行。Retrieve the rows in the cursor you want to see. 从游标中检索一行或一部分行的操作称为提取。The operation to retrieve one row or one block of rows from a cursor is called a fetch. 执行一系列提取操作以便向前或向后检索行的操作称为滚动。Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.

  4. 根据需要,对游标中当前位置的行执行修改操作(更新或删除)。Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.

  5. 关闭游标。Close the cursor.

游标行为 Cursor Behaviors
如何实现游标How Cursors Are Implemented

另请参阅See Also

DECLARE CURSOR (Transact-SQL) DECLARE CURSOR (Transact-SQL)
游标 (Transact-SQL) Cursors (Transact-SQL)
游标函数 (Transact-SQL) Cursor Functions (Transact-SQL)
游标存储过程 (Transact-SQL) Cursor Stored Procedures (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)SET TRANSACTION ISOLATION LEVEL (Transact-SQL)