Data Manipulation with SQL › Using a Cursor › Executing a Positioned Update or Delete

Executing a Positioned Update or Delete

A positioned update modifies one or more column values of the current row of an updateable cursor. The statement takes this form:

EXEC SQL
UPDATE table-name
  SET column-name = value-specification
  ...
  WHERE CURRENT OF cursor-name
END-EXEC.

Requirements for a Positioned Update

To execute a positioned update, the program must declare a cursor that:

• Is updateable
• Contains a FOR UPDATE OF clause

Advantage of an Updateable Cursor

When the database transaction running under cursor stability fetches a row from an updateable cursor, the DBMS places a lock on the row and maintains it until one of these events occurs:

• The program fetches the next cursor row
• The cursor is closed
• The database transaction ends

In this way, CA IDMS guarantees the base row is not modified or deleted by another transaction while it is the current cursor row.

The DBMS maintains the lock on the current row of an updateable cursor during a suspended SQL session. This feature is designed for pseudoconversational programming.

Note: For more information about pseudoconversational programming with embedded SQL, see 7.2, Pseudoconversational Programming.

Checking Positioned Update Status

If the program attempts to execute a positioned update when the referenced cursor is not updateable or does not contain a FOR UPDATE OF clause, the DBMS returns an invalid cursor state error.

Note: For more information about checking the status of UPDATE statements in general, see Modifying Data.

Positioned Update Example

In the following example, the program declares a cursor to retrieve current data for vacation and sick days taken by employees. The program adds input values to the values retrieved for the employee in the current cursor row. Then the program issues a positioned update.

 EXEC SQL

   DECLARE VAC_SICK_CURSOR CURSOR FOR
      SELECT EMP_ID,
             VAC_TAKEN,
             SICK_TAKEN
        FROM BENEFITS
        FOR UPDATE OF VAC_TAKEN,
                      SICK_TAKEN
 END-EXEC.
  .
  .
  .
 EXEC SQL
   OPEN VAC_SICK_CURSOR
 END-EXEC.
  .
  .
  .
 EXEC SQL
   FETCH VAC_SICK_CURSOR INTO
     :EMP-ID,
     :VAC-TAKEN INDICATOR VAC-TAKEN-I,
     :SICK-TAKEN INDICATOR SICK-TAKEN-I
 END-EXEC.
  .
  .
  .
 ADD INPUT-VAC-TAKEN TO VAC-TAKEN
 ADD INPUT-SICK-TAKEN TO SICK-TAKEN
  .
  .
  .
 EXEC SQL
   UPDATE BENEFITS
     SET VAC_TAKEN = :VAC-TAKEN,
         SICK_TAKEN = :SICK-TAKEN
     WHERE CURRENT OF VAC-SICK-CURSOR
 END-EXEC.
  .
  .
  .
 EXEC SQL
    CLOSE VAC_SICK_CURSOR
 END-EXEC.

Positioned Deletes

You can delete the current row of an updateable cursor simply by naming the table and the cursor in the DELETE statement:

DELETE FROM table-name WHERE CURRENT OF cursor-name

A cursor must be updateable to perform a positioned delete, but the FOR UPDATE OF clause is not required in the cursor declaration.

Checking Positioned Delete Status

If the program attempts to execute a positioned delete when the referenced cursor is not updateable, the DBMS returns an invalid cursor state error.

Note: For more information about checking the status of DELETE statements in general, see Deleting Data.

Positioned Delete Example

In this example, the program declares an updateable cursor. After fetching a row, the program conditionally executes a positioned delete.

 EXEC SQL

   DECLARE DEL_POSITION CURSOR FOR
      SELECT EMP_ID,
             JOB_ID
        FROM POSITION
 END-EXEC.
  .
  .
  .
 EXEC SQL
   OPEN DEL_POSITION
 END-EXEC.
  .
  .
  .
   EXEC SQL
     FETCH DEL_POSITION INTO
       :EMP-ID,
       :JOB-ID
   END-EXEC.
  .
  .
  .
 IF INPUT-ACTION = 'D&rq.
 EXEC SQL
   DELETE FROM POSITION
     WHERE CURRENT OF DEL_POSITION
 END-EXEC.
  .
  .
  .
 EXEC SQL
    CLOSE DEL_POSITION
 END-EXEC.