DECLARE CURSOR

DECLARE CURSOR (Short Name: DEC CUR)

Use the DECLARE CURSOR command to create a cursor that points to a row in a table or view.

Options

cursor
Specifies a 1 to 18 character cursor name.

CURSOR FOR SELECT clause
Specifies the columns and rows from the table whose values you want to use. You may include the DISTINCT modifier as well as where clauses and order by clauses.

SCROLL
Defines a cursor that moves forwards and backwards through a table. If this option is omitted, the cursor can only move forward.

About the DECLARE CURSOR Command

In the SELECT clause, specify the columns that contain the values you want to use from the row. Specifying the columns makes the column values accessible to the FETCH and SET VARIABLE commands. Once a cursor is declared, use the OPEN command to initialize the cursor and position it before the first row specified by DECLARE CURSOR.

Use DECLARE CURSOR to define a path through a table or view. You would move through the defined rows using the FETCH command by using either multiple FETCH commands or embedded FETCH commands within a WHILE loop. You only need to point to specific columns with DECLARE CURSOR, then FETCH can retrieve those columns by placing their values into variables. You can define a scrollable cursor, which is a cursor that moves backwards and forwards through a table.

DECLARE CURSOR defines a temporary view in memory; R:BASE does not store the view definition in the sys_views table. The SELECT clause defines columns, tables, rows, sort order, and potential grouping for the rows. When DECLARE CURSOR executes, it validates the syntax and names of columns and tables. The OPEN command can evaluate variables, create a copy of the cursor based on those values, then position the cursor before the first row.

Listing Cursors

Use LIST CURSOR to list all currently defined cursors and their status, open or closed.

Using Cursor Names in Commands

You can use the cursor name instead of a table name in commands. The following table provides examples of using the cursor name instead of a table name in commands.

To do this...	Use the cursor name like this...
Set a variable to a column value	FETCH cursor INTO varlist SET VARIABLE varname = colname WHERE CURRENT OF cursor
Change a column value to a constant	UPDATE tblname SET colname + = value WHERE CURRENT OF cursor
Change a column value to a variable value	UPDATE tblname SET colname + = .varname WHERE CURRENT OF cursor
Change a column value to an expression	UPDATE tblname SET colname + = (expression) WHERE CURRENT OF cursor
Delete the pointed-to row	DELETE FROM tblname + WHERE CURRENT OF cursor

Modifying Data Using a Cursor

If you use a cursor in commands that modify data (the UPDATE and DELETE commands), only the current row is modified. To modify all referenced rows, include FETCH in a WHILE loop to move the cursor through the rows.

Checking for End-of-Data Conditions

End-of-data conditions determine whether you have reached the end of the data declared with the DECLARE CURSOR command. The three ways to check for end-of-data conditions are:

Use an error variable defined with the SET ERROR VARIABLE command.

Use the sqlcode variable.

Include a WHENEVER NOT FOUND command.

Closing Cursors

The following commands close cursors.

Command Name	Description
CLOSE	Closes the open cursor but does not remove the cursor definition. However using CLOSE frees most of the memory used when a cursor is opened. CLOSE also frees any file handles used by DECLARE CURSOR.
COMMIT	Closes any open cursors
CONNECT	Removes any cursor definitions from memory
DISCONNECT	Removes any cursor definitions from memory
DROP CURSOR	Entirely removes the cursor definition. Dropping a cursor definition frees all memory used by the definition.
ROLLBACK	Closes any open cursors

Examples

The following example uses the SCROLL option with DECLARE CURSOR.

DECLARE c1 SCROLL CURSOR FOR SELECT empid, transid, transdate, custid, netamount FROM transmaster

Checking End-of-Data Conditions Using sqlcode

The following example shows the same type of processing as shown in the first example but uses sqlcode instead of an error variable to check end-of-data conditions. Using sqlcode is the recommended program structure for DECLARE CURSOR.

