How to use CURSORS to process multiple rows.

Consider below SELECT query -

SELECT FNAME, LNAME
     FROM MM01.CUSTOMER
        WHERE STATE = 'MH'

This select statement can possibly return more than one row from the table. Hence we should not code the INTO caluse with this type of statement, and we shouldn't code it inside the Procedure Division.
Instead, we must code it inside DECLARE CURSOR statement.

Hence, to process a result table that contains more than one row in a COBOL program, we must use a cursor. A CURSOR is a pointer that identifies the current-row in a result table. When we user cursor, we work through a result table one row at a time.

Four SQL statements that is used for cursor processing are :
DECLARE CURSOR - Defines a result table and names a cursor for it.
OPEN cursor-name - Creates the result table and positions the cursor before the first row in the table.
FETCH cursor-name -Fetches the next row from the result table.
CLOSE cursor-name - Closes the result table.

Examples :
A DECLARE CURSOR statement that defines a cursor named CUST_CURS

EXEC SQL 
    DECLARE CUST_CURS CURSOR FOR
        SELECT EMPNO, DEPTNO, FNAME, LNAME
        FROM MM01.CUSTOMER
            WHERE DEPTNO = :DEPTNO
END-EXEC.

An OPEN statement that creates the result table defined by the CUST_CURS

EXEC SQL
    OPEN CUST_CURS
END-EXEC.

A FETCH statement that fetches the next row in the CUST_CURS resukt table

EXEC SQL
    FETCH CUST_CURS
         INTO :EMPNO, :DEPTNO, :FNAME, :LNAME
END-EXEC.

A CLOSE statement that closes the CUST_CURS cursor

EXEC SQL
    CLOSE CUST_CURS
END-EXEC.

DB2 will automatically close the cursor when our program terminates.
When a FETCH statement is executed and there are no more rows in the result table , DB2 returns a value of +100 to SQLCODE field.

We can also code the DISTINCT keyword to exclude duplicate rows from the result table and we could code ORDER BY clause to sort the rows in the result table.

DECLARE CURSOR can be coded in Working-Storage Section.