Read Only Cursor
Summary
The read-only cursor is a cursor that is used to perform only read operations on the table or result table.
FOR FETCH ONLY option used to declare READ ONLY Cursors.
Positioned UPDATE and positioned DELETE operations not allowed on read-only cursors.
The read-only cursor has the following advantages -
- It improves the performance of FETCH operations.
- No blocking and avoids exclusive locks.
- It prevents some types of deadlocks.
"FOR FETCH ONLY" can specify as a synonym for "FOR READ ONLY".
Syntax -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name
CURSOR FOR select-statement
FOR FETCH 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 cursor for retrieving all employee names from the employee_details table.
EXEC SQL
DECLARE E1 CURSOR FOR
SELECT EMP_NAME
FROM EMPLOYEE_DETAILS
END-EXEC.
Scenario2 - Declare the read-only cursor for retrieving all employee names from the employee_details table.
Code -
EXEC SQL
DECLARE E2 CURSOR FOR
SELECT EMP_NAME
FROM EMPLOYEE_DETAILS
FOR FETCH ONLY
END-EXEC.
Scenario3 - Declare the read-only cursor for retrieving all employee names from highest salary to lowest.
EXEC SQL
DECLARE E2 CURSOR FOR
SELECT EMP_NAME
FROM EMPLOYEE_DETAILS
ORDER BY SALARY DESC
FOR FETCH ONLY
END-EXEC.
Scenario4 -Declare the read-only cursor for retrieving the sum of salaries at the designation level.
Code -
EXEC SQL
DECLARE E2 CURSOR FOR
SELECT DESIGNATION,
SUM(SALARY) SALARIES_TOTAL
FROM EMPLOYEE_DETAILS
GROUP BY DESIGNATION
FOR FETCH ONLY
END-EXEC.
Scenario5 - Declare the read-only cursor for retrieving the sum of salaries > 18000 at designation level.
Code -
EXEC SQL
DECLARE E2 CURSOR FOR
SELECT DESIGNATION,
SUM(SALARY) SALARIES_TOTAL
FROM EMPLOYEE_DETAILS
GROUP BY DESIGNATION
HAVING SUM(SALARY) > 18000
FOR FETCH ONLY
END-EXEC.
Practical Example -
Scenario - The below example describes how the read-only cursor is coded in the COBOL + DB2 program.
Requirement - Display employee_details table data.
READONLY CURSOR Example Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CREADONL.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLDET
END-EXEC.
* DECLARING READONLY CURSOR
EXEC SQL
DECLARE CSR1 CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION,
MANAGER_ID,
DATE_OF_HIRE,
SALARY,
DEPT_ID
FROM EMPLOYEE_DETAILS
FOR FETCH ONLY
END-EXEC.
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.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR1
INTO :EMP-ID,
:EMP-NAME,
:DESIGNATION,
:MANAGER-ID
:MANAGER-ID-NI,
:DATE-OF-HIRE,
:SALARY,
:DEPT-ID
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY EMP-ID, "|", EMP-NAME, "|",
DESIGNATION, "|", MANAGER-ID, "|",
DATE-OF-HIRE, "|", SALARY, "|", DEPT-ID
WHEN 100
CONTINUE
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
FETCH-EMP-DETAILS-EXIT.
EXIT.
**************************** Bottom of Data ****************************
Output -
