SELECT INTO v10
SELECT INTO statement is an SPL variation of the SQL
SELECT command, the differences being:
SELECT INTOis designed to assign 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 the above, all of the clauses of the
SELECT command such as
WHERE, ORDER BY, GROUP BY, HAVING, etc. are valid for
SELECT INTO. The following are the 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 has previously been declared.
If the query returns zero rows, null values are assigned to the target(s). If the query returns multiple rows, the first row is assigned to the target(s) and the rest are discarded. (Note that "the first row" is not well-defined unless you’ve used
In either cases, 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 is returned into a collection. See SELECT BULK COLLECT for more information on using the
BULK COLLECTclause with the
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. Also note the addition of the
EXCEPTION block containing the
WHEN NO_DATA_FOUND conditional expression.
If the query is executed with a non-existent employee number the results appear as follows.
Another conditional clause of use 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 an exception is thrown by SPL.
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 more information on exception handling.