The cursor example in this discussion uses this query:
select author = au_fname + " " + au_lname, au_id from authors
The results of the query are:
author au_id ------------------------- ----------- Johnson White 172-32-1176 Marjorie Green 213-46-8915 Cheryl Carson 238-95-7766 Michael O’Leary 267-41-2394 Dick Straight 274-80-9391 Meander Smith 341-22-1782 Abraham Bennet 409-56-7008 Ann Dull 427-17-2319 Burt Gringlesby 472-27-2349 Chastity Locksley 486-29-1786 Morningstar Greene 527-72-3246 Reginald Blotchet Halls 648-92-1872 Akiko Yokomoto 672-71-3249 Innes del Castillo 712-45-1867 Michel DeFrance 722-51-5454 Dirk Stringer 724-08-9931 Stearns MacFeather 724-80-9391 Livia Karsen 756-30-7391 Sylvia Panteley 807-91-6654 Sheryl Hunter 846-92-7186 Heather McBadden 893-72-1158 Anne Ringer 899-46-2035 Albert Ringer 998-72-3567 (23 rows affected)
To use a cursor with the query above:
Declare the cursor.
This declare cursor statement defines a cursor using the select statement shown above:
declare newauthors_crsr cursor for select author = au_fname + " " + au_lname, au_id from authors for update
Open the cursor:
open newauthors_crsr
Fetch rows using the cursor:
fetch newauthors_crsr
author au_id ------------------------- ----------- Johnson White 172-32-1176 (1 row affected)
You can fetch more than one row at a time by specifying the number of rows with the set cursor rows command:
set cursor rows 5 for newauthors_crsr go fetch newauthors_crsr
author au_id ------------------------- ----------- Marjorie Green 213-46-8915 Cheryl Carson 238-95-7766 Michael O’Leary 267-41-2394 Dick Straight 274-80-9391 Meander Smith 341-22-1782 (5 rows affected)
Each subsequent fetch returns the next five rows:
fetch newauthors_crsr
author au_id ------------------------- ----------- Abraham Bennet 409-56-7008 Ann Dull 427-17-2319 Burt Gringlesby 472-27-2349 Chastity Locksley 486-29-1786 Morningstar Greene 527-72-3246 (5 rows affected)
The cursor is now positioned at author Morningstar Greene, the last row of the current fetch.
To change the first name of Greene, enter:
update authors set au_fname = "Voilet" where current of newauthors_crsr
The cursor remains at Ms. Greene’s record until the next fetch.
When you are finished with the cursor, close it:
close newauthors_crsr
If you open the cursor again, Adaptive Server re-runs the query and places the cursor before the first row in the result set. The cursor is still set to return five rows with each fetch.
To remove the cursor, use:
deallocate cursor newauthors_crsr
You cannot reuse a cursor name until you deallocate it.
