What is cursor?
CURSOR is a DB2 object. It deals with a set of rows. Why we need to go for CURSOR- since we have already SELECT statement . The answer is – if SELECT statement retrieves more than one row, COBOL program may not handle it. So we need at a time one row.
At this time, we need to introduce CURSOR. The advantage of it is, though we get multiple rows for a given SELECT statement, CURSOR will process at a time one row to the COBOL host variable.
Life Cycle of a CURSOR
- DECLARE – It is only a definition. It does not execute SQL statement.
- OPEN – It EXECUTES SQL statement. It may or may not build result table. And, keeps cursor position in the result table just before first row of result table
- FETCH- It fetches data from result table into COBOL host variable
- CLOSE- It closes the cursor, and releases all resources
Usage of CURSOR
If simple SQL statement return only one row, it is up to you using CURSOR. We will get -811 error, when singleton SQL statement retrieves more than a row.
The Solution to resolve -811
- Use FETCH FIRST ROW ONLY
- Use CURSORS
How to handle multiple rows in CURSOR?
We need to put FETCH in the loop. So that , we can access one by one all the rows till end of result table.