The following example shows how to nest two cursors. Cursor C2 depends upon the value fetched into TITLE-ID from cursor C1.
The program gets the value of TITLE-ID at open time, not at declare time.
...
exec sql declare C1 cursor for
select title_id, title, royalty from titles
end-exec
exec sql declare C2 cursor for
select au_lname, au_fname, from authors
where au_id in
(select au_id from titleauthor
where title_id = :TITLE-ID)
end-exec
exec sql open C1 end-exec.
PERFORM READ-TITLE UNTIL SQLCODE = 100.
READ-END.
. . .
READ-TITLE.
exec sql fetch C1 into
:TITLE-ID, :TITLE, :ROYALTY end-exec.
IF SQLCODE NOT = 100
MOVE ROYALTY TO DISP-ROY
DISPLAY "Title ID: " TITLE-ID
", Royalty: " DISP-ROY
IF ROYALTY > 10
exec sql open C2 end-exec
PERFORM READ-AUTH UNTIL SQLCODE = 100
exec sql close C2 end-exec.
READ-AUTH.
exec sql fetch C2 into :AU-LNAME, :AU-FNAME
end-exec
IF SQLCODE NOT = 100
DISPLAY " AUTHOR: " AU-LNAME " "
AU-FNAME.
See the online sample programs for more examples using cursors. For details on accessing the online examples, see the Open Client/Server Programmer’s Supplement.
