RETURNING INTO Clause

The variables can be either individual variables or collections. If the statement affects no rows, then 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.

Note:

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

Topics

Syntax

static_returning_clause ::=

Description of the illustration static_returning_clause.eps

dynamic_returning_clause ::=

Description of the illustration dynamic_returning_clause.eps

into_clause ::=

Description of the illustration into_clause.eps

bulk_collect_into_clause ::=

Description of the illustration bulk_collect_into_clause.eps

Semantics

static_returning_clause

OLD | NEW

Given columns c1 and c2 in a table, you can specify OLD for column c1, (for example OLD c1). You can also specify OLD for a column referenced by a column expression (for example c1+OLD c2) or on a column referenced by an aggregate function (for example AVG(OLD c1)). When OLD is specified for a column, the column value before the execution of an associated INSERT, UPDATE, or DELETE statement is returned. In the case of a column referenced by a column expression, what is returned is the result from evaluating the column expression using the column value before the DML statement is executed.

NEW can be explicitly specified for a column, a column referenced in an expression, or a column referenced by an aggregate function to return a column value after the INSERT, UPDATE, or DELETE statement, or an expression result that uses the after execution value of a column.

When OLD and NEW are both omitted for a column or an expression, the post DML execution column value (pre-execution value for DELETE), or the expression result computed using the column values after DML execution, is returned.

Note that it is valid to specify OLD and NEW on constants (for example, OLD 1), however, the keywords are ignored. OLD and NEW are not currently supported on virtual columns.

Note:

While UPDATE statements have both before and after update column values, INSERT statements have no OLD column value and DELETE statements have no NEW column value. Although using OLD and NEW in these cases is valid, nothing is returned.

column

Expression whose value is the name of a column of a database 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

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

variable

Either the name of a scalar variable in which to store a column that the statement returns or the name of a host cursor variable that is declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Each select_list item in the statement must have a corresponding, type-compatible variable. The data type of a host cursor variable is compatible with the return type of any PL/SQL cursor variable.

bulk_collect_into_clause

Specifies one or more existing collections or host arrays in which to store the rows that the statement returns. For each select_list item in the statement, bulk_collect_into_clause must have a corresponding, type-compatible collection or host_array.

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

Restrictions 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.
You cannot use bulk_collect_into_clause in client programs.
When the statement that includes bulk_collect_into_clause requires implicit data type conversions, bulk_collect_into_clause can have only one collection or host_array.

collection

Name of a collection variable in which to store the rows that the statement returns.

Restrictions on collection

collection cannot be the name of an associative array that is indexed by a string.
When the statement requires implicit data type conversions, collection cannot be the name of a collection of a composite type.

:host_array

Name of an array declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Do not put space between the colon (:) and host_array.

Examples

Example 6-58, "UPDATE Statement Assigns Values to Record Variable"
Example 7-1, "Static SQL Statements"
Example 13-25, "Returning Deleted Rows in Two Nested Tables"
Example 13-26, "Returning NEW and OLD Values of Updated Rows"
Example 13-27, "DELETE with RETURN BULK COLLECT INTO in FORALL Statement"