Punyajanam when performed

DECLARE CURSOR (Transact-SQL)

  • 13 minutes to read

Scope of application:SQL Server (all supported versions) Azure SQL database

Defines the attributes of a Transact-SQL server cursor, such as: B. Its scrolling behavior, as well as the query that is used to create the result set on which the cursor is executed. supports both syntax based on the ISO standard and syntax that uses a subset of the Transact-SQL extensions.

Transact-SQL syntax conventions

syntax

Arguments

cursor_name
The name of the Transact-SQL server defined cursor. cursor_name must conform to the rules for identifiers.

INSENSITIVE
Defines a cursor that creates a temporary copy of the data it intends to use. All requests to the cursor are handled by this temporary table in tempdb answered; Changes to the base tables will not be reflected in the data retrieved from this cursor. In addition, this cursor does not allow any changes. When using ISO syntax without, any deletions and updates made to the underlying tables (by any user) will be reflected in subsequent fetches.

SCROLL
Indicates that all retrieval options (,,,,,) are available. If the type is not specified in an ISO statement, only the retrieve option is supported. cannot be specified if is also specified. If not specified, only the retrieve option is available and the cursor becomes a cursor.

select_statement
A standard statement that defines the cursor's result set. The keywords and are in select_statement a cursor declaration is not permitted.

SQL Server implicitly converts the cursor to another type when the clauses in select_statement conflict with the functionality of the requested cursor type.

READ ONLY
Prevents updates from being made through this cursor. The cursor cannot be referenced in a or statement in a clause. This option overrides the default setting by which a cursor can be updated.

UPDATE [OF column_name [,...n]]
Defines updatable columns within the cursor. If OF [, <... n>] is specified, changes can only be made in the columns listed. If no column list is specified, all columns can be updated.

cursor_name
The name of the Transact-SQL server defined cursor. cursor_name must conform to the rules for identifiers.

LOCAL
Indicates that the cursor's scope is local to the batch, stored procedure, or trigger in which it was created. The cursor name is only valid within this range. The cursor can be referenced by local cursor variables in the batch, stored procedure, trigger, or parameter of a stored procedure. A parameter can return the local cursor to the calling batch, calling stored procedure, or calling trigger. These can assign the parameter to a cursor variable to refer to the cursor after exiting the stored procedure. The cursor is implicitly deallocated when the batch, stored procedure, or trigger exits, unless the cursor was returned in a parameter. If returned in a parameter, the cursor is deallocated when the last variable referring to it is deallocated or when the cursor goes out of scope.

GLOBAL
Indicates that the area of ​​the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch run by the connection. The cursor is only implicitly deallocated when the connection is disconnected.

Note

If neither is specified, the default is determined by the setting of the database option default to local cursor controlled.

FORWARD_ONLY
Specifies that the cursor can only be moved forward from the first line to the last. is the only supported retrieval option. Any INSERT, UPDATE, and DELETE statements executed by the current user (or committed by other users) that affect rows in the result set are visible when the rows are retrieved. Since the cursor cannot be used to scroll back, changes made to rows in the database after the row was retrieved are not visible through the cursor. By default, forward cursors are dynamic; H. that all changes are detected while the current row is being processed. This allows the cursor to start faster and updates to the underlying tables can be seen in the result set. Although forward cursors do not support scrolling, applications can return to the beginning of the result set by exiting and restarting the cursor. If, or is specified without either of the keywords, the cursor is a dynamic cursor. If neither nor is specified, it defaults to unless the keywords, or are specified. By default, the cursors,, and are set to. Unlike database APIs such as ODBC and ADO, Transact-SQL cursors,, and are supported.

STATIC
Causes the cursor to always display the result set as it was when the cursor was started and makes a temporary copy of the data used by the cursor. All requests to the cursor are handled by this temporary table in the tempdb answered. Because of this, INSERT, UPDATE, and DELETE statements on base tables are not reflected in the data returned by fetches to this cursor, and the cursor does not recognize changes to the membership, order, or values ​​of the result set after it opened. Static cursors can recognize their own UPDATE, DELETE, and INSERT statements, although this is not required. For example, suppose a static cursor retrieves a row, and then another application updates that row. When the application retrieves the row from the static cursor again, the recognized values ​​are unchanged even though the other application made changes. All types of scrolling are supported.

KEYSET
Indicates that the cursor has a fixed membership and order of rows when the cursor is opened. The set of keys that uniquely identify the rows is stored in a table in tempdb created as keyset referred to as. With its ability to detect changes, this cursor offers functionality between static and dynamic cursors. Like a static cursor, it does not always detect changes to the membership and order of the result set. Like a dynamic cursor, it detects changes to the values ​​of the rows in the result set. Keyset-controlled cursors are controlled by a series of unique identifiers (keys) called the keyset. The keys are created using a series of columns that uniquely identify the rows in the result set. The keyset consists of key values ​​from all of the rows returned by the query statement. A keyset-driven cursor creates and stores a key for each line in the cursor, which in turn is stored on either the client workstation or the server. When you access any row, the stored key is used to retrieve the current data values ​​from the data source. In a keyset-controlled cursor, the membership of the result set is fixed when the keyset has been completely filled. Therefore, additions and updates that affect membership are not part of the result set until it is reopened. Changes to data values ​​(either by the owner of the keyset or other processes) that are visible as the user scrolls through the result set:

  • If a row is deleted, the row is retrieved with a value of -2 because the deleted row appears as a gap in the result set. The key for the row is in the keyset, but the row is no longer in the result set.
  • INSERTs outside the cursor (by other processes) are only visible when the cursor is exited and restarted. INSERTs within the cursor are displayed at the end of the result set.
  • Updating key values ​​from outside the cursor is like deleting the old row followed by inserting the new row. The row with the new values ​​is not visible, and attempts to get the row with the old values ​​will return a value of -2. The new values ​​are visible when the update is performed using the cursor by specifying the clause.

