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