cursors cursor and loop fetch

Part of this article is not original

Examples of Use:

DECLARE mycursor cursor
For
Select Id,[column1],[num] from Table1

Open mycursor;
declare @ID int, @Column1 varchar, @Num int
FETCH NEXT from MyCursor to @ID, @Column1, @Num;
While @ @FETCH_STATUS = 0
Begin

--do something

FETCH NEXT from MyCursor to @ID, @Column1, @Num;
End
Close MyCursor
Deallocate mycursor

The records that are checked by the SELECT statement are called result sets.
In some applications, the result set cannot be treated as a processing unit,
It requires a processor to be processed in batches, and a cursor is needed to control it.

1. Declaring a cursor:
It is used primarily to store procedures, triggers, and statements, and to process the result set on a row-by-line basis.

Syntax: DECLARE CURSOR name insensitive scroll cursor
For SELECT statement
for [(Read only)/update of [field name ...]]

Note: When specifying updatable fields with {Update of}, specify the referenced
The cursor name is in {WHERE CURRENT of cursor name}, and when {Read Only} is used,
The data in the cursor is read-only.

Example: Declare aa_cursor cursor FOR SELECT * FROM AA
where address = ' Dalian '
For update of name, phone
Open Aa_cursor
FETCH NEXT from Aa_cursor
Update AA Set name = ' Stray bullet ' where current of aa_cursor
Close Aa_cursor
Deallocate aa_cursor

2. Open the declared cursor:

opens with open.

Syntax: Open cursor name

Note: After opening, you can use to determine if the open operation is successful, if 0,
Open successfully, open with the parameterless function to get the cursor,
There are four values for the number of records currently present:
N: The cursor defines the data that has been completely read from the table.
n is the entire data row.
-M: The data defined by the cursor is not fully read from the table.
M is the row of data within the current cursor data subset.
0: No qualifying data or the cursor has been closed or disposed.
-1: The cursor is dynamic, and the data rows are not determined by regular changes.

Example: Declare aa_cursor cursor keyset for SELECT * from AA
Open Aa_cursor
If @ @error =0
Begin
print ' Cursor open successfully '
If @ @cursor_rows >0
The number of records in the print ' cursor result set is: ' +convert (varchar (3), @ @cursor_rows)
End
Close Aa_cursor
Deallocate aa_cursor

Note: Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened.
A keyset that uniquely identifies a row is built into tempdb within one of the named keyset
In the table. Changes made to non-key values in the base table (changed by the cursor owner or
Submitted by another user) is visible when the user scrolls the cursor. Other users to
is not considered (cannot be performed through a Transact-SQL Server cursor
Insert). If a row has been deleted, the fetch operation on that row returns
@ @FETCH_STATUS Value-2. Updating a key value from outside a cursor is similar to deleting an old row
The action of inserting a new row is followed. Rows with new values are not considered, for rows that contain old values
The fetch operation returns the @ @FETCH_STATUS value-2. If you specify by specifying
The WHERE current OF clause is updated with a cursor, and the new value is visible.

3. Extract the data from the cursor:
After you declare a cursor and open it successfully, you can use fetch to extract the data from the cursor.
Syntax: Fetch (First/last/prior/next/
absolute[n/@nvar]/relative[n/@nvar])
from cursor name [into] quantity name [... n]

cursors cursor and loop fetch