Cursor input modes

For purposes of input, a cursor operates in one of two modes, sequential or scrolling. A sequential cursor can fetch only the next row in sequence, so a sequential cursor can read through a table only once each time the cursor is opened. A scroll cursor can fetch the next row or any of the output rows, so a scroll cursor can read the same rows multiple times. The following example shows a sequential cursor declared in .

EXEC SQL DECLARE pcurs cursor for
   SELECT customer_num, lname, city
      FROM customer;

After the cursor is opened, it can be used only with a sequential fetch that retrieves the next row of data, as the following example shows:

EXEC SQL FETCH p_curs into:cnum, :clname, :ccity;

Each sequential fetch returns a new row.

A scroll cursor is declared with the keywords SCROLL CURSOR, as the following example from shows:

EXEC SQL DECLARE s_curs SCROLL CURSOR FOR
      SELECT order_num, order_date FROM orders
         WHERE customer_num > 104

Use the scroll cursor with a variety of fetch options. For example, the ABSOLUTE option specifies the absolute row position of the row to fetch.

EXEC SQL FETCH ABSOLUTE :numrow s_curs
     INTO :nordr, :nodat

This statement fetches the row whose position is given in the host variable numrow. You can also fetch the current row again, or you can fetch the first row and then scan through all the rows again. However, these features can cause the application to run more slowly, as the next section describes. For additional options that apply to scroll cursors, see the FETCH statement in the HCL OneDB™ Guide to SQL: Syntax.