Now you should be able to pass data generated by your program into an SQL command. But how do you retrieve the results of a query? For that purpose, embedded SQL provides special variants of the usual commands SELECT and FETCH. These commands have a special INTO clause that specifies which host variables the retrieved values are to be stored in. SELECT is used for a query that returns only single row, and FETCH is used for a query that returns multiple rows, using a cursor.
Here is an example:
* * assume this table: * CREATE TABLE test (a int, b varchar(50)); * EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 V1 PIC S9(9). 01 V2 PIC X(50) VARYING. EXEC SQL END DECLARE SECTION END-EXEC. ... EXEC SQL SELECT a, b INTO :V1, :V2 FROM test END-EXEC.
So the INTO clause appears between the select list and the FROM clause. The number of elements in the select list and the list after INTO (also called the target list) must be equal.
Here is an example using the command FETCH:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 V1 PIC S9(9).
01 V2 PIC X(50) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test END-EXEC.
...
PERFORM WITH
...
EXEC SQL FETCH NEXT FROM foo INTO :V1, :V2 END-EXEC
...
END-PERFORM.Here the INTO clause appears after all the normal clauses.