mysql sp cursoropen_sp_cursoropen (Transact-sql) - SQL Server | Microsoft Docs

sp_cursoropen (Transact-SQL)sp_cursoropen (Transact-SQL)

03/14/2017

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

打开游标。Opens a cursor. sp_cursoropen 定义与游标和游标选项相关联的 SQL 语句，然后填充游标。sp_cursoropen defines the SQL statement associated with the cursor and cursor options, and then populates the cursor. sp_cursoropen 等效于 Transact-SQLTransact-SQL DECLARE_CURSOR 和 OPEN 语句的组合。sp_cursoropen is equivalent to the combination of the Transact-SQLTransact-SQL statements DECLARE_CURSOR and OPEN. 此过程通过在表格格式数据流 (TDS) 数据包中指定 ID = 2 来调用。This procedure is invoked by specifying ID =2 in a tabular data stream (TDS) packet.

语法Syntax

sp_cursoropen cursor OUTPUT, stmt

[, scrollopt[ OUTPUT ] [ , ccopt[ OUTPUT ]

[ ,rowcount OUTPUT [ ,boundparam][,...n]]] ]]

参数Arguments

cursorcursor

SQL Server 生成的游标标识符。A SQL Server-generated cursor identifier. cursor 是一个 句柄 值，必须对涉及游标的所有后续过程(如 sp_cursorfetch)提供此值。cursor is a handle value that must be supplied on all subsequent procedures involving the cursor, such as sp_cursorfetch. cursor 是带有 int 返回值的必需参数。cursor is a required parameter with an int return value.

游标 允许单个数据库连接上的多个游标处于活动状态。cursor allows multiple cursors to be active on a single database connection.

stmtstmt

定义游标结果集的必需参数。Is a required parameter that defines the cursor result set. 任何有效的查询字符串 (语法和绑定) 任何字符串类型 (无论 Unicode、大小等，) 都可以充当有效的 stmt 值类型。Any valid query string (syntax and binding) of any string type (regardless of Unicode, size, etc.) can serve as a valid stmt value type.

scrolloptscrollopt

滚动选项。Scroll option. scrollopt 是一个可选参数，它需要以下 整数 输入值之一。scrollopt is an optional parameter that requires one of the following int input values.

值Value

说明Description

0x00010x0001

KEYSETKEYSET

0x00020x0002

DYNAMICDYNAMIC

0x00040x0004

FORWARD_ONLYFORWARD_ONLY

0x00080x0008

STATICSTATIC

0x100x10

FAST_FORWARDFAST_FORWARD

0x10000x1000

PARAMETERIZED_STMTPARAMETERIZED_STMT

0x20000x2000

AUTO_FETCHAUTO_FETCH

0x40000x4000

AUTO_CLOSEAUTO_CLOSE

0x80000x8000

CHECK_ACCEPTED_TYPESCHECK_ACCEPTED_TYPES

0x100000x10000

KEYSET_ACCEPTABLEKEYSET_ACCEPTABLE

0x200000x20000

DYNAMIC_ACCEPTABLEDYNAMIC_ACCEPTABLE

0x400000x40000

FORWARD_ONLY_ACCEPTABLEFORWARD_ONLY_ACCEPTABLE

0x800000x80000

STATIC_ACCEPTABLESTATIC_ACCEPTABLE

0x1000000x100000

FAST_FORWARD_ACCEPTABLEFAST_FORWARD_ACCEPTABLE

因为请求的值可能不适合于 stmt 定义的游标，所以，此参数可同时用作输入和输出。Because of the possibility that the requested value is not appropriate for the cursor defined by stmt, this parameter serves as both input and output. 在此类情况下，SQL Server 分配一个适当的值。In such cases, SQL Server assigns an appropriate value.

ccoptccopt

并发控制选项。Concurrency control option. ccopt 是一个可选参数，它需要以下 整数 输入值之一。ccopt is an optional parameter that requires one of the following int input values.

值Value

说明Description

0x00010x0001

READ_ONLYREAD_ONLY

0x00020x0002

SCROLL_LOCKS(以前称为 LOCKCC)SCROLL_LOCKS (previously known as LOCKCC)

0x00040x0004

乐观 (以前称为 OPTCC)OPTIMISTIC (previously known as OPTCC)

0x00080x0008

OPTIMISTIC(以前称为 OPTCCVAL)OPTIMISTIC (previously known as OPTCCVAL)

0x20000x2000

ALLOW_DIRECTALLOW_DIRECT

0x40000x4000

UPDT_IN_PLACEUPDT_IN_PLACE

0x80000x8000

CHECK_ACCEPTED_OPTSCHECK_ACCEPTED_OPTS

0x100000x10000

