CURSOR

To retrieve multiple rows for a given condition.

Let us take the following example:

Exec sql

                        Select cno,cname,cloc

                                 into :hv-cno,:hv-cname,:hv-cloc

                              from cust where cloc =:hv-cloc

            end-exec.

If the condition satisfy for one row it executes successfully. If the condition satisfy for multiple rows it wont work. It returns –811 as SALCODE. For this we use cursors.

Ø  Cursors can be used to retrieve multiple rows for a given condition

Ø  Cursor cycle is declare          open             fetch             close

Ø  Declare:  declares or define name for cursor against a table

                  Can be coded in working-storage section or procedure division

                  For better readability code in working-storage section.

Ø  Open:  can be used to open a cursor with rows for a given conditions inbuffer.

Retireves data in to buffer

Must be coded in the procedure division only

Where condition value must be supplied before opening a cursor.

Ø  Fetch:  can be used to retrieve rows one by one from buffer into application prog.

Which must be coded in procedure divison after open.

Must be coded with hostvariables

No of host variables in fetch & no of columns in the declare must be same

Canbe executed multiple times using perform. i.e. till EOT or record not found which can be identified by SQLCODE = 100

Ø  Close :  used to close the cursor

                 Must be coded in procedure division only

                  Must be executed after open statement.

Practical examples : Can be used to retrieve the data based on loc, date, products.

EXEC SQL

      DECLARE C1 CURSOR FOR

                  SELECT CNO,CNAME FROM CUST

                    WHERE CNAME=:HV-CNAME

END-EXEC.

EXEC SQL

      OPEN C1.

END-EXEC.

PERFORM UNTIL SQLCODE= 100

EXEC SQL

      FETCH  C1 INTO :HV-CNO,:HV-CNAME

END-EXEC

END-PERFORM.

EXEC SQL

      CLOSE C1

END-EXEC

For Update of where current of:

Ø  Which can be used to update row by row when multiple rows are satisfied.

Ø  Before update cursor has to be declared with for update of column option.

Ø  Where current of cursor name option must be used with update command

Ø  Withhold:  this option can be used to remain cursors open even after commit statement.

Ø  Must be coded with cursor statement

EXEC SQL

      DECLARE C2 CURSOR WITH HOLD FOR

                  SELECT CNO,CNAME FROM CUST

                    WHERE CNAME=:HV-CNAME

                     FOR UPDATE OF CNAME

END-EXEC.

EXEC SQL

      OPEN C1.

END-EXEC.

EXEC SQL

      FETCH  C2 INTO :HV-CNO,:HV-CNAME

END-EXEC

EXEC SQL

      UPDATE CUST SET CNAME=”ABC” WHERE CURRENT OF C2.

EMD=EXEC.

EXEC SQL

      CLOSE C1

END-EXEC