EXEC SQL embedded SQL statement END-EXEC
Note: A semi-colon is used instead of END-EXEC when SQL is embedded in C or Pascal.
declare c cursor for
select cname, ccity
from deposit, customer
where deposit.cname = customer.cname and deposit.balance > :amount
where amount is a host-language variable.
EXEC SQL open c END-EXEC
This statement causes the DB system to execute the query and to save the results within a temporary relation.
A series of fetch statement are executed to make tuples of the results available to the program.
EXEC SQL fetch c into :cn, :cc END-EXEC
The program can then manipulate the variable cn and cc using the features of the host programming language.
A single fetch request returns only one tuple. We need to use a while loop (or equivalent) to process each tuple of the result until no further tuples (when a variable in the SQLCA is set).
We need to use close statement to tell the DB system to delete the temporary relation that held the result of the query.
EXEC SQL close c END-EXEC