Topics

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

select_into_statement ::=

Description of the illustration select_into_statement.eps

( bulk_collect_into_clause ::=, into_clause ::=, Oracle Database SQL Language Reference for select_list syntax)

Semantics

select_into_statement

DISTINCT | UNIQUE

Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.

Restrictions on DISTINCT and UNIQUE Queries

• The total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE.

• You cannot specify DISTINCT if the select_list contains LOB columns.

ALL

(Default) Causes the database to return all rows selected, including all copies of duplicates.

select_list

If the SELECT INTO statement returns no rows, PL/SQL raises the predefined 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.

into_clause

With this clause, the SELECT INTO statement retrieves one or more columns from a single row and stores them in either one or more scalar variables or one record variable. For more information, see "into_clause ::=".

bulk_collect_into_clause

With this clause, the SELECT INTO statement retrieves an entire result set and stores it in one or more collection variables. For more information, see "bulk_collect_into_clause ::=".

rest_of_statement

Anything that can follow the keyword FROM in a SQL SELECT statement, described in Oracle Database SQL Language Reference.

Examples

• Example 2-25, "Assigning Value to Variable with SELECT INTO Statement"

• Example 5-50, "SELECT INTO Assigns Values to Record Variable"

• Example 6-37, "ROLLBACK Statement"

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

• Example 6-43, "Declaring Autonomous Function in Package"

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

• Example 12-16, "Bulk-Selecting Two Database Columns into Two Nested Tables"

• Example 12-17, "Bulk-Selecting into Nested Table of Records"

• Example 12-21, "Limiting Bulk Selection with ROWNUM, SAMPLE, and FETCH FIRST"

Related Topics

In this chapter:

• "Assignment Statement"

• "FETCH Statement"

• "%ROWTYPE Attribute"

In other chapters:

• "Assigning Values to Variables with the SELECT INTO Statement"

• "Using SELECT INTO to Assign a Row to a Record Variable"

• "Processing Query Result Sets With SELECT INTO Statements"

• "SELECT INTO Statement with BULK COLLECT Clause"