Scrolling through a table in any direction
Use a scrollable cursor to move through the table in both a forward and a backward direction.
About this task
Question: How can I fetch rows from a table in any direction?
Answer: Declare your cursor as scrollable. When you select rows from the table, you can use the various forms of the FETCH statement to move to an absolute row number, move ahead or back a certain number of rows, to the first or last row, before the first row or after the last row, forward, or backward. You can use any combination of these FETCH statements to change direction repeatedly.
You can use code like the following example to
move forward in the department table by 10 records, backward five
records, and forward again by three records:
/**************************/
/* Declare host variables */
/**************************/
EXEC SQL BEGIN DECLARE SECTION;
char[37] hv_deptname;
EXEC SQL END DECLARE SECTION;
/**********************************************************/
/* Declare scrollable cursor to retrieve department names */
/**********************************************************/
EXEC SQL DECLARE C1 SCROLL CURSOR FOR
SELECT DEPTNAME FROM DSN8D10.DEPT;
⋮
/**********************************************************/
/* Open the cursor and position it before the start of */
/* the result table. */
/**********************************************************/
EXEC SQL OPEN C1;
EXEC SQL FETCH BEFORE FROM C1;
/**********************************************************/
/* Fetch first 10 rows */
/**********************************************************/
for(i=0;i<10;i++)
{
EXEC SQL FETCH NEXT FROM C1 INTO :hv_deptname;
}
/**********************************************************/
/* Save the value in the tenth row */
/**********************************************************/
tenth_row=hv_deptname;
/**********************************************************/
/* Fetch backward 5 rows */
/**********************************************************/
for(i=0;i<5;i++)
{
EXEC SQL FETCH PRIOR FROM C1 INTO :hv_deptname;
}
/**********************************************************/
/* Save the value in the fifth row */
/**********************************************************/
fifth_row=hv_deptname;
/**********************************************************/
/* Fetch forward 3 rows */
/**********************************************************/
for(i=0;i<3;i++)
{
EXEC SQL FETCH NEXT FROM C1 INTO :hv_deptname;
}
/**********************************************************/
/* Save the value in the eighth row */
/**********************************************************/
eighth_row=hv_deptname;
/**********************************************************/
/* Close the cursor */
/**********************************************************/
EXEC SQL CLOSE C1;