READ_ONLY_ACCEPTABLEREAD_ONLY_ACCEPTABLE

0x200000x20000

SCROLL_LOCKS_ACCEPTABLESCROLL_LOCKS_ACCEPTABLE

0x400000x40000

OPTIMISTIC_ACCEPTABLEOPTIMISTIC_ACCEPTABLE

0x800000x80000

OPTIMISITC_ACCEPTABLEOPTIMISITC_ACCEPTABLE

与 scrollopt 一样， SQL ServerSQL Server 可以覆盖请求的 ccopt 值。As with scrollopt, SQL ServerSQL Server can override the requested ccopt values.

数rowcount

要用于 AUTO_FETCH 的提取缓冲区行数。The number of fetch buffer rows to use with AUTO_FETCH. 默认值为 20 行。The default is 20 rows. 指定为输入值与返回值时，行计数 的行为不同。rowcount behaves differently when assigned as an input value versus a return value.

作为输入值As input value

作为返回值As return value

当指定的 AUTO_FETCH scrollopt 值为 rowcount 时，表示要放入提取缓冲区中的行数。When the AUTO_FETCH scrollopt value is specified rowcount represents the number of rows to place into the fetch buffer.

注意：如果指定 AUTO_FETCH，则 >0 是有效的值，否则将被忽略。Note: >0 is a valid value when AUTO_FETCH is specified, but is otherwise ignored.

表示结果集中的行数，除非指定了 scrollopt AUTO_FETCH 值。Represents the number of rows in the result set, except when the scrollopt AUTO_FETCH value is specified.

-

boundparamboundparam

指示使用其他参数。Signifies the use of additional parameters. boundparam 是一个可选参数，如果 scrollopt PARAMETERIZED_STMT 值设置为 ON，则应指定此参数。boundparam is an optional parameter that should be specified if the scrollopt PARAMETERIZED_STMT value is set to ON.

返回代码值Return Code Values

如果未引发错误，则 sp_cursoropen 返回以下值之一。If no error is raised, sp_cursoropen returns one of the following values.

00

已成功执行该过程。The procedure executed successfully.

0x00010x0001

在执行过程中发生了错误(次要错误，严重程度不足以在操作中引发错误)。An error occurred during the execution (a minor error, not severe enough to raise an error in the operation).

0x00020x0002

正在执行异步操作。An asynchronous operation is in progress.

0x00020x0002

正在执行 FETCH 操作。A FETCH operation is in process.

AA

此游标已由 SQL ServerSQL Server 取消分配而不可用。This cursor has been deallocated by SQL ServerSQL Server and is unavailable.

当引发错误时，返回值可能不一致，无法确保准确性。When an error is raised, the return values may be inconsistent and the accuracy cannot be guaranteed.

当 rowcount 参数指定为返回值时，将出现以下结果集。When the rowcount parameter is specified as a return value, the following result set occurs.

-1-1

如果行数未知或不适用，则返回此值。Returned if the number of rows is unknown or not applicable.

-n-n

当异步填充生效时，返回此值。Returned when an asynchronous population is in effect. 表示在指定 scrollopt AUTO_FETCH 值时放置在提取缓冲区中的行数。Represents the number of rows that were placed into the fetch buffer when the scrollopt AUTO_FETCH value is specified.

如果使用 RPC，则返回值如下所示。If RPC is in use, the return values are as follows.

00

过程成功。Procedure is successful.

11

过程失败。Procedure failed.

22

正在异步生成键集游标。A keyset cursor is being asynchronously generated.

1616

快进游标已自动关闭。A fast-forward cursor has been automatically closed.

备注

如果 sp_cursoropen 过程成功执行，则会发送 RPC 返回参数和具有 TDS 列格式信息的结果集 (0xa0 & 0xa1 消息) 。If the sp_cursoropen procedure executes successfully, the RPC return parameters and a result set with TDS column format information (0xa0 & 0xa1 messages) are sent. 如果不成功，则发送一条或多条 TDS 错误消息。If unsuccessful, one or more TDS error messages are sent. 在任一情况下，都不会返回行数据，已 完成 的消息计数将为零。In either case, no row data will be returned and the done message count will be zero. 如果您使用的 SQL ServerSQL Server 版本低于 7.0，将返回 0xa0、0xa1(SELECT 语句的标准)以及 0xa5 和 0xa4 标记流。If you are using a version of SQL ServerSQL Server earlier than 7.0, 0xa0, 0xa1 (standard for SELECT statements) are returned along with 0xa5 and 0xa4 token streams. 如果您使用 SQL ServerSQL Server 7.0，将返回 0x81(SELECT 语句的标准)以及 0xa5 和 0xa4 标记流。If you are using SQL ServerSQL Server 7.0, 0x81 is returned (standard for SELECT statements) along with the 0xa5 and 0xa4 token streams.

