Use of SQL Midstream (RPM)

In general, when we use select these query statements, it is for a row of records,
If you want to read multiple rows of records (that is, recordsets) in Query Analyzer, you need to use loops such as cursors or while

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

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 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

Turn: The use of fruit SQL midstream label

Use of SQL Midstream (RPM)