A comprehensive explanation of SQL Server cursor concepts and examples

Introduction
Let's start with an example of what the cursor concept, steps, and syntax are:

Table One originsalary table two addsalary

Now there are 2 tables, one is the Originsalary table-the payroll, there are three fields 0_id employee number (NVARCHAR), O_name employee name (NVARCHAR), o_salary salary (FLOAT).
Another table Addsalary table-the salary increase table. There are 2 fields, o_id employee number, a_salary increase salary. The o_id of the two tables corresponds to one by one, now the salary of the pay + the original salary = current salary, that is, o_salary=o_salary+a_salary, modify the table originsalary salary field.
For some programmers who are unfamiliar with cursors, this is not a very difficult question, and it can be easy to implement it in a program. Let me first talk about the idea of using the ASP. NET program to solve this problem:
1. First get the record number of table Originsalary, 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 "Get view.
3. Use the dataset to get o_salary=o_salary+a_salary.
4. Write the UPDATE statement "update originsalary set o_salary=" To add the value "where o_id=" to get the value "
5. Cycle 3 times to complete this function.
Another way is to write a stored procedure, which I will not list here.
I want you to think about this before learning about cursors, and some examples of batch processing. Some people might say, "Can't the database handle data in one row?" Addsalary the data of the table into rows, and then the table originsalary the data line-by-row modification? The answer is certainly yes. This is the cursor concept. In the next chapter, we'll talk about cursors. I will use a cursor to solve the problem that I left to you just now.

1.1 The concept of 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.
Advantages of 1.2 Cursors
The following advantages are obtained from the cursor definition, which makes the cursor play an important role in the actual application:
1) allows the program to perform the same or different operations on each row in the rowset returned by the query statement select, rather than performing the same operation on the entire rowset.
2) provides the ability to delete and update rows in a table based on the cursor location.
3) cursors actually serve as a bridge between the set-oriented database management system (RDBMS) and the line-oriented program design, which enables the two processing methods to communicate through cursors.
1.3 Use of Cursors
Speaking of the advantages of this multi-cursor, we are now going to reveal the mysterious veil of the cursor in person.
The order in which cursors are used: reputation cursors, open cursors, read data, close cursors, delete cursors.
1.3.1 Declaring cursors
Simplest cursor declaration: DECLARE < cursor name >cursor for<select statement >;
Where the SELECT statement can be a simple query, or it can be a complex set of queries and nested queries
Example: [Table 2 addsalary for example]
Declare mycursor Cursor FOR SELECT * FROM Addsalary
So I declare a cursor to the table addsalary MyCursor

