Cursor Examples

Examples of scrollable and forward-only cursors are provided.

Forward-Only (Default) Cursors

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, SAP ASE 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. To remove the cursor, use:

    deallocate cursor newauthors_crsr

    You cannot reuse a cursor name until you deallocate it.

Insensitive Scrollable Cursors

When you declare and open an insensitive cursor, a worktable is created and fully populated with the cursor result set. Locks on the base table are released, and only the worktable is used for fetching.

To declare cursor CI as an insensitive cursor, enter:

declare CI insensitive scroll cursor for
select emp_id, fname, lname
from emp_tb
where emp_id > 2002000

open CI

To change the name “Sam” to “Joe,” enter:

.....
update emp_tab set fname = "Joe"
where fname = "Sam"
Now four “Sam” rows in the base table emp_tab disappear, replaced by four “Joe” rows.
fetch absolute 2 CI

The cursor reads the second row from the cursor result set, and returns Row 2, “2002020, Sam, Clarac.”

This next command inserts one more qualified row (that is, a row that meets the query condition in declare cursor) into table emp_tab, but the row membership is fixed in a cursor, so the added row is invisible to cursor CI. Enter:

insert into emp_tab values (2002101, "Sophie", "Chen", .., ..., ...)

The following fetch command scrolls the cursor to the end of the worktable, and reads the last row in the result set, returning the row value “2002100, Sam, West.” Again, because the cursor is insensitive, the new row inserted in emp_tab is invisible in cursor CI’s result set.

fetch last CI

Semisensitive Scrollable Cursors

Semisensitive scrollable cursors are like insensitive cursors in that they use a worktable to hold the result set for scrolling purposes.

But in semi_sensitive mode, the cursor’s worktable materializes as the rows are fetched, rather than when you open the cursor. The membership of the result set is fixed only after all the rows have been fetched once.

To declare cursor CSI semisensitive and scrollable, enter:

declare CSI semi_sensitive scroll cursor for
select emp_id, fname, lname
from emp_tab
where emp_id > 2002000

open CSI

Because the cursor is semisensitive, none of the rows are copied to the worktable when you open the cursor. To fetch the first record, enter:

fetch first CSI

The cursor reads the first row from emp_tab and returns 2002010, Mari, Cazalis. This row is copied to the worktable. Fetch the next row by entering:

fetch next CSI

The cursor reads the second row from emp_tab and returns 2002020, Sam, Clarac. This row is copied to the worktable. To replace the name “Sam” with the name “Joe,” enter:

......
update emp_tab set fname = "Joe"
where fname = "Sam"

The four “Sam” rows in the base table emp_tab disappear, and four “Joe” rows appear instead. To fetch only the second row, enter:

fetch absolute 2 CSI

The cursor reads the second row from the result set and returns employee ID 2002020, but the value of the returned row is “Sam,” not “Joe.” Because the cursor is semisensitive, this row was copied into the worktable before the row was updated, and the data change made by the update statement is invisible to the cursor, since the row returned comes from the result set scrolling worktable.

To fetch the fourth row, enter:

fetch absolute 4 CSI

The cursor reads the fourth row from the result set. Since Row 4, (2002040, Sam, Burke) was fetched after “Sam” was updated to “Joe,” the returned employee ID 2002040 is Joe, Burke. The third and fourth rows are now copied to the worktable.

To add a new row, enter:

insert into emp_tab values (2002101, "Sophie", "Chen", .., ..., ...)

One more qualified row is added in the result set. This row is visible in the following fetch statement, because the cursor is semisensitive and because we have not yet fetched the last row. Fetch the updated version by entering:

fetch last CSI

The fetch statement reads 2002101, Sophie, Chen in the result set.

After using fetch with the last option, you have copied all the qualified rows of the cursor CSI to the worktable. Locking on the base table, emp_tab, is released, and the result set of cursor CSI is fixed. Any further data changes in emp_tab do not affect the result set of CSI.

Note: Locking schema and transaction isolation level also affect cursor visibility. The above example is based on the default isolation level, level 1.

Table for Scrollable Cursors

select emp_id, fname, lname
from emp_tab
where emp_id > 2002000

The base table, emp_tab, is a datarows-locking table with a clustered index on the emp_id field. “Row position” is an imaginary column, in which the values represent the position of each row in the result set. The result set in this table is used in the examples in the following sections, which illustrate both insensitive and semisensitive cursors.

Row Position

emp_id

fname

lname

1

2002010

Mari

Cazalis

2

2002020

Sam

Clarac

3

2002030

Bill

Darby

4

2002040

Sam

Burke

5

2002050

Mary

Armand

6

2002060

Mickey

Phelan

7

2002070

Sam

Fife

8

2002080

Wanda

Wolfe

9

2002090

Nina

Howe

10

2002100

Sam

West