Two request cursors for Microsoft SQL Server database

The "IT168 Technology" cursor (cursor) is a data buffer that is opened by the system to the user and holds the execution result of the SQL statement. Each cursor area has a name. The user can use SQL statements to retrieve records from the cursor and assign them to the main variable for further processing in the main language.

Operations in a relational database work on the entire rowset. The rowset returned by the SELECT statement includes all rows that meet the criteria in the WHERE clause of the statement. This complete set of rows returned by the statement is called a result set. Applications, especially interactive online applications, do not always effectively process the entire result set as a unit. These applications require a mechanism to process one or more rows at a time. A cursor is an extension of the result set that provides this mechanism.

Cursors extend the result processing in the following ways:

Allows you to locate specific rows in a result set.

Retrieves a row or a subset of rows from the current position of the result set.

Supports data modification of rows at the current position in the result set.

Provides different levels of visibility support for changes made by other users to the database data displayed in the result set.

Provides Transact-SQL statements that are used in scripts, stored procedures, and triggers to access data in the result set.

Request cursor

MicrosoftSQL Server supports two methods of requesting cursors:

Transact-SQL

The Transact-SQL language supports the syntax for using cursors, which is developed after the ISO-banner method.

Database application Programming Interface (API) cursor functions

SQL Server supports cursor functionality for the following database APIs:

ADO (Microsoft ActiveX data Object)

OLE DB

ODBC (Open database connection)

Applications cannot mix the methods of these two request cursors. Applications that have already used the API to specify cursor behavior can no longer execute Transact-SQL DECLARE CURSOR Statements requesting a Transact-SQL cursor. An application can perform DECLARE CURSOR only after all API cursor attributes have been set back to their default values.

If neither the Transact-SQL cursor nor the API cursor is requested, SQL Server returns a complete result set to the application by default, which is called the default result set.

Cursor process

Transact-SQL cursors and API cursors have different syntax, but the following general processes apply to all SQL Server cursors:

1, the cursor is associated with the result set of the Transact-SQL statement, and the attributes of the cursor are defined, such as whether the rows in the cursor can be updated.

2. Execute Transact-SQL statements to populate the cursor.

3, retrieve the rows you want to view from the cursor. The operation of retrieving a row or branch from a cursor is called extraction. Performing a series of fetch operations to retrieve rows forward or backward is called scrolling.

4. Perform a modification (update or deletion) of the row at the current position in the cursor as required.

5, close the cursor.