Cursors in the application program are used to retrieve the multiple rows and process them one by one .
Generally by using a singleton select statement in the application program, it is possible to retrieve only one row at a time as the host variable structure allows keeping one value at a time.
Select col1,col2 from tab1
Where col1 =’string1’
Into : ww-host1
By using the above statement we can only have one set values at one execution of the program.
But it is quite common to have a requirement to retrieve a set of rows that meets particular criteria and process all the set of rows one by one. This can be achieved by the mechanism called CURSOR.
Using Cursors in application programs is quite simple and easy to understand.
Using Cursor in application Programs:
1) Declare the Cursor
2) Open it
3) Fetch the cursor
4) Close the cursor After you have processed all the required data
This statement defines the cursor and gives a name to the Cursor. Basically it contains the SQL statement to be executed.
Sample Declare Cursor Statement:
DECLARE ‘cursor name’ CURSOR FOR
SELECT col1, col2….
WHERE COL1= ‘xxxx’
- Declare Cursor statement is not an executable one, it just specifies the basic SQl to be executed and gives a name to the cursor.
- This can be declared either in Working storage or in procedure division.
Open cursor statement is an executable one. Once it has been issued, the SQl statement gets executed and a result set will be built and all the rows retrieved will be put in the result set.
The cursor will be pointed to the first row in the result set, so it is ready to process the rows.
OPEN ‘cursor name’
Fetch statement will fetches one set of values at a time in to the host variables, and this can be kept in loop to process all the rows until no more rows in the result set.
Fetch ‘cursor name’ into : ww-host-var
The above topic is about the basics of cursors in cobol db2 application program.