The SELECT INTO statement produces a result table consisting of at most one row, and assigns the values in that row to host variables.
If the table is empty, the statement assigns +100 to SQLCODE and '02000' to SQLSTATE and does not assign values to the host variables. If more than one row satisfies the search condition, statement processing is terminated, and an error occurs (SQLSTATE 21000).
This statement can be embedded only in an application program. It is an executable statement that cannot be dynamically prepared.
GROUP privileges are not checked for static SELECT INTO statements.
If the target of the SELECT INTO statement is a nickname, privileges on the object at the data source are not considered until the statement is executed at the data source. At this time, the authorization ID that is used to connect to the data source must have the privileges that are required for the operation on the object at the data source. The authorization ID of the statement can be mapped to a different authorization ID at the data source.
.-,-------------------------. V | >>-select-clause--INTO------| assignment-target |---+-----------> >--from-clause--+--------------+--+-----------------+-----------> '-where-clause-' '-group-by-clause-' >--+---------------+--+-----------------+-----------------------> '-having-clause-' '-order-by-clause-' >--+--------------------+---------------------------------------> '-fetch-first-clause-' .-FOR READ ONLY-----------------------. >--●--+-------------------------------------+--●----------------> '-FOR UPDATE--+---------------------+-' | .-,-----------. | | V | | '-OF----column-name-+-' >--+------------------+--●------------------------------------->< '-isolation-clause-'
assignment-target >>-+-global-variable-name-------------------+------------------>< +-host-variable-name---------------------+ +-SQL-parameter-name---------------------+ +-SQL-variable-name----------------------+ +-transition-variable-name---------------+ +-array-variable-name--[--array-index--]-+ '-field-reference------------------------'
For a description of the select-clause, from-clause, where-clause, group-by-clause, having-clause, order-by-clause, fetch-first-clause, and isolation-clause, see "Queries" in the SQL Reference Volume 1.
The first value in the result row is assigned to the first target in the list, the second value to the second target, and so on. Each assignment to an assignment-target is made in sequence through the list. If an error occurs on any assignment, no value is assigned to any assignment-target.
When the data type of every assignment-target is not a row type, then the value 'W' is assigned to the SQLWARN3 field of the SQLCA if the number of assignment-targets is less than the number of result column values.
If the data type of an assignment-target is a row type, then there must be exactly one assignment-target specified (SQLSTATE 428HR), the number of columns must match the number of fields in the row type, and the data types of the columns of the fetched row must be assignable to the corresponding fields of the row type (SQLSTATE 42821).
If the data type of an assignment-target is an array element, then there must be exactly one assignment-target specified.
If column-name values are listed, these columns must be updatable (SQLSTATE 42829).
Note that listing columns has only documentary effect and does not limit subsequent searched update statements from modifying other columns.
EXEC SQL SELECT MAX(SALARY)
INTO :MAXSALARY
FROM EMP;
EXEC SQL SELECT * INTO :h1, :h2, :h3, :h4
FROM EMP
WHERE EMPNO = '528671';
#sql { SELECT * INTO :FIRSTNAME, :LASTNAME, :EMPNO, :SALARY
FROM EMP
WHERE EMPNO = '528671'
FOR UPDATE };
EXEC SQL SELECT MAX(SALARY)
INTO GV_MAXSALARY
FROM EMP;