FETCH (result set cursor)

Moves a cursor to a new row in the corresponding result set and retrieves the row values into fetch buffers.

Syntax

FETCH [ direction ] cid
   [ INTO fvar [,...] ]

where direction is one of:

{
  NEXT
| { PREVIOUS | PRIOR }
| CURRENT
| FIRST
| LAST
| ABSOLUTE position
| RELATIVE offset
}

cid is the identifier of the database cursor.
fvar is a variable used as fetch buffer.
direction options different from NEXT can only be used with scrollable cursors.
position is an positive integer expression.
offset is a positive or negative integer expression.

Usage

The FETCH instruction retrieves a row from a result set of an opened cursor. The cursor must be opened before using the FETCH instruction.

The INTO clause can be used to provide the fetch buffers that receive the result set column values.

A sequential cursor can fetch only the next row in sequence from the result set.

The NEXT clause (the default) retrieves the next row in the result set. If the row pointer was on the last row before executing the instruction, the SQL code is set to 100 (NOTFOUND), and the row pointer remains on the last row. (if you issue a FETCH PREVIOUS at this time, you get the next-to-last row).

The PREVIOUS clause retrieves the previous row in the result set. If the row pointer was on the first row before executing the instruction, the SQL code is set to 100 (NOTFOUND), and the row pointer remains on the first row. (if you issue a FETCH NEXT at this time, you get the second row).

The CURRENT clause retrieves the current row in the result set.

The FIRST clause retrieves the first row in the result set.

The LAST clause retrieves the last row in the result set.

The ABSOLUTE clause retrieves the row at position in the result set. If the position is not correct, the SQL code is set to 100 (NOTFOUND). Absolute row positions are numbered from 1.

The RELATIVE clause moves offset rows in the result set and returns the row at the current position. The offset can be a negative value. If the offset is not correct, the SQL code is set to 100 (NOTFOUND). If offset is zero, the current row is fetched.

Fetching rows can have specific behavior when the cursor was declared FOR UPDATE to perform a positioned update or delete.

MAIN
   DEFINE cust_rec RECORD
            cnum INTEGER,
            cname CHAR(20)
       END RECORD
   DATABASE stores 
   DECLARE c1 SCROLL CURSOR FOR SELECT customer_num, cust_name FROM customer 
   OPEN c1
   FETCH c1 INTO cust_rec.*
   FETCH LAST c1 INTO cust_rec.* 
   FETCH PREVIOUS c1 INTO  cust_rec.* 
   FETCH FIRST c1 INTO cust_rec.*
   FETCH LAST c1 -- INTO clause is optional
   FETCH FIRST c1 -- INTO clause is optional
END MAIN