DB2 Declare Cursor Example

Input-

Scenario1 - Declare the cursor for retrieving all employee names from the employee_details table.

   EXEC SQL 
	DECLARE CS1 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.

   EXEC SQL 
	DECLARE CS2 CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	    FOR FETCH ONLY
   END-EXEC.

Scenario3 -Declare the cursor for retrieving employees in the order old to recent from the table employee_details.

   EXEC SQL 
	DECLARE CS4 SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY DATE_OF_JOIN ASC
     	    FOR FETCH ONLY
   END-EXEC.

Scenario4 -Declare the updatable cursor for updating MANAGER for employees who are having manager "EMPLOYEE1" from the employee_details table.

   EXEC SQL 
	DECLARE CS5 CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  WHERE MANAGER_ID = "EMPLOYEE1"
     	    FOR UPDATE OF MANAGER_ID
   END-EXEC.

Scenario5 - Declare the scrollable cursor for retrieving rows from the table employee_details.

   EXEC SQL 
	DECLARE CS6 SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Scenario6 - Declare the sensitive scrollable cursor for retrieving rows from the table employee_details.

   EXEC SQL 
	DECLARE CS7 ASENSITIVE SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Scenario7 - Declare the insensitive scrollable cursor for retrieving rows from the table employee_details.

   EXEC SQL 
	DECLARE CS8 INSENSITIVE SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Scenario8 - Declare cursor WITH HOLD for retrieving rows from the table employee_details.

   EXEC SQL 
	DECLARE CS9 WITH HOLD CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR UPDATE OF MANAGER_ID
   END-EXEC.

Scenario9 - Declare WITHOUT HOLD cursor for retrieving rows from the table employee_details.

   EXEC SQL 
	DECLARE CS10 WITHOUT HOLD CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Scenario10 - Declare cursor using a variable that contains cursor name for retrieving rows from the table employee_details.

   Declaration-
  05 WS-CS-VAR     PIC X(10) VALUE "CS11".
  
Code-
   EXEC SQL 
	DECLARE WS-CS-VAR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Practical Example -

Scenario - The below example describes how the cursor is declared and used in the COBOL + DB2 program.

Requirement - Display employees id, name, designation from department 1000.

Input -

CURSOR Example Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
       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 CURSOR 
           EXEC SQL
                DECLARE CSR1 CURSOR FOR
                SELECT EMP_ID,
                       EMP_NAME,
                       DESIGNATION
                FROM   EMPLOYEE_DETAILS
                WHERE  DEPT_ID = 1000
                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
           END-EXEC. 

           EVALUATE SQLCODE
               WHEN ZERO
                    DISPLAY  EMP-ID, "|", EMP-NAME, "|",
                             DESIGNATION 
               WHEN 100
                    CONTINUE
               WHEN OTHER
                    DISPLAY "DB2 ERROR:  ", SQLCODE
           END-EVALUATE.

       FETCH-EMP-DETAILS-EXIT.
           EXIT.

Output -

---