The two types of error processing differ. An error variable defined by SET ERROR VARIABLE holds the error value for any type of error, however, the sqlcode system variable holds values only for specific types of status.

Type of Error	sqlcode
Data found	0
Data not found	100

Other status code types   The negative value of the corresponding error message number.

In the following example, the WHILE statement checks the value of sqlcode.

1) DECLARE cursor1 CURSOR FOR SELECT custid, netamount +
     FROM transmaster ORDER BY netamount
2) OPEN cursor1
3) FETCH cursor1 INTO vcustid INDICATOR vi1, vnetamt +
     INDICATOR vi2
4) WHILE sqlcode <>100 THEN
     SHOW VARIABLE vcustid
     SHOW VARIABLE vnetamt
5)   FETCH cursor1 INTO vcustid INDICATOR vi1, vnetamt +
       INDICATOR vi2
   ENDWHILE
6) DROP CURSOR cursor1

1.	DECLARE CURSOR defines the cursor path.

2.	OPEN opens the cursor, evaluates variables, and positions the cursor before the first row.

3.	The first FETCH command retrieves the first set of values. The indicator variables vi1 and vi2 capture the status values, -1 for null and 0 for a value. If you omit indicator variables in FETCH commands, R:BASE displays a message if it encounters a null value, but continues processing rows.

4.	The WHILE loop processes the rows until there are no more rows. At that point, sqlcode is set to 100, and the WHILE loop ends. Control passes to the command after ENDWHILE. If the first FETCH retrieved no data, the WHILE loop is not entered.

5.	FETCH retrieves all succeeding rows and sets sqlcodeeach time. When it does not find any more data, sqlcode is set to 100 and the WHILE loop ends.

6.	DROP CURSOR removes the cursor definition from memory.

Using the WHENEVER Command with DECLARE CURSOR

The following example shows the use of the WHENEVER command, which checks the value of sqlcode. A single WHENEVER command can start a status-checking cycle that remains in operation until a command or procedure file finishes running. As in the first two examples, an indicator variable is included with each variable in FETCH. Without the indicator variables, R:BASE displays a message if it encounters a null value, but continues processing rows.

1) WHENEVER NOT FOUND GOTO skiploop
2) DECLARE cursor1 CURSOR FOR SELECT custid, netamount +
     FROM transmaster ORDER BY netamount
3) OPEN cursor1
4) FETCH cursor1 INTO vcustid INDICATOR vi1, vnetamt +
     INDICATOR vi2
5) WHILE #DATE IS NOT NULL THEN
     SHOW VARIABLE vcustid
     SHOW VARIABLE vnetamt
     FETCH cursor1 INTO vcustid INDICATOR vi1, vnetamt +
     INDICATOR vi2
   ENDWHILE
6) LABEL skiploop
7) DROP CURSOR cursor1

WHENEVER NOT FOUND tells R:BASE to execute GOTO if a command that searches for data, such as FETCH, cannot find more rows. If the first FETCH command does not find any rows, control passes to the command following LABEL skiploop. WHENEVER automatically checks any command that searches for data. If a data-not-found condition occurs, control passes to the command following the specified label.

2.	DECLARE CURSOR defines the cursor path.

3.	OPEN opens the cursor, evaluates the variables, and positions the cursor before the first row.

The first FETCH command retrieves the first set of values. If no rows match, control passes to LABEL skiploop. Indicator variables vi1 and vi2 capture the status values (-1 for null and 0 for a value). If you omit indicator variables in FETCH commands, R:BASE displays a message if it encounters a null value, but continues processing rows. (WHENEVER instructs R:BASE to exit the WHILE loop only when sqlcode is 100.)

5.	The WHILE loop processes rows until WHENEVER stops execution.

6.	This label defines where to pass control if a data-not-found condition occurs before the WHILE loop begins executing. WHENEVER includes this label name.

7.	DROP CURSOR removes the cursor definition from memory.