"Advanced Notes"
DECLARE < cursor name > [insensitive] [SCROLL] Cursorfor<select statement >
Here I say a downstream standard [insensitive] and [SCROLL] in intermediate applications
Insensitive
Indicates that MS SQL SERVER will store the data records selected by the cursor definition in a temporary table (built under the tempdb database). Read operations on the cursor are answered by the temporary table. Therefore, the modification of the base table does not affect the data that the cursor extracts, that is, the cursor does not change with the contents of the underlying table, nor does it update the base table with the cursor. If the reserved word is not used, updates and deletions to the base table are reflected in the cursor.
It should also be noted that the cursor will automatically set the INSENSITIVE option when the following conditions occur.
A. Use the distinct, GROUP by, and having UNION statements in the SELECT statement;
B. Using outer JOIN;
C. Any of the selected tables are not indexed;
D. Treat the real value as the selected column.
SCROLL
Indicates that all extraction operations (such as first, last, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. If the reserved word is not used, then only the next fetch operation is possible. Thus, SCROLL greatly increases the flexibility of extracting data, and can read any row of data records in the result set without having to close and
Re-open the cursor.

1.3.2 Opening Cursors
Very simply, we'll open the cursor we just declared MyCursor
OPEN MyCursor

1.3.3 reading data
FETCH [NEXT | PRIOR | First | Last] from {cursor name | @ cursor variable name} [into @ variable name [, ...]]
Parameter description:
Next takes a row of data and takes the next line as the current row (increment). Because the row pointer is before the 1th row of the cursor after the cursor is opened, the first fetch next operation obtains the 1th row of data in the cursor set. Next is the default cursor extraction option.
into @ variable name [,...] The column data for the extraction operation is placed in the local variable. Each variable in the list is associated from left to right with the corresponding column in the cursor result set. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the result column data type. The number of variables must be the same as the number of columns in the cursor selection list.

Now let's get the data out of the MyCursor cursor!

When the cursor is opened, the row pointer will point to the 1th row of the cursor set, and if you want to read the 1th row of data in the cursor set, you must move the row pointer to the 1th row. For this example, you can read the 1th row of data using the following operations:
Eg:fetch next from MyCursor or Fetch first from MyCursor
So I take out the data in the cursor, but the light is not enough, and we need to assign the extracted data to the variable
Declaration of 2 variables
DECLARE @O_ID NVARCHAR (20)
DECLARE @A_Salary float
Pass the fetched value to the 2 variables just declared
Fetch next from MyCursor to @ o_id,@ a_salary

1.3.4 Closing Cursors
CLOSE MyCursor

1.3.5 Deleting cursors
Deallocate mycursor

1.3.6 Instance Training
As I've covered the 5 steps used by cursors, let's take a hand and practice using a cursor to take out the data from table 2 addsalary.
In order to run the cursors we created ourselves, we write the cursor in the stored procedure so that we can see the whole process of using the cursor.
Create a new stored procedure in sqlserver2000:
CREATE PROCEDURE Pk_test
As
Declaration of 2 variables
declare @O_ID nvarchar (20)
DECLARE @A_Salary float

Declares that the number of parameters in a cursor Mycursor,select statement must be the same as the variable name taken from the cursor
Declare mycursor cursor FOR select o_id,a_salary from Addsalary

Open cursor
Open MyCursor

Extract data from Grandperi to the 2 variables we just declared.
FETCH NEXT from MyCursor to @O_ID, @A_Salary

Determining the state of a cursor
0 FETCH statement Succeeded
-1 FETCH statement failed or row not in result set
-2 rows that were fetched do not exist
while (@ @fetch_status =0)
Begin

Shows the value we take out each time we use a cursor.
print ' cursor successfully fetched a piece of data '
Print @O_ID
Print @A_Salary

Use a cursor to remove a record
FETCH NEXT from MyCursor to @O_ID, @A_Salary
End
Close Cursors
Close MyCursor
Undo Cursor
Deallocate mycursor
GO

With the comments above, I think we all understand the whole process of creating the cursor. But we are still an abstract understanding, we learn any knowledge, to be used in practice, so as to make the abstract things become concrete.
So let's run the stored procedure and see how the cursor is valued:
We open the SQLSERVER2000 Query Analyzer, we set up a good database, we execute the stored procedure
Exec Pk_test

Let me see the effect ()

By example, we can see that the cursor has taken the value out of line by line. Then I ask you not to look at the following answer, in the introductory part I just left a question for everyone to try to solve it?
Now let's write a stored procedure to solve the problem I left behind.
CREATE PROCEDURE Pk_salaryadd
As
Declare @O_ID nvarchar, @A_Salary float
Declare mycursor cursor FOR select o_id,a_salary from Addsalary
Open MyCursor
FETCH NEXT from MyCursor to @O_ID, @A_Salary
while (@ @fetch_status = 0)
Begin
Update originsalary Set [email protected]_salary where [email protected]_id
FETCH NEXT from MyCursor to @O_ID, @A_Salary
End
Close MyCursor
Deallocate mycursor
GO

According to the old method, we use Query Analyzer to execute our stored procedure and see what the result is.
Exec Pk_salaryadd
Let me see the effect ()

Execute the stored procedure and see that we have affected 3 rows of data

With the SQL statement, look at the results of table Originsalary now:

1.4 Concluding remarks
I am glad that you can read this tutorial, in fact, this is just the most basic application of cursors, showing the logic of life in the relationship, there may be more complex cursors. But we only learn to walk, can run, O (∩_∩) o ....

A comprehensive explanation of SQL Server cursor concepts and examples