Usage of SQL midstream mark

In general, when we use the select statements, they are all for a row of records,
If you want to read a multi-row record (that is, a record set) in the query analyzer, you need to use a cursor or a while loop.

Cursor type:
1. Static cursor (does not detect changes in data rows)
2. Dynamic Cursor (reflecting changes to all data rows)
3. Forward cursor only (scroll is not supported)
4. Key set cursor (can reflect modifications, but cannot accurately reflect insertion or deletion)

Order of cursor usage:
1. Define a cursor
2. Open the cursor
3. Use a cursor
4. Close the cursor
5. Release cursor

Transact-SQL:
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 scroll forward scroll
Static keyset
Read_only read-only scroll_locks lock the current row of the cursor

Obtain cursor data
Fetch [[next | prior | first | last |
Absolute {n | @ nvar | relative {n | @ nvar}]
From] cursor name [into variable]
Note:
Next next line prior last line first line
Last last line absolute N
Line n starting from the current position of relative n
The into variable assigns values of fields in the current row to the variable.

Cursor status variable:
@ Fetch_status cursor status
0 success-1 failure-2 loss
@ Cursor_rows the number of rows in the result set in the cursor
N rows-1 cursor is a dynamic 0 empty set cursor
The current row of the Operation cursor:
Current of cursor name

Example 1: Use a cursor to traverse the queried Dataset

Use pubs

Go

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

Declare auth_cur cursor

Select au_id, au_lname, au_fname, state

From authors

Open auth_cur

Fetch next from auth_cur into @ auid, @ aulname, @ aufname, @ St

While (@ fetch_status = 0)

Begin

Print 'author No.: '+ @ auid

Print 'author name: '+ @ aulname +', '+ @ aufname

Print 'region: '+ @ St

Print '--------------------------'

Fetch next from auth_cur into @ auid, @ aulname, @ aufname, @ St

End

Close auth_cur

Deallocate auth_cur

Example 2: Use a cursor to modify or delete data

Declare auth_cur cursor scroll

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 -- set the row marked by the variable @ rowcount to the current row

-- The following line uses the cursor to modify

Update authors set au_lname = 'zhang 'where current of auth_cur -- modify the current row in the cursor

-- The following row deletes data using a cursor.

Delete from authors where current of auth_cur