End of 2015 as a result of the project needs, learning the side of the Development project learning cursors and stored procedures is hereby recorded

Use of Cursors
The order in which cursors are used: reputation cursors, open cursors, read data, close cursors, delete cursors.

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

2. Open the cursor
Very simply, we'll open the cursor we just declared MyCursor
OPEN MyCursor

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.

4. Close the cursor
CLOSE MyCursor

5. Deleting cursors
Deallocate mycursor

6. Cycle

Use the While loop before closing the cursor:

while (@ @FETCH_STATUS = 0)

Begin

--in-loop operation

FETCH [NEXT | PRIOR | First |  Last] from {cursor name | @ cursor variable name} [into @ variable name [, ...]] --There must be

End

@ @FETCH_STATUS Value

---0 FETCH statement succeeded

---1 FETCH statement failed or the row is not in the result set
---2 rows that are fetched do not exist

7. Example

--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
--extracting data from Grandperi to the 2 variables we just declared
FETCH NEXT from MyCursor to @O_ID, @A_Salary
--judging the state of the cursor
--0 FETCH statement succeeded
---1 FETCH statement failed or the row is not in the result set
---2 rows that are 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 cursor
Close MyCursor
--undo cursor
Deallocate mycursor

End of 2015 as a result of the project needs, learning the side of the Development project learning cursors and stored procedures is hereby recorded