The role of a cursor

Operations in relational databases affect the entire row set. The row set returned by the SELECT statement includes all rows that meet the conditions in the WHERE clause of the statement. The complete row set returned by the statement is called the result set. Applications, especially interactive online applications, do not always take the entire result set as a unit for effective processing. These applications require a mechanism to process one or more rows at a time. A cursor is a result set extension that provides this mechanism.

The cursor expands the result processing in the following ways:

Allows you to locate specific rows in the result set.


Retrieves one or more rows from the current position of the result set.


You can modify the data of the row at the current position in the result set.


Other users provide different levels of visibility support for changes made to the database data displayed in the result set.


Provides Transact-SQL statements for the data in the access result set used in scripts, stored procedures, and triggers.
Request cursor
Microsoft & reg; SQL Server & #8482; 2000 supports two request cursors:

Transact-SQL
For a cursor Based on the SQL-92 cursor syntax, the transact-SQL language supports using their syntax.

Database Application Programming Interface (API) cursor Function
SQL Server supports the cursor functions of these database APIs:

ADO (Microsoft ActiveX & reg; Data Object)


OLE DB


ODBC (Open Database Connection)


DB-Library
Applications cannot use the two request cursors together. Applications that have used APIs to specify the cursor behavior cannot execute the transact-SQL declare cursor statement to request a Transact-SQL cursor. The declare cursor can be executed only when all API cursor features are set back to the default value.

If neither the transact-SQL statement nor the API cursor is requested, SQL Server Returns a complete result set to the application by default. This result set is called the default result set.

Cursor Process
Transact-SQL cursors and API cursors have different syntaxes, but the following general processes can be used for all SQL Server cursors:

Associate the cursor with the result set of the transact-SQL statement and define the features of the cursor, such as whether rows in the cursor can be updated.


Execute the transact-SQL statement to fill in the cursor.


Retrieves rows from the cursor you want to view. An operation that retrieves one or more rows from a cursor is called extraction. Operations that perform a series of extract operations to search rows forward or backward are called scrolling.


Modify (update or delete) the row at the current position in the cursor as needed ).


Close the cursor.

In short, I am familiar with pointers in C.
You can freely move to the desired row.

Example:
DeclareCursor_nameCursor
[Local | Global]
[Forward_only | scroll]
[Static | keyset | dynamic | fast_forward]
[Read_only | scroll_locks | optimistic]
[Type_warning]
ForSelect_statement
[For update [Column_name[,...N]

The result set generated when the cursor is opened includesPubsDatabaseAuthorsAll rows and columns in the table. You can update the cursor. All updates and deletions made to the cursor are displayed in the extraction process. Because the scroll option is not specified, fetch next is the only available extraction option.
Declare authors_cursor cursor
For select * from authors
Open authors_cursor
Fetch next from authors_cursor

Relay cursor
Declare cursor cursor_name
Select * From table_name (for read only)
Fetch cursor_name to variable_name
;
;
;
Note: Fetch retrieves a specific row from the transact-SQL Server cursor.

# Database Technology