Updatable Cursor
Summary
The updatable cursor is used to perform updates to the columns in the retrieved row(s).
Positioned UPDATE and positioned DELETE are allowed in updatable cursors.
FOR UPDATE OF clause used to declare the updatable cursors.
FOR UPDATE OF followed with a list of columns that might get updated.
WHERE CURRENT OF used to update or delete the rows in updatable cursors using the current row positioning.
All columns can be updated if a FOR UPDATE OF, an ORDER BY, a FOR READ ONLY, or a SCROLL clause without a DYNAMIC clause is not specified.
If the "FOR UPDATE OF" clause is specified while declaring a cursor, UPDATE or DELETE statements might execute on any specific row or all the rows of the result table. However, it is not mandatory to execute UPDATE or DELETE for every FETCH.
Syntax -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name
CURSOR FOR select-statement
FOR UPDATE OF columns-list
END-EXEC.
Opening Cursor -
EXEC SQL
OPEN cursor-name/cursor-variable-name;
END-EXEC.
Fetching Cursor -
EXEC SQL
FETCH cursor-name/cursor-variable-name
INTO :hv1,
:hv2,
.
.
:hvn;
END-EXEC.
UPDATE Statement -
EXEC SQL
UPDATE table_name
SET column1 = :hv1,
column2 = :hv2,
.
.
columnn = :hvn
WHERE CURRENT OF cursor-name/cursor-variable-name
END-EXEC.
Closing Cursor -
EXEC SQL
CLOSE cursor-name/cursor-variable-name;
END-EXEC.
cursor-name -
- Cursor-name is the name of the cursor used in an application program to refer to the cursor.
- The cursor-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
cursor-variable-name -
- Cursor-variable-name is a working-storage variable that contains the cursor name used in an application program to refer to the cursor.
- The cursor-variable-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
Examples -
Input -
Scenario1 - Declare the cursor for updating column manager_id of employee_details table.
Code -
DECLARE CURSOR -
EXEC SQL
DECLARE E1 CURSOR FOR
SELECT EMP_NAME,
MANAGER_ID
FROM EMPLOYEE_DETAILS
FOR UPDATE OF MANAGER_ID
END-EXEC.
UPDATE -
EXEC SQL
UPDATE EMPLOYEE_DETAILS
SET MANAGER_ID = :WS-MANAGER-ID
WHERE CURRENT OF E1
END-EXEC.
Scenario2 - Declare the cursor for updating all columns of the employee_details table.
Code -
DECLARE CURSOR -
EXEC SQL
DECLARE E2 CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION,
MANAGER_ID,
DATE_OF_HIRE,
SALARY,
DEPT_ID
FROM EMPLOYEE_DETAILS
FOR UPDATE
END-EXEC.
UPDATE -
EXEC SQL
UPDATE EMPLOYEE_DETAILS
SET DESIGNATION = :WS-DESG,
MANAGER-ID = :WS-MANAGER-ID,
DATE_OF_HIRE = :WS-DOH,
SALARY = :WS-SALARY
WHERE CURRENT OF E2
END-EXEC.
Scenario3 - Declare the cursor for updating designation "FRESHER" in department 1000 of employee_details table.
Code -
DECLARE CURSOR -
EXEC SQL
DECLARE E3 CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID = 1000
FOR UPDATE OF DESIGNATION
END-EXEC.
UPDATE -
EXEC SQL
UPDATE EMPLOYEE_DETAILS
SET DESIGNATION = "JUNIOR DEVELOPER"
WHERE CURRENT OF E3
END-EXEC.
Scenario4 - Declare the cursor for updating designation "FRESHER" in departments 1000 and 2000 of the employee_details table.
Code -
DECLARE CURSOR -
EXEC SQL
DECLARE E4 CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID IN (1000, 2000)
ORDER BY DEPT_ID ASC
FOR UPDATE OF DESIGNATION
END-EXEC.
UPDATE -
EXEC SQL
UPDATE EMPLOYEE_DETAILS
SET DESIGNATION = "JUNIOR DEVELOPER"
WHERE CURRENT OF E4
END-EXEC.
Practical Example -
Scenario - The below example describes how the update cursor is coded in the COBOL + DB2 program.
Requirement - Hike the salary of TESTER(s) by 3000.
UPDATE CURSOR Example Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CUPDATE.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLDET
END-EXEC.
* DECLARING UPDATE CURSOR
EXEC SQL
DECLARE CSR1 CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION,
MANAGER_ID,
SALARY
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = 'TESTER'
AND DEPT_ID = 2000
FOR UPDATE OF SALARY
END-EXEC.
01 WS-VAR.
05 WS-UCNT PIC 9(02) VALUE ZEROES.
05 WS-UPDATED-SAL PIC S9(5)V9(2) USAGE COMP-3.
PROCEDURE DIVISION.
* OPENING CURSOR
EXEC SQL
OPEN CSR1
END-EXEC.
* FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE
IF SQLCODE EQUAL ZERO
PERFORM FETCH-EMP-DETAILS
THRU FETCH-EMP-DETAILS-EXIT
UNTIL SQLCODE NOT EQUAL 0
END-IF.
* CLOSING CURSOR
EXEC SQL
CLOSE CSR1
END-EXEC.
DISPLAY "NO OF ROWS UPDATED: ", WS-UCNT.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR1
INTO :EMP-ID,
:EMP-NAME,
:DESIGNATION,
:MANAGER-ID
:MANAGER-ID-NI,
:SALARY
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
PERFORM UPDATE-EMP-DETAILS
THRU UPDATE-EMP-DETAILS-EXIT
WHEN 100
CONTINUE
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
FETCH-EMP-DETAILS-EXIT.
EXIT.
UPDATE-EMP-DETAILS.
COMPUTE WS-UPDATED-SAL = SALARY + 3000.
EXEC SQL
UPDATE EMPLOYEE_DETAILS
SET SALARY = :WS-UPDATED-SAL
WHERE CURRENT OF CSR1
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
COMPUTE WS-UCNT = WS-UCNT + 1
WHEN 100
DISPLAY "UPDATING ROW NOT FOUND"
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
UPDATE-EMP-DETAILS-EXIT.
EXIT.
**************************** Bottom of Data ****************************
Output -
employee_details table after UPDATE -
