INTO statement retrieves values from one or more database tables (as the SQL
SELECT statement does) and stores them in variables (which the SQL
SELECT statement does not do).
INTO statement with the
COLLECT clause is vulnerable to aliasing, which can cause unexpected results. For details, see "SELECT BULK COLLECT INTO Statements and Aliasing".
Oracle Database SQL Language Reference for the syntax of the SQL
DISTINCT | UNIQUE
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
You cannot specify
DISTINCTif the select_list contains LOB columns.
(Default) Causes the database to return all rows selected, including all copies of duplicates.
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.
With this clause, the
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 ::=".
With this clause, the
INTO statement retrieves an entire result set and stores it in one or more collection variables. For more information, see "bulk_collect_into_clause ::=".
Anything that can follow the keyword
FROM in a SQL
SELECT statement, described in Oracle Database SQL Language Reference.
Example 3-25, "Assigning Value to Variable with SELECT INTO Statement"
Example 6-53, "SELECT INTO Assigns Values to Record Variable"
Example 7-37, "ROLLBACK Statement"
Example 7-38, "SAVEPOINT and ROLLBACK Statements"
Example 7-43, "Declaring Autonomous Function in Package"
Example 8-20, "Validation Checks Guarding Against SQL Injection"
Example 13-16, "Bulk-Selecting Two Database Columns into Two Nested Tables"
Example 13-17, "Bulk-Selecting into Nested Table of Records"
Example 13-21, "Limiting Bulk Selection with ROWNUM, SAMPLE, and FETCH FIRST"
In this chapter:
In other chapters:
Oracle Database SQL Language Reference for information about the SQL