SQL Server cursor

I. cursor Recognition

 
Cursor allows you to access the result set returned by SQL Server row by row.
A major reason for using cursor is to convert the set operation into a single record processing method.
After retrieving data from a database using SQL, the results are stored in a memory area, and the results are often a collection containing multiple records.
The cursor mechanism allows you to access these records row by row in SQL Server and display and process these records as you wish.
 

 
Ii. Basic cursor format
 

 Declared 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 is to be created for the retrieved result set, and later data is obtained from this temporary copy. If the data in the original base table changes during subsequent cursor processing, they are invisible to the cursor. This insensitive cursor does not allow data changes.
The scroll keyword indicates that the cursor can be rolled up in any way. All fetch options (first, last, next, relative, and absolute) can be used in the cursor. If this option is ignored, the cursor can only scroll forward (next ).
Select_statement indicates the result set created by the SQL statement. The statement compute, compute by, for browse, and into cannot be used in the selection statement of the cursor declaration.
Read only indicates that data modification is 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 that can be modified in the result set. 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 declared process.
The Global keyword makes the cursor globally visible for the entire connection. The global cursor is available at any time when the connection is activated. The cursor is no longer available only when the connection ends.
Forward_only indicates that the cursor can only scroll forward.
Static and insensitive cursors are the same.
Keyset indicates the order of the selected rows. SQL Server creates a temporary keyword set from the result set. If the non-Keyword columns of the database are modified, they are visible to the cursor. Because it is a fixed set of keywords, it is invisible to modify keyword columns or insert new columns.
Dynamic indicates that the cursor will reflect all modifications to the result set.
Scroll_lock is used to lock the modification or deletion of a cursor to ensure its operation is successful.
Optimistic specifies which modifications or deletions made through the cursor will not succeed.
Note:
· If distinct, union, and group by statements are used in the SELECT statement and the selection contains an aggregate expression, the cursor is automatically an insensitive cursor.
· If the base table does not have a unique index, the cursor is created as an insensitive cursor.
· If the SELECT statement contains order by, and the column identified by 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 an insensitive cursor. The same is true for a cursor defined using the SQL ANSI-92 syntax, but there is no insensitive keyword.
 Open cursor
 Open a cursor to create a result set. The cursor is defined by the declare statement, but its actual execution is through the open statement. Syntax:
Open {[Global] cursor_name} | cursor_variable_name}
Global indicates a global cursor.
Cursor_name is the name of the opened cursor.
Cursor_variable_name is the variable name of the referenced cursor. The variable should be of the cursor type.
After the cursor is opened, the system variable @ cursor_rows can be used to check the number of rows in the result set. @ Cursor_rows indicates that the cursor is being asynchronously migrated. The absolute value (if @ cursor_rows is-5, the absolute value is 5) indicates the number of rows in the current result set. Asynchronous cursors allow users to access the cursors when the cursor is fully migrated.
 Value From cursor 
You can move and process each row in the result set when you set the value from the cursor. If the cursor is defined as scrollable (the scroll keyword is used during Declaration), any row in the result set can be retrieved at any time. For non-scroll cursors, only the next row of the current row can be retrieved. The result set can be retrieved from local variables. The syntax of 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 the value of the next row from the current row.
Prior indicates the value of the first row from the current row.
First is the first line of the result set.
Last is the last row of the result set.
Absolute n indicates the nth row in the result set. The number of rows can also be transmitted through a local variable. The row number starts from 0, so when n is 0, no rows can be obtained.
Relative n indicates that the row to be retrieved is at 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 retrieved is at the position of the First n rows of the current row. If the value is negative, the last n rows of the current row are returned.
Into @ cursor_variable_name indicates the list of variables where the cursor column value is stored. The number of variables in the list should be the same as the number of variables used by 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 remain until the next fetch statement is used.
Every execution of fetch is stored in the system variable @ fetch_status. If fetch is successful, @ fetch_status is set to 0. @ Fetch_status-1 indicates that a part of the result set has been reached (for example, the row in the base table is deleted after the cursor is opened ). @ Fetch_status can be used to construct a cursor processing cycle.
 Close cursor 
