Examples of scrollable and forward-only cursors are provided.
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)
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, 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.
To remove the cursor, use:
deallocate cursor newauthors_crsr
You cannot reuse a cursor name until you deallocate it.
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 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.
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.