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 clause, this statement retrieves an entire result set into one or more collections. The SELECT INTO statement with the BULK COLLECT clause is faster than equivalent loops with FETCH statements. For more information, see "Reducing Loop Overhead with Bulk SQL".

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

select_into_statement ::=

Description of the illustration select_into_statement.gif

See table_reference ::=.

select_item ::=

Description of the illustration select_item.gif

See function_call ::=.

table_reference ::=

Description of the illustration table_reference.gif

Semantics

alias

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

BULK COLLECT

Enables the SELECT INTO statement to return multiple rows, which it stores in one or more collections of records (which must already exist). You can declare associative arrays or nested tables that grow as needed to hold the entire result set.

Without BULK COLLECT, the 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.

numeric_literal

A literal that represents a number or a value that can be implicitly converted to a number (see Table 3-10, "Possible Implicit PL/SQL Data Type Conversions").

record_name

A user-defined or %ROWTYPE record into which rows of values are selected. The record must have a corresponding, type-compatible field for each select_item.

rest_of_statement

Anything that can follow the FROM clause in a SQL SELECT statement (except the SAMPLE clause). For the syntax of the SQL SELECT statement, see Oracle Database SQL Language Reference.

schema_name

The name of the schema that contains the table or view. The default is your own schema.

select_item

If the SELECT INTO statement returns no rows, PL/SQL raises the exception NO_DATA_FOUND. To guard against this exception, select the result of the aggregate function COUNT(*), which returns a single value even if no rows match the condition.

subquery

A SQL SELECT statement (not a PL/SQL SELECT INTO statement) that provides a set of rows for processing.

subquery2

A SQL SELECT statement (not a PL/SQL SELECT INTO statement) that returns a single column value, which must be either a nested table or a varray. (The operator TABLE tells the database that the value is a collection, not a scalar value.)

table_name

The name of a database table.

table_reference

A reference to a table or view for which you have the SELECT privilege, which is accessible when you run the SELECT INTO statement.

variable_name

The name of a 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. With BULK COLLECT, variable_name can be the name of a collection of records.

Restriction on variable_name You cannot select into a BOOLEAN variable.

view_name

The name of a database view.

Examples

• Example 2-26, "SELECT INTO Assigns Values to Variables"

• Example 6-2, "CURRVAL and NEXTVAL Pseudocolumns"

• Example 6-40, "ROLLBACK Statement"

• Example 6-41, "SAVEPOINT and ROLLBACK Statements"

• Example 6-48, "Declaring an Autonomous Function in a Package"

• Example 7-13, "Validation Checks Guarding Against SQL Injection"

Related Topics

In this chapter:

• "Assignment Statement"

• "FETCH Statement"

• "%ROWTYPE Attribute"

In other chapters:

• "Selecting At Most One Row (SELECT INTO Statement)"

• "Reducing Loop Overhead with Bulk SQL"