Definition and use of SQL Midstream label

Type of cursor:
1, static cursors (do not detect changes in data rows)
2. Dynamic cursor (reflects all data row changes)
3. Forward-only cursors (does not support scrolling)
4, keyset cursor (can reflect the changes, but can not accurately reflect the insertion, deletion)

Cursor Use order:
1. Defining cursors
2. Open cursor
3. Using Cursors
4. Close the cursor
5. Releasing the cursor

Grammar

DECLARE cursor name CURSOR [LOCAL | global][forward_only | scroll][static | KEYSET | DYNAMIC] [Read_Only | Scroll_locks]
For Selet statement [for update[of column name [, column name]]
Note: Local local cursor global global cursor
Forward_only only forward SCROLL scrolling
Static KEYSET Key Set dynamic
READ_ONLY read-only scroll_locks lock cursor when moving forward

Get data for a cursor
FETCH [[NEXT | PRIOR | First | Last |
absolute{N | @nvar | RELATIVE {n | @nvar}]
From] cursor name [into variable]
Note:
Next line PRIOR Previous line first row
Last line ABSOLUTE n n Nth Row
RELATIVE n the nth row at the beginning of the current position
into variable assigns the value of each field in the current row to the variable

Cursor State variables:
@ @fetch_status Cursor State
0 successes-1 failures-2 lost
@ @cursor_rows The number of rows in the result set in the cursor
n Rows-1 cursor is a dynamic 0 empty set cursor
To manipulate the current line of the cursor:
Current of cursor name

Example 1: Iterating through a query's dataset with a cursor

Use pubs

Go

Declare @auid char, @aulname varchar, @aufname varchar, @st char (2), @auinfo varchar (50)

DECLARE auth_cur cursor FOR

Select au_id, au_lname, au_fname, state

From authors

Open Auth_cur

FETCH NEXT from Auth_cur to @auid, @aulname, @aufname, @st

while (@ @fetch_status =0)

Begin

print ' Author number: ' [email protected]

print ' Author name: ' [email protected]+ ', ' [email protected]

print ' state: ' [email protected]

print '--------------------------'

FETCH NEXT from Auth_cur to @auid, @aulname, @aufname, @st

End

Close Auth_cur

Deallocate auth_cur

Example 2: Modifying and deleting data using cursors

Declare auth_cur cursor Scroll for

Select au_id, au_lname, au_fname, state

From authors for update of au_lname

Open Auth_cur

DECLARE @rowcount int

Set @rowcount = 6

Fetch absolute @rowcount from Auth_cur--sets the row identified by the variable @rowcount as the forward

--The following 1 lines are modified using cursors

Update authors set Au_lname= ' WHERE current of auth_cur--modifies the forward in the cursor

--The following 1 lines are used to delete operations using cursors

Delete from authors where current of auth_cur

Definition and use of SQL Midstream label