SQL Server cursor

1.1 cursor Concept
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.

1.2 advantages of cursor
The following advantages can be obtained from the cursor definition. These advantages make the cursor play an important role in practical application:
1) Allow the program to perform the same or different operations on each row in the row set returned by the SELECT statement, rather than the same operation on the entire row set.
2) provides the ability to delete and update rows in a table based on the cursor position.
3) cursors serve as a bridge between a collection-oriented database management system (RDBMS) and a row-oriented program design. These two processing methods are communicated through cursors.

1.3 Use of cursors
After talking about the advantages of this multi-game, we will unveil the secret of the cursor.
The order in which the cursor is used: Name cursor, open cursor, read data, close cursor, delete cursor.
1.3.1 declare a cursor
Simplest cursor Declaration: declare <cursor Name> cursor for <SELECT statement>;
The SELECT statement can be a simple query, or a complex successive query or nested query.
Example: [Table 2 addsalary is used as an example]
Declare mycursor cursor for select * From addsalary
In this way, I declare a cursor mycursor to the table addsalary.
 
[Advanced Remarks]
Declare <cursor Name> [insensitive] [scroll] cursorfor <SELECT statement>
Here I will talk about [insensitive] and [scroll] In the cursor intermediate application.
Insensitive
Ms SQL server stores the data records selected by the cursor definition in a temporary table (created in the tempdb database ). Read operations on the cursor are all responded to by the temporary table. Therefore, modification to the basic table does not affect the data extracted by the cursor. That is, the cursor does not change with the content of the basic table, and cannot be used to update the basic table. If this reserved word is not used, the update and deletion of the basic table will be reflected in the cursor.
It should also be noted that when the following conditions occur, the insensitive option is automatically set for the cursor.
A. Use the distinct, group by, and having Union statements in the SELECT statement;
B. Use Outer Join;
C. No index is available for any selected table;
D. Use the real value as the selected column.
Scroll
Indicates that all the extraction operations (such as first, last, Prior, next, relative, and absolute) are available. If you do not use this reserved word, you can only perform next extraction. It can be seen that scroll greatly increases the flexibility of data extraction, and can read any row of data records in the result set at will without the need to close it.
Reopen the cursor.
 
1.3.2 open a cursor
It's very simple. We just opened the declared cursor mycursor.
Open mycursor
 
1.3.3 read data
Fetch [next | prior | first | last] from {cursor name | @ cursor variable name} [into @ variable name [,…] ]
Parameter description:
Next, remove the data of a row and use 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.
 
Now let's retrieve the data of the mycursor cursor!
 
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:
Eg: Fetch next from mycursor or fetch first from mycursor
In this way, the data in the cursor is taken out, but the light is not enough. We also need to assign the retrieved data to the variable.
// Declare two variables
Declare @ o_id nvarchar (20)
Declare @ a_salary float
// Pass the retrieved value to the two variables just declared
Fetch next from mycursor into @ o_id, @ a_salary
 
1.3.4 close the cursor
Close mycursor

1.3.5 delete a cursor
Deallocate mycursor
 
1.3.6 instance training
As mentioned above, I have finished the five steps for using the cursor. Now let's start and use the cursor to retrieve the data in Table 2 addsalary.
To run the self-created cursor, we write the cursor in the stored procedure, so that we can see the entire usage process of the cursor.
Create a new stored procedure in sqlserver2000:
Create procedure pk_test
As
// Declare two variables
Declare @ o_id nvarchar (20)
Declare @ a_salary float
 
// Declare a cursor mycursor. The number of parameters in the SELECT statement must be the same as the variable name retrieved from the cursor.
Declare mycursor cursor for select o_id, a_salary from addsalary
 
// Open the cursor
Open mycursor
 
// Retrieve the data from the cursor and assign values to the two variables we just declared
Fetch next from mycursor into @ o_id, @ a_salary
 
// Determine the cursor status
// The 0 fetch statement is successful.
//-1 The fetch statement fails or this row is not in the result set
//-2 The extracted row does not exist
// 100 is in the last row
While (@ fetch_status = 0)
Begin
 
// Display the value we retrieve with the cursor each time
Print 'a data entry is successfully retrieved from the curss'
Print @ o_id
Print @ a_salary
 
// Use the cursor to retrieve the next record
Fetch next from mycursor into @ o_id, @ a_salary
End
// Close the cursor
Close mycursor
// Undo the cursor
Deallocate mycursor
Go