DB2 Cursors

A CURSOR is mainly used to retrieve more than one row from a table.

Steps to use the cursor in COBOL-DB2 program:-

Declare - Declares the cursor name with the SELECT query

Syntax:

DECLARE cursor-name CURSOR [WITH HOLD]
  [WITH RETURN [TO CALLER | TO CLIENT]] 

FOR SELECT-STATEMENT

FOR UPDATE OF(column-names) -->Optional

Example:

       EXEC SQL

               DECLARE CSR1 CURSOR FOR

SELECT DEPNO, DEPNAME, MGRNO

FROM   DEP

WHERE  ADMRDEP = :ADMRDEP

FOR UPDATE OF --->Optional

    DEPNAME

   ,MGRNO          

END-EXEC.

Cursor WITHOUT HOLD - The cursor will be closed if there is any COMMIT operation in the program. This is the default option.

Cursor WITH HOLD - The cursor will still remain open even though there is a commit operation in the program. In other words if there is a commit in the program, the cursor will get closed. So, to avoid this situation we use WITH HOLD option. 

WITH HOLD option is mainly used when there is large amount of data to be processed and stored procedure wanted to save the work that was done to this point of time.

WITH RETURN is optional, which specifies the cursor will be returned to calling program (TO CALLER) or directly to the client (TO CLIENT)

FOR UPDATE OF - To delete the entire row

WHERE CURRENT OF - Used in the UPDATE statement to delete/update the last row fetched.

EXEC SQL
 UPDATE EMP
 SET EMP_NAME=:EMPNAME
 WHERE CURRENT OF EMP_CURSOR
END-EXEC

EXEC SQL
 DELETE  FROM EMP
 WHERE CURRENT OF EMP_CURSOR
END-EXEC.

Open - executes the sql query and stores corresponding rows in temporary result table

Syntax:

OPEN cursor-name

Example:

    

       EXEC SQL

              OPEN CSR1

       END-EXEC.

Fetch - Fetches each row from the temporary result table and moves the data to host variables for further processing

Syntax:

        FETCH cursor-name

     INTO  :host-variable-1,

           :host-variable-2,

                       .

                       .

                       .

           :host-variable-n

Example:

      EXEC SQL

          FETCH  CSR1

INTO :DEPNO,

:DEPNAME,

:MGRNO

       END-EXEC.

Close - closes the cursor. Releases all the resources used by the cursor.

Syntax:

     CLOSE cursor-name

Example:

     

       EXEC SQL

         CLOSE CSR1

    END-EXEC.