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.
Keep reading my Part-2 post.
