SQL Server cursor concept and comprehensive instance explanation

Introduction
Let's skip the concepts, steps, and syntax of the cursor. Let's look at an example:

Table 1 originsalary Table 2 addsalary

There are now two tables, one of which is originsalary table-Payroll table. There are three fields: 0_id employee ID (nvarchar), o_name employee name (nvarchar), and o_salary salary (float ).
Another addsalary table-salary increase table. There are two fields, o_id employee number and a_salary increase salary. The o_id of the two tables is one-to-one, and now the salary of the salary increase + the original salary = the current salary, that is, o_salary = o_salary + a_salary, is used to modify the salary field of the originsalary table.
For some programmers who are not familiar with cursors, this is not a very difficult problem. It may be very easy to implement it using a program. Let me talk about the idea of using ASP. NET programs to solve this problem:
1. first obtain the number of records in the originsalary table and write a loop.
2. Write the SQL statement "select * From DBO. originsalary as a left join DBO. addsalary as B on a.o _ id = B .o _ id" to obtain the view.
3. Use dataset to get o_salary = o_salary + a_salary.
4. Write the update statement "Update originsalary set o_salary =" and add the value "where o_id =" to get the value"
5. This function is completed three times in a loop.
Another method is to write the stored procedure. I will not list it here.
I think you should think about this problem and some batch processing examples before learning about the cursor. Some may say, "cannot a database process data in one row? Will the data of the table addsalary be taken out row by row, and then the originsalary data is modified row by row ?" The answer is yes, of course. This is the cursor concept. In the next chapter, let's talk about what a cursor is? I will use the cursor to solve the problem left for you just now.
 
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
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
 
Through the above annotations, I think everyone understands the creation process of the cursor. However, we still have an abstract understanding. We need to apply any knowledge to practice so that we can make abstract things more concrete.
Then we will run the stored procedure to see how the cursor is taken:
We open the query Analyzer of sqlserver2000, and after the database is ready, we execute the Stored Procedure
Exec pk_test

Let me see the effect ()

Through the instance, we can see that the cursor extracts all values row by row. So I will not take a look at the answer below. In the introduction section, I will leave a question for you. Can you solve it?
Now, let's write a stored procedure to solve the problem I just stayed behind.
Create procedure pk_salaryadd
As
Declare @ o_id nvarchar (20), @ a_salary float
Declare mycursor cursor for select o_id, a_salary from addsalary
Open mycursor
Fetch next from mycursor into @ o_id, @ a_salary
While (@ fetch_status = 0)
Begin
Update originsalary set o_salary = o_salary + @ a_salary where o_id = @ o_id
Fetch next from mycursor into @ o_id, @ a_salary
End
Close mycursor
Deallocate mycursor
Go
 
According to the old method, we use the query analyzer to execute our stored procedures and see what the results are like?
Exec pk_salaryadd
Let me see the effect ()

After executing the stored procedure, we can see that three rows of data are affected.

Use SQL statements to check the current results of originsalary:

1.4 conclusion
I'm glad that you can read this tutorial. In fact, this is only the most basic application of the cursor. It shows that there may be more complicated cursors in the relationship between the logic of life. But we can run only when we learn to walk ....