PL/SQL User's Guide and Reference

SELECT INTO Statement

Description

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 Oracle7 Server SQL Reference.

Syntax

select_into_statement ::=

SELECT [DISTINCT | ALL] {* | select_item[, select_item]...}
   INTO {variable_name[, variable_name]... | record_name}
   FROM {table_reference | (subquery)} [alias]
          [, {table_reference | (subquery)} [alias]]...
   rest_of_select_statement;

select_item ::=

{  function_name[(parameter_name[, parameter_name]...)]
 | NULL
 | numeric_literal
 | [schema_name.]{table_name | view_name}.*
 | [[schema_name.]{table_name. | view_name.}]column_name
 | sequence_name.{CURRVAL | NEXTVAL}
 | 'text'} [[AS] alias]

table_reference ::=

[schema_name.]{table_name | view_name}[@dblink_name]

Keyword and Parameter Description

select_item

This is a value returned by the SELECT statement, then assigned to the corresponding variable or field in the INTO clause.

variable_name[, variable_name]...

This identifies a list of previously declared scalar variables into which select_item values are fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible variable in the list.

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.

subquery

This is query that provides a value or set of values to the SELECT statement. The syntax of subquery is like the syntax of select_into_ statement, except that subquery cannot have an INTO clause.

alias

This is another (usually short) name for the referenced column, table, or view, and can be used in the WHERE clause.

rest_of_select_statement

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

Usage Notes

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

When you use a SELECT INTO statement to assign values to variables, it should return only one row. If it returns more than one row, you get the following results:

PL/SQL raises the predefined exception TOO_MANY_ROWS

SQLCODE returns -1422 (Oracle error code ORA-01422)

SQLERRM returns the Oracle error message single-row query returns more than one row

SQL%NOTFOUND yields FALSE

SQL%FOUND yields TRUE

SQL%ROWCOUNT yields 1

If no rows are returned, you get these results:

PL/SQL raises the predefined exception NO_DATA_FOUND unless the SELECT statement called a SQL group function such as AVG or SUM. (SQL group functions always return a value or a null. So, a SELECT INTO statement that calls a group function never raises NO_DATA_FOUND.)

SQLCODE returns +100 (Oracle error code ORA-01403)

SQLERRM returns the Oracle error message no data found

SQL%NOTFOUND yields TRUE

SQL%FOUND yields FALSE

SQL%ROWCOUNT yields 0

Example

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;