Use the FETCH command to position the cursor on a row specified by the DECLARE CURSOR command, and place values from the columns into global variables.
Options
ABS n
The value n is the nth row in the cursor list. The current cursor location is not relevant. Positive numbers count from the first row in the list. Negative numbers force an end-of-data condition. This option applies only to scrolling cursors, which are defined with the DECLARE CURSOR command.
cursorname INTO
Names the cursor from which to fetch data into the specified variable list.
FIRST
Specifies the first row in the cursor list. This option applies only to scrolling cursors, which are defined with the DECLARE CURSOR command.
INDICATOR ind_var
Stores the status of the variable: non-null (0) or null (-1).
LAST
Specifies the last row in the cursor list. This option applies only to scrolling cursors, which are defined with the DECLARE CURSOR command.
NEXT
Specifies the next entry the cursor points to. This option applies only to scrolling cursors, which are defined with the DECLARE CURSOR command.
PRIOR
Specifies the prior entry the cursor points to. This option applies only to scrolling cursors, which are defined with the DECLARE CURSOR command.
REL n
Moves the cursor n rows. Positive integers move forward, and negative integers move backwards. For example, if n is 5, the cursor moves forward 5 rows. This option applies only to scrolling cursors, which are defined with the DECLARE CURSOR command.
varname
Specifies a variable name, which must be unique among the variable names within the database. The variable name is limited to 128 characters.
About the FETCH Command
FETCH moves the cursor to the next available row referred to by the DECLARE CURSOR command and also accommodates scrollable cursors specified by DECLARE CURSOR. FETCH retrieves the values of columns in the order in which the columns were specified by DECLARE CURSOR. The LIST CURSORS command lists all the defined cursors.
FETCH cursorname without any variable specification will retrieve the next row from the cursor. Use the SET VAR varname WHERE CURRENT OF cursorname to retrieve the columns you need.
Using the Sqlcode Variable
You must check the sqlcode variable with each use of FETCH to verify that all rows specified by DECLARE CURSOR have been found. Use the WHENEVER sqlcode command to check for SQL processing errors other than data-not-found errors.
Use WHENEVER NOT FOUND to check for a data-not-found errors. When you use the WHENEVER NOT FOUND command, data-not-found error checking is automatic; however, you must use the LABEL command. When a data-not-found error occurs, control passes to the command line specified by the LABEL command and the subsequent error-handling commands.
Using Indicator Variables
If the data contains null values, use indicator variables to capture the status of a value. If you do not use indicator variables, R:BASE displays an error message when it encounters a null value, but produces no rows.
Placing a Value into a Numeric Variable
If you use FETCH to place a value into a variable that has not been previously defined and has a NUMERIC data type, then that variable acquires the precision and scale of the column from which the value is fetched.
Using the FETCH Command Without Variable Specification
Using FETCH cursorname without any variable specification will retrieve the next row from the cursor. Use the SET VAR varname WHERE CURRENT OF cursorname to retrieve the columns you need.
Example
The following command lines fetch every other row from a table.
DROP CURSOR C1
DECLARE c1 SCROLL CURSOR FOR SELECT transid, transdate, +
netamount FROM transmaster ORDER BY netamount DESC
OPEN c1
FETCH c1 INTO vtransid ind1, vtransdate ind2, vnetamount ind3
SELECT COUNT(*) INTO vtotcount i1 FROM transmaster
SET VAR vcount INT=0
WHILE sqlcode <> 100 THEN
SET VAR vcount = (.vcount+1)
WRITE 'Total count', .vtotcount, 'Cursor count', .vcount
--fetch every other row
FETCH REL 2 FROM c1 INTO vtransid int1, +
vtransdate ind2, vnetamount ind3
ENDWHILE
CLOSE C1
DROP CURSOR C1
