The use of cursors in SQL

Generally, when we use SELECT query statements, they are all for one row of records.
If you want to read multiple rows of records (i.e., recordset) in the query analyzer, you need to use a cursor Or WHILE and other loops

Types of cursors:
1. Static cursor (does not detect changes in data rows)
2. Dynamic cursor (reflects changes in all data rows)
3. Forward-only cursor (does not support scrolling)
4. Keyset cursor (can reflect modifications, but cannot accurately reflect insertions and deletions)

The order of cursor use:
1. Define the cursor
2. Open the cursor
3. Use the cursor
4. Close the cursor
5. Release 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 forward only SCROLL scroll
STATIC static KEYSET key set DYNAMIC dynamic
READ_ONLY read-only SCROLL_LOCKS lock the current row of the cursor

get the cursor data
FETCH PRI [[NEXT | OR | FIRST | LAST |
ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]
From ] Cursor name [into variable]
Note:
NEXT Next line PRIOR Previous line FIRST First line
LAST Last line ABSOLUTE n Line n
RELATIVE n The nth row starting from the current position
into variable Assign each field value of the current row to the variable

Cursor status variable:
@@fetch_status Cursor status
Number of rows
                 N Number of rows - 1 The cursor is dynamic 0 Empty set cursor
Current row of the cursor:
current of Cursor name

Example 1: Use the cursor to traverse the queried data set

use pubs

go

declare @auid char(12),@aulname varchar(20),@aufname varchar(20), @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 into @auid,@aulname,@aufname, @st

while (@@fetch_status=0)

begin

print 'Author number: ' +@auid

print 'Author's name: '+@aulname+','+@aufname

print 'State: '+@st

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

fetch next from auth_cur into @auid,@aulname,@aufname, @st

end

close auth_cur

deallocate auth_cur

Example 2: Use cursor to modify, Delete data

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 --Set the row identified by the variable @rowcount as the current row

--The following line is modified using the cursor

update authors set au_lname='Zhang' where current of auth_cur --Modify the current row in the cursor

--The following line is to use the cursor to perform a delete operation

delete from authors where current of auth_cur