Use the DECLARE CURSOR command to create a cursor that points to a row in a table or view.
Options
cursorname
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 can 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 cursorname INTO varlist SET VARIABLE varname = colname + WHERE CURRENT OF cursorname |
Change a column value to a constant |
UPDATE tblname SET colname = value + WHERE CURRENT OF cursorname |
Change a column value to a variable value |
UPDATE tblname SET colname = .varname + WHERE CURRENT OF cursorname |
Change a column value to an expression |
UPDATE tblname SET colname = (expression) + WHERE CURRENT OF cursorname |
Delete the pointed-to row |
DELETE FROM tblname + WHERE CURRENT OF cursorname |
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 |
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. |
|
Closes any open cursors |
|
Removes any cursor definitions from memory |
|
Removes any cursor definitions from memory |
|
Entirely removes the cursor definition. Dropping a cursor definition frees all memory used by the definition. |
|
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 uses sqlcode to check end-of-data conditions, which is the recommended program structure for DECLARE CURSOR. The sqlcode system variable holds values only for specific types of status.
Type of Error |
SQLCODE |
Data found |
0 |
Data not found |
100 |
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
| 1. | 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. |
| 4. | 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. |
Visit the From The Edge Web site to download the "R:BASE Cursors Explained" technical document.