备注Remarks

stmt 参数stmt Parameter

如果 stmt 指定了存储过程的执行，则输入参数可能会定义为 常量字符串的 一部分，或指定为 boundparam 参数。If stmt specifies the execution of a stored procedure, the input parameters may either be defined as constants as part of the stmt string, or specified as boundparam arguments. 通过此方法，可以将声明的变量作为绑定参数传递。Declared variables can be passed as bound parameters in this way.

Stmt 参数允许的内容取决于 ccopt ALLOW_DIRECT 返回值是由链接还是链接到 ccopt 值的其余部分，例如：The allowed contents of the stmt parameter depend upon whether or not the ccopt ALLOW_DIRECT return value has been linked by OR to the rest of the ccopt values, i.e.:

如果未指定 ALLOW_DIRECT，则 Transact-SQLTransact-SQL 必须使用为包含单个 SELECT 语句的存储过程调用的 SELECT 语句或执行语句。If ALLOW_DIRECT is not specified, either a Transact-SQLTransact-SQL SELECT or EXECUTE statement calling for a stored procedure containing a single SELECT statement must be used. 此外，SELECT 语句必须限定为一个游标；也即，它不能包含关键字 SELECT INTO 或 FOR BROWSE。Furthermore, the SELECT statement must qualify as a cursor; that is, it cannot contain the keywords SELECT INTO or FOR BROWSE.

如果指定了 ALLOW_DIRECT，则这可能导致一个或多个 Transact-SQLTransact-SQL 语句，包括那些依次执行其他存储过程以及多个语句的语句。If ALLOW_DIRECT is specified, this may result in one or more Transact-SQLTransact-SQL statements, including those that, in turn, execute other stored procedures with multiple statements. 将只执行非 SELECT 语句或包含关键字 SELECT INTO 或 FOR BROWSE 的任何 SELECT 语句，而不会导致创建游标。Non-SELECT statements or any SELECT statement that contains the keywords SELECT INTO or FOR BROWSE will simply be executed and will not result in the creation of a cursor. 这同样适用于在一批多个语句中包含的任何 SELECT 语句。The same is true for any SELECT statement included in a batch of multiple statements. 在 SELECT 语句包含只与游标相关的子句的情况下，将忽略这些子句。In cases where a SELECT statement contains clauses that pertain only to cursors, those clauses are ignored. 例如，当 ccopt 的值为0x2002 时，这是对的请求：For instance, when the value of ccopt is 0x2002, this is a request for:

具有滚动锁的游标(如果只有一个 SELECT 语句限定为游标)，或者A cursor with scroll locks, if there is only a single SELECT statement that qualifies as a cursor, or

一个直接的语句执行(如果有多个语句、一个非 SELECT 语句或不限定为游标的 SELECT 语句)。A direct statement execution if there are multiple statements, a single non-SELECT statement, or a SELECT statement that does not qualify as a cursor.

scrollopt 参数scrollopt Parameter

前五个 scrollopt 值 (KEYSEY、DYNAMIC、FORWARD_ONLY、STATIC 和 FAST_FORWARD) 互相排斥。The first five scrollopt values (KEYSEY, DYNAMIC, FORWARD_ONLY, STATIC, and FAST_FORWARD) are mutually exclusive.

PARAMETERIZED_STMT 和 CHECK_ACCEPTED_TYPES 可以由 OR 链接到前五个值中的任何一个。PARAMETERIZED_STMT and CHECK_ACCEPTED_TYPES can be linked by OR to any of the first five values.

AUTO_FETCH 和 AUTO_CLOSE 可以由 OR 链接到 FAST_FORWARD。AUTO_FETCH and AUTO_CLOSE can be linked by OR to FAST_FORWARD.

