Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
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
PDF · Mobi · ePub

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 an EXECUTE IMMEDIATE statement.

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

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

Keyword and Parameter Descriptions

BULK COLLECT INTO

Used only for a statement that returns multiple rows, this clause specifies one or more collections in which to store the returned rows. This clause must have a corresponding, type-compatible collection_item or :host_array_name for each select_item in the statement to which the RETURNING INTO clause belongs.

For the reason to use this clause, see Table 12-0, "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".

collection_name

The name of a declared collection, into which returned rows are stored.

host_array_name

An array into which returned rows are stored. The array must be declared in a PL/SQL host environment and passed to PL/SQL as a bind argument (hence the colon (:) prefix).

INTO

Used only for a statement that returns a single row, this clause specifies the variables or record into which the column values of the returned row are stored. This clause must have a corresponding, type-compatible variable or record field for each select_item in the statement to which the RETURNING INTO clause belongs.

multiple_row_expression

An expression that returns multiple rows of a table.

record_name

A record into which a returned row is stored.

single_row_expression

An expression that returns a single row of a table.

variable_name

Either the name of a variable into which a column value of the returned row is stored, or the name of a cursor variable that is declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The data type of the cursor variable is compatible with the return type of any PL/SQL cursor variable.

Usage

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 a Boolean 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