Scroll cursor: A regular cursor can only move forward in query results in embedded SQL. However, a scroll cursor is used to provide random access of rows in query results. It is generally used for browsing database content. The only disadvantage of scroll cursor is that it increases the processing overhead.
A scroll cursor is declared as:
exec sql declare emplcur scroll cursor for
select name, avgsale, sale from employees where avgsale > sale order by name;
For using scroll cursor, extended version of FETCH is used having syntax:
FETCH extension FROM cursor_name INTO host_variable
Here, extension can be any one of:
1) FETCH FIRST: For retrieving first row of query results.
2) FETCH LAST: For retrieving last row of query results.
3) FETCH ABSOLUTE: For retrieving a particular row by row number.
4) FETCH RELATIVE: It is used for moving cursor a particular number of rows forwards or backwards relative to its current position.
5) FETCH PRIOR: For retrieving row of query results which immediately comes before the current row of cursor.
6) FETCH NEXT: For retrieving row of query results which immediately comes after the current row of cursor. (This is how a regular cursor works).