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:
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:
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.
Copyright © 2013 CA.
All rights reserved.
|
|