如果 CHECK_ACCEPTED_TYPES 为 ON，则 (KEYSET_ACCEPTABLE DYNAMIC_ACCEPTABLE、FORWARD_ONLY_ACCEPTABLE、STATIC_ACCEPTABLE 或 FAST_FORWARD_ACCEPTABLE 的最后五个 scrollopt 值中至少有一个值 , 也必须为 ON。If CHECK_ACCEPTED_TYPES is ON, at least one of the last five scrollopt values (KEYSET_ACCEPTABLE, DYNAMIC_ACCEPTABLE, FORWARD_ONLY_ACCEPTABLE, STATIC_ACCEPTABLE, or FAST_FORWARD_ACCEPTABLE) must also be ON.

STATIC 游标始终打开为 READ_ONLY。STATIC cursors are always opened as READ_ONLY. 这意味着无法通过此游标更新基础表。This means that the underlying table cannot be updated through this cursor.

ccopt 参数ccopt Parameter

前四个 ccopt 值 (READ_ONLY，SCROLL_LOCKS，并且两个乐观值) 都是互斥的。The first four ccopt values (READ_ONLY, SCROLL_LOCKS, and both OPTIMISTIC values) are mutually exclusive.

备注

选择前四个 ccopt 值中的一个值，指示游标是只读的，还是使用锁定或乐观方法来防止丢失更新。Choosing one of the first four ccopt values dictates whether the cursor is read-only, or if locking or optimistic methods are used to prevent lost updates. 如果未指定 ccopt 值，则默认值为乐观。If a ccopt value is not specified, the default value is OPTIMISTIC.

ALLOW_DIRECT 和 CHECK_ACCEPTED_TYPES 可以由 OR 链接到前四个值中的任何一个。ALLOW_DIRECT and CHECK_ACCEPTED_TYPES can be linked by OR to any of the first four values.

UPDT_IN_PLACE 可以由 OR 链接到 READ_ONLY、SCROLL_LOCKS 或任一 OPTIMISTIC 值。UPDT_IN_PLACE can be linked by OR to READ_ONLY, SCROLL_LOCKS, or either of the OPTIMISTIC values.

如果 CHECK_ACCEPTED_TYPES 为 ON，则 (READ_ONLY_ACCEPTABLE，SCROLL_LOCKS_ACCEPTABLE，最后四个 ccopt 值中至少有一个，，并且 OPTIMISTIC_ACCEPTABLE 值都必须为 ON。If CHECK_ACCEPTED_TYPES is ON, at least one of the last four ccopt values (READ_ONLY_ACCEPTABLE, SCROLL_LOCKS_ACCEPTABLE, and either of the OPTIMISTIC_ACCEPTABLE values) must also be ON.

定位更新和删除函数只能在提取缓冲区内执行，并且仅当 ccopt 值等于 SCROLL_LOCKS 或乐观时才可执行。Positioned UPDATE and DELETE functions may be performed only within the fetch buffer and only if the ccopt value equals SCROLL_LOCKS or OPTIMISTIC. 如果 SCROLL_LOCKS 是指定的值，则此操作可确保成功。If SCROLL_LOCKS is the specified value, the operation is guaranteed to succeed. 如果 OPTIMISTIC 是指定的值，则当自上次提取该行后行已发生变化时，操作将失败。If OPTIMISTIC is the specified value, the operation will fail if the row has changed since it was last fetched.

失败原因在于：当 OPTIMISTIC 为指定的值时，将通过比较时间戳或校验和值执行乐观并发控制函数(由 SQL ServerSQL Server 确定)。The reason for this failure is that when OPTIMISTIC is the specified value, an optimistic currency control function is performed by comparing timestamps or checksum values, as determined by SQL ServerSQL Server. 如果任何行不匹配，则操作失败。If any of these rows do not match, the operation will fail.

将 UPDT_IN_PLACE 指定为返回值可控制以下结果：Specifying UPDT_IN_PLACE as the return value governs the following results:

如果未设置，则当对某个表使用唯一索引执行定位更新时，游标将从其工作表中删除该行，并将其插入到游标使用的任何键列的末尾，从而更改这些列。If not set when performing a positioned update on a table with a unique index, the cursor deletes the row from its work table and inserts it at the end of any of the key columns used by the cursor, thereby changing those columns.

如果设置为 ON，游标将只更新工作表的原始行中的键列。If set ON, the cursor will simply update the key columns in the work table's original row.

bound_param 参数bound_param Parameter

根据代码中的错误消息指定 PARAMETERIZED_STMT 时，参数名称应为 paramdef 。The parameter name should be paramdef when PARAMETERIZED_STMT is specified, according to the error message in the code. 当未指定 PARAMETERIZED_STMT 时，在错误消息将不指定任何名称。When PARAMETERIZED_STMT is not specified, no name is specified in the error message.

RPC 注意事项RPC Considerations

RPC RETURN_METADATA 输入标志可设置为 0x0001，以请求在 TDS 流中返回游标选择列表元数据。The RPC RETURN_METADATA input flag can be set to 0x0001 to request that cursor select list metadata be returned in the TDS stream.

示例Examples

bound_param 参数bound_param Parameter

第五个参数之后的任何参数都将作为输入参数传递到语句计划。Any parameters after the fifth are passed along to the statement plan as input parameters. 第一个此类参数必须为以下格式的字符串：The first such parameter must be a string in the form of:

{局部变量名称数据类型}[,...北{ local variable name data type } [,...n]

后续参数用于传递要替换为语句中的 局部变量名称 的值。Subsequent parameters are used to pass the values to be substituted for the local variable name in the statement.

另请参阅See Also