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 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.

Topics

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

static_returning_clause ::=

dynamic_returning_clause ::=

into_clause ::=

bulk_collect_into_clause ::=

Semantics

static_returning_clause

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_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 argument. Each select_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.

Restriction on variable variable cannot have the data type BOOLEAN.

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_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".

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 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 argument. Do not put space between the colon (:) and host_array.

Examples

• Example 5-47, "UPDATE Statement Assigns Values to Record Variable"

• Example 6-1, "Static SQL Statements"

• Example 12-25, "Returning Deleted Rows in Two Nested Tables"

• Example 12-26, "DELETE with RETURN BULK COLLECT INTO in FORALL Statement"

Related Topics

In this chapter:

• "DELETE Statement Extension"

• "EXECUTE IMMEDIATE Statement"

• "FETCH Statement"

• "SELECT INTO Statement"

• "UPDATE Statement Extensions"

In other chapters:

• "SQL Statements that Return Rows in PL/SQL Record Variables"

• "EXECUTE IMMEDIATE Statement"

• "RETURNING INTO Clause with BULK COLLECT Clause"