Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E17126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

RETURNING INTO Clause

The RETURNING INTO clause specifies the variables in which to store the values returned by the statement to which the clause belongs. The variables can be either individual variables or collections. If the statement does not affect any rows, the values of the variables are undefined.

The static RETURNING INTO clause belongs to a DELETE, INSERT, or UPDATE statement. The dynamic RETURNING INTO clause belongs to the EXECUTE IMMEDIATE statement.

Topics:

Syntax

static_returning_clause ::=

static_returning_clause
Description of the illustration static_returning_clause.gif

dynamic_returning_clause ::=

dynamic_returning_clause
Description of the illustration dynamic_returning_clause.gif

into_clause ::=

into_clause
Description of the illustration into_clause.gif

bulk_collect_into_clause ::=

bulk_collect_into_clause
Description of the illustration bulk_collect_into_clause.gif

Semantics

static_returning_clause

single_row_expression

An expression that returns a single row of a table.

multiple_row_expression

An expression that returns multiple rows of a table.

into_clause

Specifies the variables or record in which to store the column values that the statement returns.

Restriction on into_clause Use into_clause in dynamic_returning_clause if and only if dynamic_sql_stmt (which appears in "EXECUTE IMMEDIATE Statement") returns a single row.

record_name

The name of a record variable in which to store the row that the statement returns. For each select_item in the statement, the record must have a corresponding, type-compatible field.

variable_name

Either the name of a scalar variable in which to store a column that the statement returns or the name of a weak cursor variable that is declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. Each select_item in the statement must have a corresponding, type-compatible variable.

Restriction on variable_name The variable_name cannot be the name of a BOOLEAN variable.

bulk_collect_into_clause

Specifies one or more collections or host arrays in which to store the rows that the statement returns. For each select_item in the statement, bulk_collect_into_clause must have a corresponding, type-compatible collection_name or host_array_name.

For the reason to use this clause, see "Bulk SQL and Bulk Binding".

Restriction on bulk_collect_into_clause Use the bulk_collect_into_clause clause in dynamic_returning_clause if and only if dynamic_sql_stmt (which appears in "EXECUTE IMMEDIATE Statement") can return multiple rows.

collection_name

The name of a collection in which to store the rows that the statement returns.

Restrictions on collection_name 

host_array_name

The name of an array in which to store the rows that the statement returns. The array must be declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. Do not put space between the colon (:) and host_array_name.

Usage

You cannot use the RETURNING INTO clause for remote or parallel deletes.

For DML statements that have a RETURNING clause, you can place OUT bind arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.

At run time, bind arguments or define variables replace corresponding placeholders in the dynamic SQL statement. Every placeholder must be associated with a bind argument in the USING clause or RETURNING INTO clause (or both) or with a define variable in the INTO clause.

The value a of bind argument cannot be the literal TRUE, FALSE, or NULL. To pass the value NULL to the dynamic SQL statement, see "Uninitialized Variable for NULL in USING Clause".

Examples

Related Topics

In this chapter:

In other chapters: