Definition:
In Oracle PL/SQL, the RETURNING clause is used to return information about the effected rows after issuing Data Manipulation Language (DML) statements. This capability was added in Oracle version 10g. Prior to Oracle 10g, you would have had to issue additional statements to the database to implement the functionality provided by this feature.
The RETURNING clause has two important restrictions:
- It is only available for use on tables, materialized views, normal views based on a single table or an expression that is based on tables, materialized views, or normal views.
- The RETURNING clause is only valid on a single-set aggregate. A single set aggregate is DML which only affects a single row, or using an aggregate function in the RETURNING statement (such as SUM).
Example Syntax:
Used with the INSERT statement:
INSERT INTO <table> (c1, c2, .., cn)
VALUES (v1, v2, .., vn)
RETURNING <expression> INTO <variables>
Used with the UPDATE statement:
UPDATE <table> SET (c1) = (v1), (c2) = (v2), (cn) = (vn)
WHERE <condition>
RETURNING <expression> INTO <variables>
Used with the DELETE statement:
DELETE FROM <table>
WHERE <condition>
RETURNING <expression> INTO <variables>
Related Links:
Related Code Snippets:
