SELECT INTO Statement

The SELECT INTO statement retrieves data from one or more database tables, then assigns the selected values to variables or fields. For a full description of the SELECT statement, see Oracle9i SQL Reference.

Syntax

Text description of the illustration select_into_statement.gif

Text description of the illustration select_item.gif

Keyword and Parameter Description

alias

This is another (usually short) name for the referenced column, table, or view.

BULK COLLECT

This clause instructs the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. The SQL engine bulk-binds all collections referenced in the INTO list. For more information, see "Reducing Loop Overhead for Collections with Bulk Binds".

collection_name

This identifies a declared collection into which select_item values are bulk fetched. For each select_item, there must be a corresponding, type-compatible collection in the list.

function_name

This identifies a user-defined function.

host_array_name

This identifies an array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which select_item values are bulk 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

This is a literal that represents a number or a value implicitly convertible to a number.

parameter_name

This identifies a formal parameter of a user-defined function.

record_name

This identifies 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

This is anything that can legally follow the FROM clause in a SELECT statement except the SAMPLE clause.

schema_name

This qualifier identifies the schema containing the table or view. If you omit schema_name, Oracle assumes the table or view is in your schema.

subquery

This is a 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".

table_reference

This identifies 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

This identifies 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.

Usage Notes

The BULK COLLECT clause tells the SQL engine to bulk-bind output collections before returning them. It bulk-binds all collections referenced in the INTO list. The corresponding columns can store scalar or composite values including objects.

When you use a SELECT INTO statement without the BULK COLLECT clause, it should return only one row. If it returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS.

However, if no rows are returned, PL/SQL raises NO_DATA_FOUND unless the SELECT statement called a SQL aggregate function such as AVG or SUM. (SQL aggregate functions always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND.)

The implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of a SELECT INTO statement.

Examples

The following SELECT statement returns an employee's name, job title, and salary from the emp database table:

SELECT ename, job, sal INTO my_ename, my_job, my_sal FROM emp
   WHERE empno = my_empno;

In the following example, the SQL engine loads the entire empno and ename database columns into nested tables before returning the tables to the PL/SQL:

DECLARE
   TYPE NumTab IS TABLE OF emp.empno%TYPE;
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   enums NumTab;  -- no need to initialize
   names NameTab;
BEGIN
   SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
   ...
END;

Related Topics

Assignment Statement, FETCH Statement, %ROWTYPE Attribute