Note

If the query references one or more tables without a unique index, the keyset cursor is converted to a static cursor.

DYNAMIC
Defines a cursor that reflects any data changes to the rows in the result set as you scroll the cursor and retrieve a new record, regardless of whether the changes are made inside or outside (by other users) the cursor. Therefore, all INSERT, UPDATE and DELETE statements are visible to all users via the cursor. Data values, order, and membership of the rows can change with each retrieval. The retrieve option is not supported for dynamic cursors. Updates that run outside of the cursor will only be visible if they have been committed (unless the cursor's transaction isolation level is set to). For example, suppose a dynamic cursor retrieves two lines and another application updates one of those lines and deletes the other. Then, when the dynamic cursor retrieves these rows, it will not find the deleted row, but it will display the new values ​​of the updated row.

FAST_FORWARD
Indicates a, cursor with performance optimizations enabled. cannot be specified if or is also specified. This type of cursor does not allow changes to be made to the data within the cursor.

Note

and cannot be used in the same statement.

READ_ONLY
Prevents updates from being made through this cursor. The cursor cannot be referenced in a or statement in a clause. This option overrides the default setting by which a cursor can be updated.

SCROLL_LOCKS
Indicates that positioned updates or deletions by the cursor are guaranteed to succeed. SQL Server locks the rows while they are being read into the cursor to ensure that they are available for later changes. cannot be specified if or is also specified.

OPTIMISTIC
Indicates that positioned updates or deletions by the cursor are unsuccessful if the row has been updated since it was last read into the cursor. SQL Server does not lock rows while they are being read into the cursor. Instead, by comparing timestamp-Column values ​​or a checksum value if the table doesn't have any timestampColumn, determines whether the row was changed after it was read into the cursor. If the line was changed, the attempted positioned update or delete operation will fail. cannot be specified if is also specified.

TYPE_WARNING
Specifies that a warning will be sent to the client if the cursor is implicitly converted from the requested type to another type.

select_statement
A standard SELECT statement that defines the cursor's result set. The keywords,, and are in select_statement a cursor declaration is not permitted.

Note

You can use a query hint in a cursor declaration. However, if you also use the clause, specify after.

SQL Server implicitly converts the cursor to another type when the 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]]
Defines updatable columns within the cursor. If specified, changes can only be made in the columns listed. If specified without a column list, all columns can be updated unless the parallelism option is specified.

Remarks

defines the attributes of a Transact-SQL server cursor, such as: B. Its scrolling behavior, as well as the query that is used to create the result set on which the cursor is executed. The statement populates the result set and returns a row from the result set. The statement releases the current result set associated with the cursor. The statement releases the resources used by the cursor.

The first form of the statement uses ISO syntax to declare cursor behavior. The second form of the statement uses Transact-SQL extensions that allow cursors to be defined using the same types of cursors used in the ODBC or ADO database API cursor functions.

The two forms cannot be used at the same time. If you specify the keyword or before the keyword, you cannot use keywords between and between the keywords. If you include keywords between the keywords and, you may or may not include them in front of the keyword.

If neither nor is specified in a Transact-SQL syntax statement, the default is as follows:

  • If the statement does not support updates (due to lack of permissions, access to remote tables that do not support updates, etc.), the cursor is.

  • By default, the and cursors are set to.

  • By default, the and cursors are set to.

Reference to cursor names is only possible from other Transact-SQL statements. They cannot be referenced by database API functions. For example, once a cursor has been declared, there is no way to refer to the cursor name in an OLE DB, ODBC, or ADO function or method. The rows of the cursor cannot be obtained using API functions or API methods. The only way to get the rows is with the FETCH statements of Transact-SQL.

After a cursor has been declared, the properties of the cursor can be determined using the following system stored procedures:

System stored proceduresDESCRIPTION
sp_cursor_listReturns a list of the cursors currently visible in the connection and their attributes.
sp_describe_cursorDescribes the attributes of a cursor, e.g. B. whether it is a forward cursor or a scroll cursor.
sp_describe_cursor_columnsDescribes the column attributes in the cursor result set.
sp_describe_cursor_tablesDescribes the base tables accessed by the cursor.

Variables can possibly be used as part of select_statement that declares a cursor. The values ​​of cursor variables do not change after a cursor is declared.

Permissions

In the standard setting, all users have the authorization if they have the authorization for the views, tables and columns used in the cursor.

restrictions

Cursors or triggers cannot be used in a table with a clustered columnstore index. This restriction does not apply to ungrouped columnstore indexes; cursors and triggers can be used on a table with a nonclustered columnstore index.

Examples

A. Using the simple cursor and syntax

The result set generated when this cursor is opened contains all rows and columns in the table. This cursor can be updated. All updates and deletions are shown in getting this cursor. is the only retrieval available because the option has not been specified.

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 provider.

additional Information

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