SELECT INTO v16
SELECT INTO statement is an SPL variation of the SQL
SELECT command. The differences are:
SELECT INTOassigns the results to variables or records where they can then be used in SPL program statements.
- The accessible result set of
SELECT INTOis at most one row.
Other than these differences, all of the clauses of the
SELECT command, such as
GROUP BY, and
HAVING, are valid for
These examples show two variations of
target is a comma-separated list of simple variables.
select_expressions and the remainder of the statement are the same as for the
SELECT command. The selected values must exactly match in data type, number, and order the structure of the target or a runtime error occurs.
record is a record variable that was previously declared.
If the query returns zero rows, null values are assigned to the targets. If the query returns multiple rows, the first row is assigned to the targets and the rest are discarded. ("The first row" isn't well-defined unless you used
In either case, where no row is returned or more than one row is returned, SPL throws an exception.
There is a variation of
SELECT INTOusing the
BULK COLLECTclause that allows a result set of more than one row that's returned into a collection. See SELECT BULK COLLECT for more information.
You can use the
WHEN NO_DATA_FOUND clause in an
EXCEPTION block to determine whether the assignment was successful, that is, at least one row was returned by the query.
This version of the
emp_sal_query procedure uses the variation of
SELECT INTO that returns the result set into a record. It also uses the
EXCEPTION block containing the
WHEN NO_DATA_FOUND conditional expression.
If the query is executed with a nonexistent employee number, the results appear as follows:
Another conditional clause useful in the
EXCEPTION section with
SELECT INTO is the
TOO_MANY_ROWS exception. If more than one row is selected by the
SELECT INTO statement, SPL throws an exception.
When the following block is executed, the
TOO_MANY_ROWS exception is thrown since there are many employees in the specified department:
See Exception handling for information on exception handling.