The close statement is used to close the cursor and release the result set. After the cursor is closed, you cannot perform the fetch operation again. If you still need to use the fetch statement, you need to re-open the cursor. Syntax:
Close [Global] cursor_name | cursor_variable_name
 Release cursor 
Release the cursor after the cursor is no longer needed. The deallocate statement releases the lock applied to the data structure and cursor. Syntax:
Deallocate [Global] cursor_name | cursor_variable_name

 
III. Basic usage templates of cursors
 

Declare:
Declare cursor name [scroll] cursor for select statement [for update [of list name]
 
 
Define a cursor to correspond to a select statement
 
 
For update, which indicates that the cursor can be used to modify and delete the current row.
 
 
Open
 
Open a cursor and execute the query corresponding to the cursor. The result set is the active set of the cursor.
 
 
Open cursor name
 
 
Fetch
 
 
Move the cursor to a specific row in the activity set, and extract the row data and put it in the corresponding variable.
 
Fetch [next | prior | first | last | current | Relative n | absolute m] cursor name into [variable table]
 
 
Close
 
 
Close the cursor to release the activity set and its resources. Execute the open statement when you need to use the cursor again.
 
 
Close cursor name
 
 
Deallocate
 
Delete the cursor. You cannot execute an open statement on the cursor later.
 
 
Deallocate cursor name
 
 
@ Fetch_status
 
 
Returns the status of the last cursor executed by the fetch statement.
 
 
0 fetch statement successful
 
-1 fetch statement failed
 
 
-2 The extracted row does 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
 

 
Iv. cursor Performance
 

 
The best technique to improve the performance of a cursor is to avoid using a cursor when it can be avoided, and use the corresponding statement to accomplish the same function as much as possible (generally, the efficiency can be greatly improved when appropriate ).
 

 
As a relational database, SQL Server Processes data sets much better than a single row. Access from individual rows is not suitable for relational DBMS. If you cannot avoid using a cursor, you can use the following techniques to optimize the performance of the cursor.
(1). Do not use static/Insensitive cursunless necessary. Opening the static cursor will cause all rows to be copied to the temporary table. This is why it is not sensitive to changes-it actually points
 

A backup in the temporary database table. Naturally, the larger the result set, the declaration of the static cursor on it will cause the more temporary database resources to compete.
(2). Do not use the keyset cursor unless necessary. Like a static cursor, opening a keyset cursor creates a temporary table. Although this table only contains one keyword column of the basic table (unless there is no unique keyword ),
 

 
However, when processing a large result set, it will be quite large.
(3) when processing a one-way read-only result set, use fast_forward instead of forward_only. If fast_forward is used to define a forward_only, The read_only cursor has certain internal performance optimization.
(4). Use the read_only keyword to define the read-only cursor. This prevents accidental modification, and allows the server to know that the row will not be modified when the cursor moves.
(5). Be careful with the large number of row modifications made through the cursor during transaction processing. At the transaction isolation level, these rows remain locked before the transaction is completed or rolled back, which may cause resource competition on the server.
(6 ). exercise caution when modifying the dynamic cursor, especially when the cursor is created on a table with a non-unique clustered index key, because they will cause a "Halloween" problem-making changes to duplicate errors of the same row or the same row.
 

 
Because SQL Server internally changes the keyword of a row to an existing value and forces the server to append the subscript so that it can be moved in the result set later. When you access from the remaining items in the result set,
 

 
And thenProgramWill be repeated, resulting in an endless loop.
(7). For large result sets, consider using asynchronous cursors and give control to the caller as much as possible. Asynchronous cursors are particularly useful when returning a large result set to a movable table,
 

Because they allow applications to display rows almost immediately.
 

 
Cursor allows you to access the result set returned by SQL Server row by row.
A major reason for using cursor is to convert the set operation into a single record processing method.
After retrieving data from a database using SQL, the results are stored in a memory area, and the results are often a collection containing multiple records.
The cursor mechanism allows you to access these records row by row in SQL Server and display and process these records as you wish.
 

 
Ii. Basic cursor format
 

 Declared 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 is to be created for the retrieved result set, and later data is obtained from this temporary copy. If the data in the original base table changes during subsequent cursor processing, they are invisible to the cursor. This insensitive cursor does not allow data changes.
The scroll keyword indicates that the cursor can be rolled up in any way. All fetch options (first, last, next, relative, and absolute) can be used in the cursor. If this option is ignored, the cursor can only scroll forward (next ).
Select_statement indicates the result set created by the SQL statement. The statement compute, compute by, for browse, and into cannot be used in the selection statement of the cursor declaration.
Read only indicates that data modification is 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 that can be modified in the result set. 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 declared process.
The Global keyword makes the cursor globally visible for the entire connection. The global cursor is available at any time when the connection is activated. The cursor is no longer available only when the connection ends.
Forward_only indicates that the cursor can only scroll forward.
Static and insensitive cursors are the same.
Keyset indicates the order of the selected rows. SQL Server creates a temporary keyword set from the result set. If the non-Keyword columns of the database are modified, they are visible to the cursor. Because it is a fixed set of keywords, it is invisible to modify keyword columns or insert new columns.
Dynamic indicates that the cursor will reflect all modifications to the result set.
Scroll_lock is used to lock the modification or deletion of a cursor to ensure its operation is successful.
Optimistic specifies which modifications or deletions made through the cursor will not succeed.
Note:
· If distinct, union, and group by statements are used in the SELECT statement and the selection contains an aggregate expression, the cursor is automatically an insensitive cursor.
· If the base table does not have a unique index, the cursor is created as an insensitive cursor.
· If the SELECT statement contains order by, and the column identified by 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 an insensitive cursor. The same is true for a cursor defined using the SQL ANSI-92 syntax, but there is no insensitive keyword.
 Open cursor
 Open a cursor to create a result set. The cursor is defined by the declare statement, but its actual execution is through the open statement. Syntax:
Open {[Global] cursor_name} | cursor_variable_name}
Global indicates a global cursor.
Cursor_name is the name of the opened cursor.
Cursor_variable_name is the variable name of the referenced cursor. The variable should be of the cursor type.
After the cursor is opened, the system variable @ cursor_rows can be used to check the number of rows in the result set. @ Cursor_rows indicates that the cursor is being asynchronously migrated. The absolute value (if @ cursor_rows is-5, the absolute value is 5) indicates the number of rows in the current result set. Asynchronous cursors allow users to access the cursors when the cursor is fully migrated.
 Value From cursor 
