SELECT INTO Statement

The SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in either variables or a record (which the SQL SELECT statement does not do).

By default, the SELECT INTO statement retrieves one or more columns from a single row. With the BULK COLLECT INTO clause, this statement retrieves an entire result set at once.

Syntax

select_into_statement ::=

Description of the illustration select_into_statement.gif

select_item ::=

Description of the illustration select_item.gif

table_reference ::=

Description of the illustration table_reference.gif

Keyword and Parameter Descriptions

alias

Another (usually short) name for the referenced column, table, or view.

BULK COLLECT INTO

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 argument) 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, the database 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.

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 the database 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.

view_name

The name of a database view.

Usage Notes

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 the aggregate function COUNT(*), which returns 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.

Examples