SELECT INTO
Any SELECT statement that the system accepts can be used in SQL Script as a standalone SQL Script statement, as long as it uses the SELECT INTO format.
Syntax
SELECT <projections> INTO <varListOrRowVariable>
FROM . . .
Remarks
• A standalone SELECT statement without the INTO clause is disallowed and discarded by the optimizer because it would do nothing to the program state.
• Variables are allowed in a SQL statement anywhere a literal of the same type is allowed.
• The BOOLEAN and ROW types are not supported in SQL.
• There is no special syntax for noting that something is a variable instead of a column in SQL statements, so be cautious when declaring a variable’s name. If there is a conflict, the name is interpreted as a column name and not a variable name.
• When using SELECT INTO, the cursor must return a single row. If it returns no rows or multiple rows, an exception is raised.
• Use of SELECT INTO is sometimes called an “implicit cursor” because it is opened, fetches one row, and is closed in one statement.
Example
PROCEDURE selinto_ex ( )
BEGIN
DECLARE a INTEGER;
DECLARE b DATE;
SELECT col1, col2 INTO a, b FROM T WHERE x = 1;
END