declare cursor examples

The following declare cursor statement defines a result set for the authors_crsr cursor that contains all authors that do not reside in California:

declare authors_crsr cursor
for select au_id, au_lname, au_fname
from authors
where state != 'CA'
for update

The following example defines an insensitive scrollable result set, of the stores_scrollcrsr, containing bookstores in California, for:

declare storinfo_crsr insensitive scroll cursor
for select stor_id, stor_name, payterms
from stores
where state = "CA"

For the syntax of declare cursor, see “declare cursor syntax”.S

To declare an insensitive, nonscrollable cursor called “C1,” enter:

declare C1 insensitive cursor for
select fname from emp_tab

To declare an insensitive, scrollable cursor called “C3,”enter:

declare C3 insensitive scroll cursor for
select fname from emp_tab

To fetch the first row, enter:

fetch first from <cursor_name>

You can also fetch the columns of the first row from the result set. To place them in the variables you specify in <fetch_target_list>, enter:

fetch first from <cursor_name> into <fetch_target_list>

You can fetch the 20th row in the result set directly, regardless of the cursor’s current position:

fetch absolute 20 from <cursor_name> into <fetch_target_list>