You can move and process each row in the result set when you set the value from the cursor. If the cursor is defined as scrollable (the scroll keyword is used during Declaration), any row in the result set can be retrieved at any time. For non-scroll cursors, only the next row of the current row can be retrieved. The result set can be retrieved from local variables. The syntax of 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 the value of the next row from the current row.
Prior indicates the value of the first row from the current row.
First is the first line of the result set.
Last is the last row of the result set.
Absolute n indicates the nth row in the result set. The number of rows can also be transmitted through a local variable. The row number starts from 0, so when n is 0, no rows can be obtained.
Relative n indicates that the row to be retrieved is at 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 retrieved is at the position of the First n rows of the current row. If the value is negative, the last n rows of the current row are returned.
Into @ cursor_variable_name indicates the list of variables where the cursor column value is stored. The number of variables in the list should be the same as the number of variables used by 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 remain until the next fetch statement is used.
Every execution of fetch is stored in the system variable @ fetch_status. If fetch is successful, @ fetch_status is set to 0. @ Fetch_status-1 indicates that a part of the result set has been reached (for example, the row in the base table is deleted after the cursor is opened ). @ Fetch_status can be used to construct a cursor processing cycle.
 Close cursor 
The close statement is used to close the cursor and release the result set. After the cursor is closed, you cannot perform the fetch operation again. If you still need to use the fetch statement, you need to re-open the cursor. Syntax:
Close [Global] cursor_name | cursor_variable_name
 Release cursor 
