SQL Server Cursors use

/***
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 [,...] 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 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 extracted 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

**/ CREATE PROCEDUREpk_test as --declaration of 2 variablesDeclare @O_ID nvarchar( -) 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 cursorDeclareMyCursorcursor  for SelectO_id,a_salary fromaddsalary--Open CursorOpenMyCursor--Extract data from Grandperi to the 2 variables we just declared.Fetch Next  fromMyCursor into @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 pulls out a piece of data' Print @O_ID Print @A_Salary --use a cursor to remove a recordFetch Next  fromMyCursor into @O_ID,@A_Salary End --Close CursorsCloseMyCursor--Undo CursordeallocateMyCursorGO

Transferred from: http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html