Skip Headers

PL/SQL User's Guide and Reference
Release 2 (9.2)

Part Number A96624-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

PL/SQL Language Elements, 45 of 52


SELECT INTO Statement

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 Oracle9i SQL Reference.

Syntax

Text description of select_into_statement.gif follows
Text description of the illustration select_into_statement.gif


Text description of select_item.gif follows
Text description of the illustration select_item.gif


Keyword and Parameter Description

alias

This is another (usually short) name for the referenced column, table, or view.

BULK COLLECT

This clause instructs the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. The SQL engine bulk-binds all collections referenced in the INTO list. For more information, see "Reducing Loop Overhead for Collections with Bulk Binds".

collection_name

This identifies a declared collection into which select_item values are bulk fetched. For each select_item, there must be a corresponding, type-compatible collection in the list.

function_name

This identifies a user-defined function.

host_array_name

This identifies an array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which select_item values are bulk fetched. For each select_item, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.

numeric_literal

This is a literal that represents a number or a value implicitly convertible to a number.

parameter_name

This identifies a formal parameter of a user-defined function.

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.

rest_of_statement

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

schema_name

This qualifier identifies the schema containing the table or view. If you omit schema_name, Oracle assumes the table or view is in your schema.

subquery

This is a SELECT statement that provides a set of rows for processing. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement".

table_reference

This identifies a table or view that must be accessible when you execute the SELECT statement, and for which you must have SELECT privileges. For the syntax of table_reference, see "DELETE Statement".

TABLE (subquery2)

The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table or a varray. Operator TABLE informs Oracle that the value is a collection, not a scalar value.

variable_name

This identifies a previously declared 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.

Usage Notes

The BULK COLLECT clause tells the SQL engine to bulk-bind output collections before returning them. It bulk-binds all collections referenced in the INTO list. The corresponding columns can store scalar or composite values including objects.

When you use a SELECT INTO statement without the BULK COLLECT clause, it should return only one row. If it returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS.

However, if no rows are returned, PL/SQL raises NO_DATA_FOUND unless the SELECT statement called a SQL aggregate function such as AVG or SUM. (SQL aggregate functions always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND.)

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

Examples

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;

In the following example, the SQL engine loads the entire empno and ename database columns into nested tables before returning the tables to the PL/SQL:

DECLARE
   TYPE NumTab IS TABLE OF emp.empno%TYPE;
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   enums NumTab;  -- no need to initialize
   names NameTab;
BEGIN
   SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
   ...
END;

Related Topics

Assignment Statement, FETCH Statement, %ROWTYPE Attribute


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback