The examples in this section show referencing a structure for output. Assume the following table resides in the database:
CREATE TABLE TABLE1, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(FIELD1 CHAR(20),
FIELD2 INTEGER,
FIELD3 CHAR(6),
FIELD4 DECIMAL(13,2))
UNIQUE PRIMARY INDEX (FIELD1)
Example: Single Row SELECT
EXEC SQL
SELECT *
INTO :STRUCTURE1
FROM TABLE1
WHERE FIELD3 = ’ABCDEF’;
Example: Multiple Row SELECT
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT *
FROM TABLE1;
EXEC SQL
OPEN C1;
EXEC SQL
FETCH C1 INTO :STRUCTURE1; (loop for FETCH)
EXEC SQL
CLOSE C1;
Example: Single Row SELECT That Returns Only Some Columns
EXEC SQL
SELECT FIELD1,
FIELD3
INTO :FIELD1,
:FIELD3
FROM TABLE1
WHERE FIELD2 = 99;
Example: Single Row SELECT Using Qualified Variable References
EXEC SQL
SELECT FIELD1,
FIELD3
INTO :STRUCTURE1.FIELD1,
:STRUCTURE1.FIELD3
FROM TABLE1
WHERE FIELD2 = 99;
Example: Single Row INSERT Using a Structure for Field Values
EXEC SQL
INSERT INTO TABLE1
VALUES (:STRUCTURE1)
END-EXEC
The fields in the table would receive the following assignments:
TABLE1.FIELD1 <- FIELD1 of STRUCTURE1 TABLE1.FIELD2 <- FIELD2 of STRUCTURE1 TABLE1.FIELD3 <- FIELD3 of STRUCTURE1 TABLE1.FIELD4 <- FIELD4 of STRUCTURE1
The precompiler uses the elementary items as sending/receiving variables whenever a host structure name is used. If this structure is defined as:
DCL 01 STRUCTURE1,
02 FIELD1 CHAR(20),
02 FIELD2 FIXED BIN(31),
02 FIELD3,
03 FIELD3A CHAR(2),
03 FIELD3B CHAR(2),
03 FIELD3C CHAR(2),
02 FIELD4 FIXED DEC(13,2);
and specify the following single row SELECT statement:
EXEC SQL
SELECT *
INTO :STRUCTURE1
FROM TABLE1
WHERE FIELD3 = ’ABCDEF’;
the precompiler generates code such that PL/I attempts these assignments:
TABLE1.FIELD1 -> FIELD1 of STRUCTURE1 TABLE1.FIELD2 -> FIELD2 of STRUCTURE1 TABLE1.FIELD3 -> FIELD3A of STRUCTURE1 TABLE1.FIELD4 -> FIELD3B of STRUCTURE1
Data moved into FIELD3A is truncated, and moving data into FIELD3B causes an error.