Non-scrollable Cursor/Serial Cursor
Summary
The non-scrollable cursor processes the row(s) one by one from the table or result table.
NO SCROLL keyword used to specify the cursor is non-scrollable.
If the cursor is not scrollable, each FETCH operation positions the cursor at the next sequential row in the result set.
If the SCROLL option is not specified, NO SCROLL is the default option.
Positioned UPDATE and positioned DELETE operations are allowed on Non-scrollable cursors.
"Non-Scrollable Cursors" also called as "Serial Cursors".
Syntax -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name
NO SCROLL 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 non-scrolling cursor for retrieving all employee names from employee_details table.
Code -
EXEC SQL
DECLARE E1 NO SCROLL CURSOR FOR
SELECT EMP_NAME
FROM EMPLOYEE_DETAILS
END-EXEC.
Scenario2 - Declare the cursor for retrieving all employee names from the employee_details table.
Code -
EXEC SQL
DECLARE E2 CURSOR FOR
SELECT EMP_NAME
FROM EMPLOYEE_DETAILS
END-EXEC.
Scenario3 - Declare the cursor for retrieving employee names under department 1000 from the employee_details table.
Code -
EXEC SQL
DECLARE E3 NO SCROLL CURSOR FOR
SELECT EMP_NAME
FROM EMPLOYEE_DETAILS
WHERE DEPT_ID = 1000
END-EXEC.
Scenario4 - Declare non-scrolling cursor for retrieving all employee names from highest salary to lowest.
Code -
EXEC SQL
DECLARE E4 CURSOR FOR
SELECT EMP_NAME
FROM EMPLOYEE_DETAILS
ORDER BY SALARY DESC
END-EXEC.
Scenario5 - Declare a non-scrolling cursor for retrieving a 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
END-EXEC.
Scenario6 - Declare non-scrolling cursor for retrieving a 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
END-EXEC.
Scenario7 - Declare non-scrolling cursor for retrieving employee names under department 1000 from employee_details table and hike 2000 salary.
Code -
EXEC SQL
DECLARE E2 CURSOR FOR
SELECT EMP_NAME,
SALARY
FROM EMPLOYEE_DETAILS
WHERE DEPT_ID = 1000
FOR UPDATE OF SALARY
END-EXEC.
Practical Example -
Scenario - The below example describes how the non-scrollable cursor is coded in the COBOL + DB2 program.
Requirement - Display employee_details table data for department 1000.
NON SCROLLABLE CURSOR Example Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CNSCROLL.
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 CSR-NSCROLL NO SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION,
MANAGER_ID,
DATE_OF_HIRE,
SALARY,
DEPT_ID
FROM EMPLOYEE_DETAILS
WHERE DEPT_ID = 1000
END-EXEC.
PROCEDURE DIVISION.
* OPENING CURSOR
EXEC SQL
OPEN CSR-NSCROLL
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 CSR-NSCROLL
END-EXEC.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR-NSCROLL
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 -