Release the cursor after the cursor is no longer needed. The deallocate statement releases the lock applied to the data structure and cursor. Syntax:
Deallocate [Global] cursor_name | cursor_variable_name

 
III. Basic usage templates of cursors
 

Declare:
Declare cursor name [scroll] cursor for select statement [for update [of list name]
 
 
Define a cursor to correspond to a select statement
 
 
For update, which indicates that the cursor can be used to modify and delete the current row.
 
 
Open
 
Open a cursor and execute the query corresponding to the cursor. The result set is the active set of the cursor.
 
 
Open cursor name
 
 
Fetch
 
 
Move the cursor to a specific row in the activity set, and extract the row data and put it in the corresponding variable.
 
Fetch [next | prior | first | last | current | Relative n | absolute m] cursor name into [variable table]
 
 
Close
 
 
Close the cursor to release the activity set and its resources. Execute the open statement when you need to use the cursor again.
 
 
Close cursor name
 
 
Deallocate
 
Delete the cursor. You cannot execute an open statement on the cursor later.
 
 
Deallocate cursor name
 
 
@ Fetch_status
 
 
Returns the status of the last cursor executed by the fetch statement.
 
 
0 fetch statement successful
 
-1 fetch statement failed
 
 
-2 The extracted row does 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
 

 
Iv. cursor Performance
 

 
The best technique to improve the performance of a cursor is to avoid using a cursor when it can be avoided, and use the corresponding statement to accomplish the same function as much as possible (generally, the efficiency can be greatly improved when appropriate ).
 

 
As a relational database, SQL Server Processes data sets much better than a single row. Access from individual rows is not suitable for relational DBMS. If you cannot avoid using a cursor, you can use the following techniques to optimize the performance of the cursor.
(1). Do not use static/Insensitive cursunless necessary. Opening the static cursor will cause all rows to be copied to the temporary table. This is why it is not sensitive to changes-it actually points
 

A backup in the temporary database table. Naturally, the larger the result set, the declaration of the static cursor on it will cause the more temporary database resources to compete.
(2). Do not use the keyset cursor unless necessary. Like a static cursor, opening a keyset cursor creates a temporary table. Although this table only contains one keyword column of the basic table (unless there is no unique keyword ),
 

 
However, when processing a large result set, it will be quite large.
(3) when processing a one-way read-only result set, use fast_forward instead of forward_only. If fast_forward is used to define a forward_only, The read_only cursor has certain internal performance optimization.
(4). Use the read_only keyword to define the read-only cursor. This prevents accidental modification, and allows the server to know that the row will not be modified when the cursor moves.
(5). Be careful with the large number of row modifications made through the cursor during transaction processing. At the transaction isolation level, these rows remain locked before the transaction is completed or rolled back, which may cause resource competition on the server.
(6 ). exercise caution when modifying the dynamic cursor, especially when the cursor is created on a table with a non-unique clustered index key, because they will cause a "Halloween" problem-making changes to duplicate errors of the same row or the same row.
 

 
Because SQL Server internally changes the keyword of a row to an existing value and forces the server to append the subscript so that it can be moved in the result set later. When you access from the remaining items in the result set,
 

 
And then the program will repeat, resulting in an endless loop.
(7). For large result sets, consider using asynchronous cursors and give control to the caller as much as possible. Asynchronous cursors are particularly useful when returning a large result set to a movable table,
 

 because they allow applications to display rows almost immediately.