Cursor with UPDATE

A cursor can also update rows. You declare it with the FOR UPDATE OF clause and then use UPDATE ... WHERE CURRENT OF to change the row the cursor is currently positioned on.

Declaring a cursor for update

• Add FOR UPDATE OF column-list at the end of the SELECT in the DECLARE.
• Example: DECLARE EMPCUR CURSOR FOR SELECT EMPNO, EMPNAME, DEPTNO, SALARY FROM EMP FOR UPDATE OF SALARY.
• Only the columns named in FOR UPDATE OF can be updated through this cursor - updating any other column gives SQLCODE -503.
• An updateable cursor's SELECT is restricted: one table only, no DISTINCT, no GROUP BY, no ORDER BY, no joins, no subqueries in most cases.
• FOR UPDATE (without a column list) also works and allows updating any updatable column of the single table.

Updating the current row - full example

• The program fetches each row, computes the new salary, and updates the row the cursor is positioned on.
• Example:
  
  WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. 01 WS-EMPNO PIC S9(9) COMP. 01 WS-EMPNAME PIC X(20). 01 WS-DEPTNO PIC S9(4) COMP. 01 WS-SALARY PIC S9(7)V99 COMP-3. 01 WS-NEW-SAL PIC S9(7)V99 COMP-3. EXEC SQL DECLARE EMPCUR CURSOR FOR SELECT EMPNO, EMPNAME, DEPTNO, SALARY FROM EMP FOR UPDATE OF SALARY END-EXEC. PROCEDURE DIVISION. EXEC SQL OPEN EMPCUR END-EXEC. PERFORM UNTIL SQLCODE NOT = 0 EXEC SQL FETCH EMPCUR INTO :WS-EMPNO, :WS-EMPNAME, :WS-DEPTNO, :WS-SALARY END-EXEC EVALUATE SQLCODE WHEN 0 COMPUTE WS-NEW-SAL = WS-SALARY * 1.25 EXEC SQL UPDATE EMP SET SALARY = :WS-NEW-SAL WHERE CURRENT OF EMPCUR END-EXEC WHEN 100 EXEC SQL CLOSE EMPCUR END-EXEC WHEN OTHER DISPLAY "FETCH ERROR " SQLCODE STOP RUN END-EVALUATE END-PERFORM.
• WHERE CURRENT OF EMPCUR updates exactly the row that the last successful FETCH positioned the cursor on.
• The UPDATE must come after a FETCH that returned SQLCODE 0 - there is no 'current row' before the first FETCH or after SQLCODE 100.

Rules for positioned updates

• The cursor must be open and positioned on a row by FETCH (SQLCODE 0) before UPDATE ... WHERE CURRENT OF.
• After the UPDATE, the cursor stays positioned on the same row - the next FETCH moves to the following row.
• A COMMIT ends the unit of work and closes the cursor (unless WITH HOLD) - re-OPEN if you must continue after a commit.
• Only one row is ever updated by WHERE CURRENT OF - it never affects other rows.
• Remember to CLOSE the cursor when the loop finishes, in both the normal and error paths.