Use of SQL SERVER cursor Cursors (reprint)

One: Understanding Cursors

Cursor It enables the user to access the result set returned by SQL Server on a row-by-line basis.

One of the main reasons for using cursors is to convert the set operation to a single record processing mode.

When data is retrieved from a database in SQL language, the result is placed in an area of memory, and the result is often a collection of multiple records.

The cursor mechanism allows users to access these records row-by-line within SQL Server, displaying and processing these records at the user's own will.

Two: The basic form of the cursor

To declare a cursor:Form 1
DECLARE cursor_name [insensitive] [SCROLL] Cursor
For select_statement
[For {READ only | UPDATE][of column_list]}]
Form 2
DECLARE cursor_name Cursor
[LOCAL | GLOBAL]
[Forward_only | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[Read_Only | Scroll_locks | Optimistic]
For select_statement
[For {READ only | UPDATE][of column_list]}]
The INSENSITIVE keyword indicates that a temporary copy of the retrieved result set is to be created, and subsequent data is obtained from the temporary copy.
If the data in the original base table changes during subsequent cursor processing, they are not visible to the cursor. This insensitive cursor does not allow for data changes.
The scroll keyword indicates that the cursor can scroll in any direction. All fetch options (first, last, next, relative, absolute) can be used in cursors.
If this option is omitted, the cursor can only scroll forward (next).
Select_statement indicates the result set established by the SQL statement. Transact SQL statements COMPUTE, COMPUTE by, for Browse, and into are not allowed in the cursor declaration's SELECT statement.
READ only indicates that data modifications are not allowed in the cursor result set.
The UPDATE keyword indicates that the result set of the cursor can be modified.
Of column_list indicates the columns in the result set that can be modified. By default (using the UPDATE keyword), all columns can be modified.
The local keyword indicates that the cursor is local and can only be used in the process it declares.
The Global keyword makes the cursor globally visible to the entire connection. Global cursors are available at any time the connection is activated. The cursor is no longer available until the end of the connection.
Forward_only indicates that the cursor can only scroll forward.
The static cursor is the same as the insensitive cursor.
Keyset indicates the order of the selected rows. SQL Server creates a temporary keyword set from the result set. If the non-key columns of the database are modified, they are visible to the cursor.
Because it is a fixed set of keywords, it is not visible to modify the key column or to insert a new column.
Dynamic indicates that the cursor will reflect all modifications to the result set.
Scroll_lock is to ensure the success of the cursor operation, and to modify or delete the lock.
Optimistic indicates which modifications or deletions made through the cursor will not succeed.
Attention:
· If the distinct, UNION, GROUP by statement is used in the SELECT statement and the aggregation expression is included in the selection, the cursor is automatically a insensitive cursor.
· If the base table does not have a unique index, the cursor is created as a insensitive cursor.
· If the SELECT statement contains an order by, and the column of the order by is not a unique row identifier, the dynamic cursor is converted to a keyset cursor.
If the keyset cursor cannot be opened, it is converted to a insensitive cursor. The same is true for cursors defined using SQL ANSI-92 syntax, except that there are no insensitive keywords.
Open Cursor
Opening a cursor is creating a result set. Cursors are defined by the Declare statement, but their actual execution is through the open statement. The syntax is as follows:
OPEN {[GLOBAL] cursor_name} | cursor_variable_name}
Global indicates a universal cursor.
Cursor_name is the name of the cursor being opened.
Cursor_variable_name is the variable name of the referenced cursor. The variable should be a cursor type.
After the cursor is opened, the system variable @ @cursor_rows can be used to detect the number of rows in the result set.
@ @cursor_rows is negative, indicates that the cursor is being migrated asynchronously, with an absolute value (if @ @cursor_rows is-5, the Absolute is 5) is the number of rows for the current result set.
An asynchronous cursor enables the user to still have access to the cursor's results when the cursor is fully migrated.
To take a value from a cursor
During the process of taking a value from a cursor, you can move and process it back and forth on each row in the result set.
If the cursor is defined as scrollable (using the scroll keyword when declaring), any row in the result set can be fetched at any time.
For non-scrolling cursors, only the next row of the current row can be taken. The result set can be taken to a local variable. The syntax for the FETCH command is as follows:
FETCH [NEXT | prior| First | Last | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
From [GLOBAL] cursor_name} | Cursor_variable_name}
[Into @variable_name] [,...... N]]
Next indicates that the value is taken from the next line in the current row.
Prior indicates that a value is taken from the previous line of the current row.
First is the top row of the result set.
Last is the final row of the result set.
ABSOLUTE N Represents the nth row in the result set, and the number of rows can also be propagated through a local variable. The line number starts at 0, so n is 0 and cannot get any rows.
RELATIVE n indicates that the row to be fetched is in the position of the first n rows or the last n rows of the current row. If the value is positive, the row to be fetched is in the position of the first n rows of the current row, and if the value is negative, the last n rows of the current row are returned.
into @cursor_variable_name a list of variables that represent where the cursor column values are stored.
The number of variables in the list should be the same as the number of variables used in the SELECT statement in the DECLARE statement.
The data type of the variable should also be the same as the data type of the selected column. The values in the variables persist until the next time the FETCH statement is used.
The execution of each fetch is stored in the system variable @ @fetch_status.
If fetch succeeds, the @ @fetch_status is set to 0. An @ @fetch_status of 1 indicates that a portion of the result set has been reached (for example, rows in the base table are deleted after the cursor is opened).
@ @fetch_status can be used to construct a loop for cursor processing.
Close Cursors
The close statement is used to close the cursor and free the result set. After the cursor is closed, the fetch operation cannot be performed. If you also need to use a FETCH statement, reopen the cursor. The syntax is as follows:
CLOSE [GLOBAL] cursor_name | Cursor_variable_name
Releasing Cursors
After the cursor is used no longer needed, the cursor is released. The DEALLOCATE statement frees the data structure and locks added by the cursor. The syntax is as follows:
deallocate [GLOBAL] cursor_name | Cursor_variable_name

