Sensitive Scrollable Cursor
Summary
Sensitive scroll cursor is the cursor that is sensitive to insert, update, or delete operations performed on the database table after generating the result table. i.e., after opening the cursor.
For example, when the positioned UPDATE or DELETE is performed using the cursor, the changes are immediately visible in the result table.
Declare scrollable cursors as SENSITIVE only if you want to see the latest data modified with the updates or deletes after the cursor opens.
The result table size, the order of the rows remains the same after the cursor opens i.e. -
- Rows inserted after the cursor opened are not added to the result table.
- Rows that are positioned updated/deleted after the cursor is opened are visible in the result table.
- Also, deleted rows are not visible.
Syntax -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name SENSITIVE SCROLL
CURSOR FOR select-statement
FOR UPDATE OF columns-list/FOR READ ONLY
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.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 sensitive scroll cursor for updating column manager_id of employee_details table.
Code -
EXEC SQL
DECLARE E1 SENSITIVE SCROLL CURSOR FOR
SELECT EMP_NAME,
MANAGER_ID
FROM EMPLOYEE_DETAILS
FOR UPDATE OF MANAGER_ID
END-EXEC.Scenario2 - Declare the sensitive scroll cursor for updating all columns of the employee_details table.
Code -
EXEC SQL
DECLARE E2 SENSITIVE SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION,
MANAGER_ID,
DATE_OF_HIRE,
SALARY,
DEPT_ID
FROM EMPLOYEE_DETAILS
FOR UPDATE
END-EXEC.Scenario3 - Declare the sensitive scroll readonly cursor for designation "FRESHER" in department 1000 of employee_details table.
Code -
EXEC SQL
DECLARE E3 SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID = 1000
FOR READ ONLY
END-EXEC.Scenario4 - Declare the sensitive scroll readonly cursor for designation "FRESHER" in departments 1000 and 2000 of the employee_details table.
Code -
EXEC SQL
DECLARE E4 SCROLL 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 READ ONLY
END-EXEC.Practical Example -
Scenario - The below example describes how the sensitive scroll cursor is coded in the COBOL + DB2 program.
Requirement - Hike the salary of TESTER(s) by 1000.
SENSITIVE SCROLL CURSOR Example Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CSENSITS.
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 SENSITIVE SCROLL 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
DISPLAY "FETCH CUSOR BEFORE UPDATE...".
DISPLAY "-----------------------------------------------".
IF SQLCODE EQUAL ZERO
PERFORM FETCH-EMP-DETAILS
THRU FETCH-EMP-DETAILS-EXIT
UNTIL SQLCODE NOT EQUAL 0
END-IF.
DISPLAY "NO OF ROWS UPDATED: ", WS-UCNT.
* PLACING THE CURSOR AGAIN TO THE BEGINNING OF THE RESULT TABLE
PERFORM SCROLL-TO-START-OF-RT
THRU SCROLL-TO-START-OF-RT-EXIT
* FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE
DISPLAY " ".
DISPLAY "FETCH CUSOR AFTER UPDATE...".
DISPLAY "-----------------------------------------------".
IF SQLCODE EQUAL ZERO
PERFORM FETCH-EMP-DETAILS-FB
THRU FETCH-EMP-DETAILS-FB-EXIT
UNTIL SQLCODE NOT EQUAL 0
END-IF.
* CLOSING CURSOR
EXEC SQL
CLOSE CSR1
END-EXEC.
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
DISPLAY EMP-ID " : " EMP-NAME " : " DESIGNATION
- " : " MANAGER-ID " : " SALARY
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 + 1000
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.
SCROLL-TO-START-OF-RT.
* FETCH CURSOR
EXEC SQL
FETCH BEFORE FROM CSR1
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
WHEN 100
CONTINUE
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
SCROLL-TO-START-OF-RT-EXIT.
EXIT.
FETCH-EMP-DETAILS-FB.
* FETCH CURSOR
EXEC SQL
FETCH CSR1
INTO :EMP-ID,
:EMP-NAME,
:DESIGNATION,
:MANAGER-ID
:MANAGER-ID-NI,
:SALARY
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY EMP-ID " : " EMP-NAME " : " DESIGNATION
- " : " MANAGER-ID " : " SALARY
WHEN 100
CONTINUE
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
FETCH-EMP-DETAILS-FB-EXIT.
EXIT.
**************************** Bottom of Data ****************************Output -
