DB2 Cursors

DB2 CURSORS

Using CURSORs in the COBOL programs.
In the part 1, we have seen how to access data using singleton select statement. using singleton select we can only select one record from the table. When there is a situation, like.. program needs to get all data in EMPLOYEE table and print all
employee names and their respective salaries. With singleton select statement (refer part1) it is not possible.

Using CURSORs we can get all records in the table.

Let us see step by step how we can do this in a cobol program.

TASK : Get all records from EMPLOYEE table and display EMPNAME and SALARY in the spool

STEP 1. DECLARE cursor in WORKING-STORAGE SECTION.

EXEC SQL
DECLARE EMPCUR CURSOR FOR
SELECT EMPNAME, SALARY
FROM EMPLOYEE
END-EXEC.

STEP 2. Open the cursor in PROCEDURE division.

EXEC SQL

OPEN EMPCUR

END-EXEC.

At this time, sql specified in the step1 executes and cursor is ready with data.

STEP 3. Get data from cursor record by record.

Following code should be in a loop till we get SQLCODE = 100. Let us assume there are 5 records in EMPLOYEE table, every time when you execute this code, we will get a record. 6th time when we execute the code we will get SQLCODE = 100, we can end the loop.


fetching/retrieving data from cursor.

EXEC SQL
FETCH EMPCUR
INTO :HV-EMPNAME,
:HV-SALARY
END-EXEC.

EVALUATE SQLCODE
WHEN 0

DISPLAY ' -----------------'
DISPLAY ' Employee name ' HV-EMPNAME
DISPLAY ' Employee salary ' HV-SALARY
DISPLAY ' -----------------'

WHEN 100

MOVE 'Y' TO END-OF-LOOP

WHEN OTHER

DISPLAY ' ERROR IN RETRIEVING THE DATA FROM EMPLOYEE TABLE'
DISPLAY 'SQLCODE : ' SQLCODE

END-EVALUATE.



TIP : SQLCODE = 100 is end of the cursor, in case of cursors.
SQLCODE = 100 is record not found in case of singleton select



STEP 4. Once retrieving records from table completed, we need to
close the cursor using following command.

EXEC SQL
CLOSE CURSOR
END-EXEC.



Other info about cursors....

Your program can have several cursors. Each cursor requires its own:
DECLARE CURSOR statement to define the cursor
OPEN and CLOSE statements to open and close the cursor
FETCH tatement to retrieve rows from the cursor's result table.

You can use cursors to fetch, update, or delete a row of a table,
but you cannot use them to insert a row into a table.