Three: Basic usage templates for cursors

DECLARE:

DECLARE cursor name [scroll] cursor FOR SELECT statement [for UPDATE [of list name]]

Define a cursor so that it corresponds to a SELECT statement

For UPDATE option, which indicates that the cursor can be used to modify and delete the current row

Open

Opens a cursor, executes the query corresponding to the cursor, and the result set is the active set of the cursor

Open cursor Name

Fetch

Move the cursor to a specific row in the active set and drop the row data into the appropriate variable

Fetch [Next | prior | first | last | current | relative n | absolute m] CURSOR name into [variable table]

Close

Closes the cursor, releasing the active set and the resources it occupies. When you need to use the cursor again, execute the Open statement

Close cursor Name

Deallocate

Delete the cursor and no longer execute the open statement on the cursor

DEALLOCATE cursor Name

@ @FETCH_STATUS

Returns the state of the last cursor executed by the FETCH statement.

0 FETCH statement Succeeded

-1 FETCH statement failed

-2 rows that were fetched do not exist

Example: DECLARE employee_cursor Cursor forselect EmployeeID, Title

From AdventureWorks.HumanResources.Employee;

OPEN Employee_cursor; FETCH NEXT from Employee_cursor;

While @ @FETCH_STATUS = 0

BEGIN

--//to do ...

FETCH NEXT from Employee_cursor;

END;

CLOSE employee_cursor;deallocate Employee_cursor;

GO

Four: Cursor performance issues

The best technique for improving cursor performance is to avoid using cursors when possible, and to do the same with the corresponding statements (typically, the efficiency can be greatly improved).

SQL Server is a relational database that processes datasets much better than processing a single row, and access to individual rows is not suitable for relational DBMS at all.
If you cannot avoid using cursors at times, you can use the following techniques to optimize cursor performance.
(1). Do not use the Static/insensitive cursor unless necessary. Opening a static cursor causes all rows to be copied to the staging table.
     That's why it's not sensitive to change-it's actually a backup to a temporary database table.
     Naturally, the larger the result set, the more the temporary database contention problem is caused by the static cursor that is declared on it.
(2). Do not use the keyset cursor unless necessary. As with a static cursor, opening a keyset cursor creates a temporary table.
      Although this table includes only one key column of the base table (unless there is no unique keyword), it is still quite large when working with large result sets.
(3). When processing a one-way read-only result set, use Fast_forward instead of forward_only. Make
     When you define a forward_only with Fast_forward, the READ_ONLY cursor has some internal performance optimizations.
(4). Use the READ_ONLY keyword to define a read-only cursor. This prevents accidental modification and allows the server to understand that the row is not modified when the cursor moves.
(5). Beware of a large number of row modifications in the transaction through cursors. Depending on the transaction isolation level, these rows remain locked until the transaction is completed or rolled back, which can cause contention on the server for resources.
(6). Be careful with dynamic cursor modifications, especially on tables that are built on non-unique clustered index keys, because they cause "Halloween" problems-duplicate error modifications to the same row or row.
      Because SQL Server internally modifies a row's keywords to an already existing value and forces the server to append the subscript so that it can later move in the result set.
       When accessed from the remainder of the result set, the row is encountered and the program repeats, resulting in a dead loop.
(7). For large result sets, consider using asynchronous cursors to give control to the caller as much as possible. Asynchronous cursors are especially useful when you return a fairly large result set to a moveable table.

Use of SQL SERVER cursor Cursors (reprint)