Oracle RETURNING Clause

• Tweet

• Tweet

The Oracle RETURNING clause was implemented as part of the Oracle 10g release and is used to return information about the effected rows after issuing Data Manipulation Language (DML) statements. Prior to Oracle 10g, you would have needed to work around not having this feature, most likely by issuing additional statements to the database.

The RETURNING clause has a few restrictions:

• it is only available for use on tables, materialised views, normal views based on a single table or an expression based on the previous three items
• it is only valid on a single-set aggregate. A single set aggregate is DML which only effects a single row or using an aggregate function in the RETURNING statement (such as SUM).

The general syntax for the RETURNING clause is:

INSERT INTO <table> (c1, c2, .., cn) VALUES (v1, v2, .., vn) RETURNING <expression> INTO <variables>
UPDATE <table> SET (c1) = (v1), (c2) = (v2), (cn) = (vn) WHERE <condition> RETURNING <expression> INTO <variables>
DELETE FROM <table> WHERE <condition> RETURNING <expression> INTO <variables>


This feature is particularly useful when INSERTing into a table, where the Primary Key is sourced from a sequence and fetched via a TRIGGER. In the below example, the ID of the newly inserted row is assigned to pContactID using the RETURNING clause. This is an elegant solution as it means you don’t have to SELECT the NEXTVAL from the sequence and assign that value to the ContactID during INSERT simply so you can return the new primary key value.

PROCEDURE Ins
(pContactID     OUT Contacts.ContactID%TYPE,
 pFirstname     IN Contacts.Firstname%TYPE,
 pSurname       IN Contacts.Surname%TYPE)
IS
BEGIN
 INSERT INTO Contacts
 (fname, sname)
 VALUES
 (pFirstname, pSurname)
 RETURNING ContactID INTO pContactID;
END;

You could just as easily use it to return the information about a row deleted, such as:

PROCEDURE Del
(pContactID     IN Contacts.ContactID%TYPE,,
 pFirstname     OUT Contacts.Firstname%TYPE,
 pSurname       OUT Contacts.Surname%TYPE)
IS
BEGIN
 DELETE FROM Contacts
 WHERE ContactID = pContactID
 RETURNING fname, sname INTO pFirstname, pSurname;
END;

Since the RETURNING clause is for use with aggregates, an example illustrating its use is in order. The below example modifies pContactID salary by pPercentageChange and subsequently returns the updated total company salary expenditure.

PROCEDURE UpdateSalary
(pContactID        IN Contacts.ContactID%TYPE,
 pPercentageChange IN NUMBER,
 pGrossSalary      OUT NUMBER)
IS
BEGIN
 UPDATE Contacts
 SET salary = salary * pPercentageChange
 WHERE ContactID = pContactID
 RETURNING SUM(salary) INTO pGrossSalary;
END;

The Oracle RETURNING clause provides the PL/SQL developer with a lot of flexibility. The real benefits however, come from the simplified PL/SQL and clarity gained in the code. If you’ve got a lot of application code or PL/SQL which isn’t utilising the power available to you – it might be time to undertake a clean up in your project.

Related posts:

1. Oracle Dynamic SQL Using The DECODE Function
2. Storing Time Information In Oracle
3. Oracle DECODE Function
4. Oracle ORA-04030 Application Errors
5. Oracle Derived Columns In SELECT Statements