The SELECT
INTO
statement retrieves data from one or more database tables, and assigns the selected values to variables or collections. For a full description of the SELECT
SQL statement, see Oracle Database SQL Reference.
In its default usage (SELECT ... INTO
), this statement retrieves one or more columns from a single row. In its bulk usage (SELECT ... BULK COLLECT INTO
), this statement retrieves an entire result set at once.
select into statement ::=
select item ::=
Keyword and Parameter Description
alias
Another (usually short) name for the referenced column, table, or view.
Stores result values in one or more collections, for faster queries than loops with FETCH
statements. For more information, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".
collection_name
A declared collection into which select_item
values are fetched. For each select_item
, there must be a corresponding, type-compatible collection in the list.
function_name
A user-defined function.
host_array_name
An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which select_item
values are fetched. For each select_item
, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.
numeric_literal
A literal that represents a number or a value implicitly convertible to a number.
parameter_name
A formal parameter of a user-defined function.
record_name
A user-defined or %ROWTYPE
record into which rows of values are fetched. For each select_item
value returned by the query, there must be a corresponding, type-compatible field in the record.
rest_of_statement
Anything that can follow the FROM
clause in a SQL SELECT
statement (except the SAMPLE
clause).
schema_name
The schema containing the table or view. If you omit schema_name
, Oracle assumes the table or view is in your schema.
subquery
A SELECT
statement that provides a set of rows for processing. Its syntax is similar to that of select_into_statement
without the INTO
clause.
table_reference
A table or view that must be accessible when you execute the SELECT
statement, and for which you must have SELECT
privileges. For the syntax of table_reference
, see "DELETE Statement".
TABLE (subquery2)
The operand of TABLE
is a SELECT
statement that returns a single column value, which must be a nested table or a varray. Operator TABLE
informs Oracle that the value is a collection, not a scalar value.
variable_name
A previously declared variable into which a select_item
value is fetched. For each select_item
value returned by the query, there must be a corresponding, type-compatible variable in the list.
By default, a SELECT
INTO
statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS
and the values of the variables in the INTO
clause are undefined. Make sure your WHERE
clause is specific enough to only match one row
If no rows are returned, PL/SQL raises NO_DATA_FOUND
. You can guard against this exception by selecting the result of an aggregate function, such as COUNT(*)
or AVG()
, where practical. These functions are guaranteed to return a single value, even if no rows match the condition.
A SELECT ... BULK COLLECT INTO
statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.
The implicit cursor SQL
and its attributes %NOTFOUND
, %FOUND
, %ROWCOUNT
, and %ISOPEN
provide information about the execution of a SELECT
INTO
statement.
Example 13-4 shows various ways to use the SELECT
INTO
statement.
Example 13-4 Using the SELECT INTO Statement
DECLARE deptid employees.department_id%TYPE; jobid employees.job_id%TYPE; emp_rec employees%ROWTYPE; TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; all_emps emp_tab; BEGIN SELECT department_id, job_id INTO deptid, jobid FROM employees WHERE employee_id = 140; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid || ', Job Id: ' || jobid); END IF; SELECT * INTO emp_rec FROM employees WHERE employee_id = 105; SELECT * BULK COLLECT INTO all_emps FROM employees; DBMS_OUTPUT.PUT_LINE('Number of rows: ' || SQL%ROWCOUNT); END; /
For examples, see the following: