Forward-only (default) cursors

The following cursor example 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:

  1. 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
    
  2. Open the cursor:

    open newauthors_crsr
    
  3. 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.

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

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

  6. Use the deallocate command to remove the cursor:

    deallocate cursor newauthors_crsr
    

    You cannot reuse a cursor name until you deallocate it.