Scroll-able cursor is a new topic. From DB2 Version 7, the concept of Scrollable cursor available. Usually, after getting query result into Temporary table, developers apply this cursor to validate data.
Note-where as in static cursor you cannot search the resultant table back and forth. This is just sequential search.
DECLARE GLOBAL TEMPORARAY TABLE TEMP_PROJ LIKE DSN8810.PROJ INCLUDING IDENTITY ON COMMIT PRESERVE ROWS;
So the mechanism of scrollable cursor works in temporary table. It will be stored in SYSIBM.SYSTABLES. These are not persistent , locking, logging and recovery do not apply.
Indexes can not created. Constraints can not created. Can not be referenced by Utilities. When delete we need to delete all the rows.
Some of the available options:
- NEXT – will FETCH the next row, the same way that the pre-V7
- FETCH statement functioned
- PRIOR – will FETCH the previous row
- FIRST – will FETCH the first row in the results set
- LAST – will FETCH the last row in the results set
- CURRENT – will re-FETCH the current row from the result set
- BEFORE – positions the cursor before the first row of the results set
- AFTER – positions the cursor after the last row of the results set
- ABSOLUTE n – will FETCH the row that is n rows away from the first row in the results set
- RELATIVE n – will FETCH the row that is n rows away from the last row fetched
DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR FOR SELECT FIRSTNAME, LASTNME FROM DSN8710.EMP ORDER BY LASTNME FETCH FIRST 1000 ROWS ONLY; OPEN csr1; FETCH ABSOLUTE 200 csr1 INTO :FN, :LN;
If I want to fetch rows between 200 and 500
FETCH 200 ROW FETCH NEXT 300 TIMES
Basically, scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries.