A comprehensive explanation of SQL Server cursor concepts and examples

Introduction We do not talk about what the cursor concept, steps and syntax, first look at an example:

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 solving this problem with the ASP: 1.       first get the record number of table Originsalary, write a loop. 2.       writes 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.       uses a dataset to get o_salary=o_salary+a_salary. 4.       write the UPDATE statement "update originsalary set o_salary=" add Value "where o_id=" Get 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 Cursor Concepts   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 thatConverts a collection operation into 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. The advantages of the 1.2 cursor from the cursor definition can be obtained with the following advantages, which make 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 a cursor location.   3) cursors actually serve as a bridge between a set-oriented database management system (RDBMS) and line-oriented programming, which enables these two processes to communicate through cursors. 1.3 Use of cursors   The advantages of this multi-cursor, now we are going to uncover 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 declares the cursor's 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 query and nested query example: [Table 2 Addsalary For example] Declare mycursor cursor FOR SELECT * FROM addsalary so I declare a cursor addsalary   "Advanced remarks" MyCursor Declare ; cursor name > [insensitive] [SCROLL] Cursorfor<select statement > Here I say [insensitive] and [SCROLL] insensitive in a downstream standard application indicate Ms SQL SERVER stores 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. Using distinct, GROUP by, HA in SELECT statementsVING Tsun UNION statement; b. use outer JOIN; C. Any table selected does not have an index; d. treats 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 the cursor is very simple, we open the cursor we declared just now 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   takes down a row of data and puts 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 [,...]   puts the column data of the extraction operation into 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 by using the following operation: 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, we also need to assign the extracted data to the variable/ /DECLARE 2 variables declare @O_ID NVARCHAR declare @A_Salary float//The value to be fetched into the 2 variables just declared Fetch next from mycursor into @ o_id,@ a_sal ary   1.3.4 closing cursors close MYCURSOR&NBSP;&Nbsp;            1.3.5 Delete cursor deallocate mycursor             1.3.6 example training as I've covered the 5 steps used by cursors, now let's get to the top of the hand and practice using cursors to take out table 2 addsalary data. 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//DECLARE 2 variables declare @O_ID nvarchar    declare @A_Salary FL Oat  //declare 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  //Opening cursors Open MyCursor  //Extract data from Grandperi to the 2 variables we just declared, fetch next from the MyCursor into @O_ID, @A_Salary &nbsp ; To determine the state of a cursor//0 FETCH statement succeeded     //-1 FETCH statement failed or this row is not in result set     //-2 fetched row does not exist while (@ @fetch_status =0) begin  //Show us each time we use the cursor out of the value    print ' cursor successfully fetched a data '    print @O_ID    prin T @A_Salary  //Use a cursor to remove a record    fetch next from MyCursor to @O_ID, @A_Salary end//close cursor close MyCursor//Undo Tour Standard Deallocate mycursor GO   through 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 this stored procedure and see how the cursor is valued: We open the SQLSERVER2000 Query Analyzer, we set up the 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 just left behind. CREATE PROCEDURE Pk_salaryadd as declare @O_ID nvarchar, @A_Salary float declare mycursor curs Or for select O_id,a_salary from Addsalary open mycursor fetch next from MyCursor to @O_ID, @A_Salary while (@ @fetch_statu s = 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'm glad to see this tutorial, but this is just one of the most fundamental applications of cursors, which shows the logic of life in the relationship, there may be more complex cursors. But we only learn to walk, can run, O (∩_∩) o ....

Transferred from: http://www.cnblogs.com/wudiwushen/archive/2010/03/30/1700925.html

A comprehensive explanation of SQL Server cursor concepts and examples