To declare a scrollable cursor and fetch rows at random, specify the scroll sensitivity and scrollability in the declare cursor, then specify the fetch orientation at fetch time.
The following example demonstrates declaring an insensitive scrollable cursor and fetching rows at random:
exec sql declare c1 insensitive scroll cursor for
select title_id, royalty, ytd_sales from authors
where royalty < 25;
exec sql open c1;
In this example, scroll and insensitive are specified in the declare cursor. A fetch orientation can be specified at fetch time to indicate which row is required from the result set.
The following fetch example fetches the specified columns of the first row from the result set:
exec sql fetch first from c1 into :title,:roy,:sale;
The following fetch example fetches the specified columns of the previous row from the result set:
exec sql fetch prior from c1 into :title,:roy,:sale;
The following fetch example fetches the specified columns of row twenty from the result set:
exec sql fetch absolute 20 from c1 into :title,:roy,:sale;
Use sqlcode or sqlstate to determine if fetch statements return valid rows. For scrollable cursors, it is possible to fetch 0 rows if the cursor is positioned outside of result set boundaries, for example, before the first row or after the last row. In these circumstances, fetching 0 rows is not an error.
