Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2) Part Number E25519-05 |
|
|
PDF · Mobi · ePub |
The SELECT
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).
Caution:
TheSELECT
INTO
statement with the BULK
COLLECT
clause is vulnerable to aliasing, which can cause unexpected results. For details, see "SELECT BULK COLLECT INTO Statements and Aliasing".LNPLS1820Topics
LNPLS1325select_into_statement ::=
See:
LNPLS1326select_item ::=
See "function_call ::=".
LNPLS1327table_reference ::=
LNPLS1821select_into_statement
LNPLS1822DISTINCT or UNIQUE
Causes the database to return only one copy of each set of duplicate rows selected. Duplicate rows are those with matching values for each select_item
. These two keywords are synonymous.
LNPLS1823Restrictions on DISTINCT and UNIQUE
The total number of bytes in all select_item
expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE
.
No select_item
expression can contain a LOB column.
LNPLS1824ALL
(Default) Causes the database to return all rows selected, including all copies of duplicates.
Selects all columns.
LNPLS1826into_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".
LNPLS1329bulk_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".
LNPLS1338subquery
SQL SELECT
statement (not a PL/SQL SELECT
INTO
statement).
LNPLS1328alias
Another (usually short) name for the referenced column, table, or view.
LNPLS1336rest_of_statement
Anything that can follow the FROM
clause in a SQL SELECT
statement, described in Oracle Database SQL Language Reference.
LNPLS1827select_item
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.
LNPLS1828numeric_literal
Literal of a numeric data type.
LNPLS1337schema
Name of the schema that contains the table or view. Default: your schema.
LNPLS1829db_table_or_view
Name of a database table or view.
LNPLS1830column
Name of a column of db_table_or_view
.
Selects all columns of db_table_or_view
.
LNPLS1832sequence
Name of a sequence.
LNPLS1833CURRVAL
Current value in sequence
.
LNPLS1834NEXTVAL
Next value in sequence
.
LNPLS1835alias
Another (usually short) name for the referenced column, table, or view.
LNPLS1836table_reference
Reference to a table or view for which you have the SELECT
privilege, which is accessible when you run the SELECT
INTO
statement.
LNPLS1837schema
Name of the schema that contains the table or view. Default: your schema.
LNPLS1838table
Name of a database table.
LNPLS1839view
Name of a database view.
LNPLS1840PARTITION partition or SUBPARTITION subpartition
See Oracle Database SQL Language Reference.
LNPLS1841@dblink
Database link, described in Oracle Database SQL Language Reference. Do not put space between @ and dblink
.
Example 2-25, "Assigning Value to Variable with SELECT INTO Statement"
Example 5-45, "SELECT INTO Assigns Values to Record Variable"
Example 7-13, "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 and SAMPLE"
LNPLS1842In this chapter:
LNPLS1843In other chapters: