Insensitive Scroll Cursor
Summary
Insensitive scroll cursor is the cursor that is not 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 or INSERT is performed using the cursor, the changes are not visible in the result table.
SENSITIVE FETCH is not allowed in insensitive scroll cursor and only INSENSITIVE FETCH is allowed.
The size, rows order, and the values for each row of the result table do not change after the cursor opens.
Declaring a cursor as INSENSITIVE has the below results -
- Rows inserted into the original table are not added to the result table after the cursor opens.
- The result table is read-only.
- FOR UPDATE clause is not allowed in the cursor. i.e., the cursor can't use for the positioned update or delete operations.
Syntax -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name INSENSITIVE SCROLL
CURSOR FOR select-statement
FOR READ ONLY
END-EXEC.Opening Cursor -
EXEC SQL
OPEN cursor-name/cursor-variable-name;
END-EXEC.Fetching Cursor -
EXEC SQL
FETCH [INSENSITIVE] 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.
INSENSITIVE -
- Specifies the fetch is INSENSITIVE.
- When FETCH INSENSITIVE -
- No changes to the original table are visible in the result table.
- Positioned UPDATE and DELETE statements are not allowed.
Example -
Input -
Scenario1 - Declare the INSENSITIVE SCROLL CURSOR for reading employee_details table.
Code -
EXEC SQL
DECLARE E1 INSENSITIVE SCROLL CURSOR FOR
SELECT EMP_NAME,
MANAGER_ID
FROM EMPLOYEE_DETAILS
FOR READ ONLY
END-EXEC.Scenario2 - Declare the insensitive scroll readonly cursor for designation "FRESHER" in department 1000 of employee_details table.
Code -
EXEC SQL
DECLARE E2 INSENSITIVE SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID = 1000
FOR READ ONLY
END-EXEC.Scenario3 - INSENSITIVE FETCH for getting emp_id, emp_name on insensitive scroll readonly cursor.
Code -
DECLARE CURSOR
EXEC SQL
DECLARE E3 INSENSITIVE SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID IN (1000, 2000)
ORDER BY DEPT_ID ASC
FOR READ ONLY
END-EXEC.FETCH -
EXEC SQL
FETCH INSENSITIVE E3
INTO :HV-EMP-ID,
:HV-EMP-NAME
END-EXEC.Practical Example -
Scenario - The below example describes how the INSENSITIVE SCROLL CURSOR is coded in the COBOL + DB2 program.
Requirement - Insert employee after the insensitive cursor opened.
Table Before Update -
Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CISENSIT.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLDET
END-EXEC.
* DECLARING READ ONLY CURSOR
EXEC SQL
DECLARE CSR_INSCROLL INENSITIVE SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION,
MANAGER_ID,
SALARY
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = 'TESTER'
AND DEPT_ID = 2000
FOR READ ONLY
END-EXEC.
PROCEDURE DIVISION.
* OPENING CURSOR
EXEC SQL
OPEN CSR_SSSCROLL
END-EXEC.
* INSERTING A EMPLOYEE THAT MATCHES TO THE CURSOR CONDITION
PERFORM INSERT-EMP-DETAILS
THRU INSERT-EMP-DETAILS-EXIT.
* 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 CSR_SSSCROLL
END-EXEC.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR_SSSCROLL
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-EXIT.
EXIT.
INSERT-EMP-DETAILS.
* INSERT EMPLOYEE WITH EMP_ID 10
EXEC SQL
INSERT INTO EMPLOYEE_DETAILS
VALUES (10, 'EMPLOYEE10', 'TESTER', 'EMPLOYEE4',
'2019-10-08', 15000, 2000)
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY "ROW INSERTED SUCCESSFULLY"
WHEN OTHER
DISPLAY "DB2 INSERT ERROR: ", SQLCODE
END-EVALUATE.
INSERT-EMP-DETAILS-EXIT.
EXIT.
**************************** Bottom of Data ****************************Output -
Table After Update -
