SQL cursor usage

1. Why cursor:
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.
2. How to use a cursor:
Generally, using a cursor follows the following general steps:
(1) Declare the cursor. Associate the cursor with the result set of the T-SQL statement.
(2) Open the cursor.
(3) Use a cursor to operate data.
(4) Close the cursor.
2.1. Declare a cursor
Standard syntax format of the declare cursor statement SQL-92:
Declare cursor name [insensitive] [scroll] cursor
For SQL-Statement
Eg:
Declare mycrsrvar cursor
For select * From tbmydata
2.2 Open cursor
Open mycrsrvar
When the cursor is opened, the row pointer will point to the cursor set before the 1st rows, if you want to read 1st rows of data in the cursor set, you must move the row pointer to 1st rows. In this example, you can use the following operations to read 1st rows of data:
Fetch first from e1cursor
Or fetch next from e1cursor

2.3 use a cursor to operate data
The following example uses @ fetch_status to control the cursor activity in a while loop.
/* Perform the following operations to read data using a cursor. */
Declare e1cursor cursor/* declares the cursor. The default value is forward_only */
For select * From c_example
Open e1cursor/* Open cursor */
Fetch next from e1cursor/* read 1st rows of Data */
While @ fetch_status = 0/* use a while loop to control cursor activity */
Begin
Fetch next from e1cursor/* Other rows of data will be read in the loop body */
End
Close e1cursor/* close the cursor */
Deallocate e1cursor/* Delete cursor */

 

Declare @ ID int, @ name varchar (20 );
Declare cur cursor fast_forward
Select ID, name from;
Open cur;
Fetch next from cur into @ ID, @ name;
While @ fetch_status = 0
Begin
-- Do what you want to do
Fetch next from cur into @ ID, @ name;
End
Close cur;
Deallocate cur;

2.4 close the cursor
use the close statement to close the cursor
close {[Global] cursor name} | cursor variable name}

use the deallocate statement to delete a cursor, the syntax format is as follows:
deallocate {[Global] cursor name} | @ cursor variable name
3. the concise syntax of the fetch operation is as follows:
fetch
[next | prior | first | last]
from
{cursor name | @ cursor variable name} [into @ variable name [, …]]
parameter description:
next removes the data of a row and uses the next row as the current row (incrementing ). Since the row pointer is directed to the cursor before the cursor is opened, the first execution of the fetch next operation will obtain 1st rows of data in the cursor set. Next is the default cursor extraction option.
into @ variable name [,…] Put the extracted column data in a local variable. The variables in the list are associated with the corresponding columns in the cursor result set from left to right. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the data type of the result column. The number of variables must be the same as the number of columns in the cursor selection list.
success-
after each fetch operation is performed, check the status value in the global variable @ fetch_status to determine whether the fetch operation is successful. The variable has three State values:
. 0 indicates that the fetch statement is successfully executed.
·-1 indicates that the fetch statement failed. For example, moving the row pointer exceeded the result set.
-2 indicates that the extracted row does not exist.
because @ fetch_statu is a global variable, all the cursors on a connection may affect the value of this variable. Therefore, after executing a fetch statement, you must test the value of this variable before executing another fetch Statement on another cursor to make a correct judgment.