Using the Relational Data Framework › Cursor Operations › Statements Used in Cursor Operations

Statements Used in Cursor Operations

A cursor operation consists of these SQL statements, which you must invoke in the following order:

1. The DECLARE CURSOR statement. Before you start a cursor operation, you must invoke a DECLARE CURSOR statement. This special form of the SELECT statement defines a cursor to point to the rows you want to process. This cursor is a key that you pass to CA OPS/MVS in subsequent statements so that those statements act upon the proper rows. The CA OPS/MVS product holds this statement in reserve until you are ready to process the selected rows.
2. The OPEN statement. To start the cursor operation, you invoke the OPEN statement. This statement tells CA OPS/MVS to select the first of the rows specified on the DECLARE CURSOR statement.
3. The FETCH statement. Once you have opened the cursor, the FETCH statement fetches the values in the first selected row. Using the FETCH statement to access a row makes that row current. You can then invoke the UPDATE or DELETE FROM statement to modify or delete that row.

   CA OPS/MVS executes one FETCH statement for each row to be fetched, until it has processed all of the rows in your table or you end the cursor operation.

4. The UPDATE or DELETE FROM statement. The UPDATE and DELETE FROM statements work in cursor operations as they do when you invoke them alone, with one exception. When an UPDATE or DELETE FROM statement invoked as part of a cursor operation contains a WHERE clause, that clause instructs the CA OPS/MVS product to update or delete the currently selected row.
5. The CLOSE statement. This statement ends the cursor operation, telling CA OPS/MVS to stop processing rows. If you omit the CLOSE statement, the update or delete operation continues until CA OPS/MVS finishes processing all of the rows in the table. When CA OPS/MVS processes the last row, it sets the SQLCODE global variable to 100.

Example: Syntax for Cursor Operations

Because the statements that control cursor operations function in sets, the examples that follow show groups of statements you invoke together to update or delete rows one at a time.

Note: You may specify the text ADDRESS SQL on each statement, as shown in Example 2. However, since these statements should follow one another in the order shown, it is necessary to specify the text ADDRESS SQL only on the DECLARE CURSOR statement, as shown in the following example.

• To perform a cursor update operation, invoke this set of statements from an OPS/REXX program:

  ADDRESS SQL
    "DECLARE cursorname CURSOR FOR selectstatement"
    "OPEN cursorname"
    "FETCH cursorname INTO hostvarlist"
    "UPDATE tablename
      SET colname = string | :hostvar | NULL
      [, colname = string | :hostvar  | NULL]
      WHERE CURRENT OF cursorname"
    "CLOSE cursorname"
  

  For descriptions of the operands, see the text following Example 2.

• To perform a cursor delete operation, invoke this set of statements from an OPS/REXX program:

  ADDRESS SQL
    "DECLARE cursorname CURSOR FOR selectstatement"
  ADDRESS SQL
    "OPEN cursorname"
  ADDRESS SQL
    "FETCH cursorname INTO hostvarlist"
  ADDRESS SQL
    "DELETE FROM tablename WHERE CURRENT OF cursorname"
  ADDRESS SQL
    "CLOSE cursorname"
  

cursorname

This is the 1- to 18-character name of the cursor (pointer) that provides a common reference point for a set of related SQL cursor operation statements.

selectstatement

This is the text of a SELECT statement to execute when you invoke the OPEN statement. This SELECT statement should use the syntax described in SELECT Statement in this chapter.

hostvarlist

Used on the FETCH statement, the hostvarlist variable identifies a set of host variable names to store the selected column values. The order in which you specify REXX stem names should correspond to the order in which column names are specified on the DECLARE CURSOR statement.

tablename

This is the table containing the rows to be updated by the UPDATE statement or deleted with the DELETE FROM statement.