CURSORS in DB2 and how to use CURSOR in DB2?

A mechanism allowing an application program (cobol-db2 program for example) to retrieve a set of rows. Suppose our SQL query fetches many-rows result table. Using CURSOR our COBOL program can gain access to individual row of  the result table. 

There are two types of cursor:
1.Row-set positioned cursor : Here the result table are stored into Host variable arrays.
2.Row-positioned cursor : This type points to the current row/particular row in the result set.
It points to the next row to be passed to the application program.

The primary steps for using any  cursor are as follows:
Step1. Declare the cursor
Step2 Open the Cursor. This causes Db2 to build the result set.
Step3. Fetch each row ,one at a time, and pass it onto application program
Step4: Close the cursor.

DECLARE  the cursor:
EXEC SQL
 DECLARE EMP_CURSOR CURSOR FOR
 SELECT EMP_NAME,EMP_ID
 FROM EMP
 WHERE EMP_JOIN_DATE > '20000101'
END-EXEC.
DECLARE statement does not build result set.It simply forms the SQL statement we want to use to fetch the data. At DB2 precompile time, the definition of the CURSOR is stored in the bindfile, and the statement is commented.

OPEN  the cursor:
The open statement actually runs the SQL statement and builds the result set and makes it ready ready to be processed to be used to the application program in subsequent steps.
EXEC SQL
 OPEN EMP_CURSOR
END-EXEC.
Once the open is done, we need to fetch the data

FETCH the cursor:
EXEC SQL
 FETCH  EMP_CURSOR
 INTO :EMPNAME,:EMPID
END-EXEC.

FETCH is the only way to get the result into application program Host variable. We fetch each row into the Host variable and process the data. We can continue fetching of the data until we get a SQLCODE of +100  which implies END of the result table.
CLOSE the CURSOR
Once we are done with fetching of the records, we need to close the cursor.
EXEC SQL
 CLOSE EMP_CURSOR
END-EXEC.

How to UPDATE AND DELETE  records in cursor.
There can be instances where we need to modify the data in db2 table based on some values in a programmatic way. Had it been a spufi or QMF we have our update and delete statements ready to run and modify. But  we are in cobol program. Nevertheless we can do the same modifications using cursors again.!! We need to add special clause while declaring the cursor with  'FOR UPDATE OF'.
Even if we want to delete, we need to mention 'FOR UPDATE OF'  in declare statement  and we need to delete the entire row. Also we need to use WHERE CURRENT OF to update or delete the last row fetched.
Lets see the below example where we will update and delete some row in the cursor :

EXEC SQL
 DECLARE EMP_CURSOR CURSOR FOR
 SELECT EMP_NAME,EMP_ID
 FROM EMP
 WHERE EMP_JOIN_DATE > '20000101'
 FOR UPDATE OF NAME       <== DECLARE THE CURSOR WITH THIS CLAUSE
END-EXEC.
...............................
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.
****Important point Regarding the Usage of 'FOR UPDATE OF'  in CURSOR.****
If the select statement defining the cursor contains ORDER BY,GROUP BY, DISTINCT, SET OPERATORS ( UNION,EXCEPT, INTERSECT), JOIN, FOR FETCH ONLY/FOR READ ONLY, then the usage of 'FOR UPDATE OF' is NOT permitted. The above keywords make the cursor READ ONLY cursor.


Read about Cursor types: Sensitive and insensitive cursors !