|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
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 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.
Keyword and Parameter Description
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 (FORALL, BULK COLLECT)".
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.
A user-defined function.
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.
A literal that represents a number or a value implicitly convertible to a number.
A formal parameter of a user-defined function.
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.
Anything that can follow the
FROM clause in a SQL
SELECT statement (except the
The schema containing the table or view. If you omit
schema_name, Oracle assumes the table or view is in your schema.
SELECT statement that provides a set of rows for processing. Its syntax is like that of
select_into_statement without the
INTO clause. See "SELECT INTO Statement".
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".
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.
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
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
AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition.
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
%ISOPEN provide information about the execution of a
The following example demonstrates using the
SELECT INTO statement to query a single value into a PL/SQL variable, entire columns into PL/SQL collections, or entire rows into a PL/SQL collection of records:
DECLARE howmany NUMBER; some_first employees.first_name%TYPE; some_last employees.last_name%TYPE; some_employee employees%ROWTYPE; TYPE first_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER; TYPE last_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER; first_names first_typ; last_names last_typ; CURSOR c1 IS SELECT first_name, last_name FROM employees; TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER; all_names name_typ; TYPE emp_typ IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; all_employees emp_typ; BEGIN -- Query a single value and store it in a variable. SELECT COUNT(*) INTO howmany FROM user_tables; dbms_output.put_line('This schema owns ' || howmany || ' tables.'); -- Query multiple columns from one row, and store them in variables. SELECT first_name, last_name INTO some_first, some_last FROM employees WHERE ROWNUM < 2; dbms_output.put_line('Random employee: ' || some_first || ' ' || some_last); -- Query a single row and store it in a record. SELECT * INTO some_employee FROM employees WHERE ROWNUM < 2; -- Query multiple columns from multiple rows, and store them in a collection -- of records. SELECT first_name, last_name BULK COLLECT INTO all_names FROM EMPLOYEES; -- Query multiple columns from multiple rows, and store them in separate -- collections. (Generally less useful than a single collection of records.) SELECT first_name, last_name BULK COLLECT INTO first_names, last_names FROM EMPLOYEES; -- Query an entire (small!) table and store the rows -- in a collection of records. Now you can manipulate the data -- in-memory without any more I/O. SELECT * BULK COLLECT INTO all_employees FROM employees; END; /