RETURNING INTO Clause

The returning clause specifies the values return from DELETE, EXECUTE IMMEDIATE, INSERT, and UPDATE statements. You can retrieve the column values into individual variables or into collections. You cannot use the RETURNING clause for remote or parallel deletes. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined.

Syntax

returning clause ::=

Description of the illustration returning_clause.gif

Keyword and Parameter Description

BULK COLLECT

Stores result values in one or more collections, for faster queries than loops with FETCH statements. For more information, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".

collection_name

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

host_array_name

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

host_variable_name

A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

INTO ...

Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

multiple_row_expression

Expression that returns multiple rows of a table.

RETURNING | RETURN

Used only for DML statements that have a RETURNING clause (without a BULK COLLECT clause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the RETURNING INTO clause

single_row_expression

Expression that returns a single row of a table.

variable_name

A variable that stores a selected column value.

Usage

For DML statements that have a RETURNING clause, you can place OUT 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 replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".

Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, LOBs, instances of an object type, and refs. Dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be BOOLEANs or index-by tables. The only exception is that a PL/SQL record can appear in the INTO clause.

Examples

For examples, see the following:

Example 5-52, "Using the RETURNING Clause with a Record"
Example 6-1, "Data Manipulation With PL/SQL"
Example 7-5, "Dynamic SQL with RETURNING BULK COLLECT INTO Clause"
Example 7-6, "Dynamic SQL Inside FORALL Statement"
Example 11-15, "Using BULK COLLECT With the RETURNING INTO Clause"
Example 11-16, "Using FORALL With BULK COLLECT"