Don't Return Home Without the RETURNING Clause

[That title is, by the way, a fairly lame play on the American Express "Don't leave home without it." tagline. Sorry, can't help myself. SF]

The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING you would have to run a SELECT statement after the DML statement is completed to obtain the values of the changed columns. The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form. You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement.

I explore the feature below. You can execute the same code on LiveSQL.

Set Up Some Data

First, let's create some data with which to play.

CREATE TABLE parts
(
   part_number    INTEGER
 , part_name   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO parts VALUES (1, 'Mouse');
   INSERT INTO parts VALUES (100, 'Keyboard');
   INSERT INTO parts VALUES (500, 'Monitor');
   COMMIT;
END;
/

CREATE TABLE employees
(
   employee_id   INTEGER
 , last_name     VARCHAR2 (100)
 , salary        NUMBER
)
/

BEGIN
   INSERT INTO employees VALUES (100, 'Gutseriev', 1000);
   INSERT INTO employees VALUES (200, 'Ellison', 2000);
   INSERT INTO employees VALUES (400, 'Gates', 3000);
   INSERT INTO employees VALUES (500, 'Buffet', 4000);
   INSERT INTO employees VALUES (600, 'Slim', 5000);
   INSERT INTO employees VALUES (700, 'Arnault', 6000);
   COMMIT;
END;
/

The Basics of RETURNING

Suppose that after I update a part name, I need to find out which row was changed. Here's one way I could to this:

DECLARE
   l_num   PLS_INTEGER;
BEGIN
   UPDATE parts
      SET part_name = UPPER (part_name)
    WHERE part_name LIKE 'K%';

   SELECT part_number
     INTO l_num
     FROM parts
    WHERE part_name = UPPER (part_name);

   DBMS_OUTPUT.put_line (l_num);
END;
/

This solution issues the update and then in a separate SQL statement retrieves the part number of the row that was just modified - but only by reproducing the logic ("partname = UPPER (partname)") in the WHERE clause. This means that I have introduced repetition in my code, and also inefficiency (an extra context switch). This is logically equivalent to using the RETURNING clause, but definitely inferior to RETURNING. And keep in mind that if you use a SELECT after your DML statement to determine if the correct changes were made, you need to be very careful about how you specify the WHERE clause of your query to be sure that you identify the same rows that were (possibly) changed.

Now take a look at the next block.

DECLARE
   l_num   PLS_INTEGER;
BEGIN
      UPDATE parts
         SET part_name = UPPER (part_name)
       WHERE part_name LIKE 'K%'
   RETURNING part_number
        INTO l_num;

   DBMS_OUTPUT.put_line (l_num);
END;
/

Don't do an unnecessary SELECT simply to see/verify the impact of a non-query DML statement! Just add RETURNING to the statement and get information back from that single context switch between PL/SQL and SQL. Note that this RETURNING INTO only works because the WHERE clause identifies a single row for changing.

RETURNING with Multiple Rows Changed

Now suppose that I am (or could be) updating more than one row with my DML statement. For example, I will simply remove the WHERE clause from the above block. Let's see what happens when I execute it:

DECLARE
   l_num   PLS_INTEGER;
BEGIN
      UPDATE parts
         SET part_name = UPPER (part_name)
   RETURNING part_number
        INTO l_num;

   DBMS_OUTPUT.put_line (l_num);
END;
/

Oh no! I get an error:

ORA-01422: exact fetch returns more than requested number of rows

Exact fetch? Why is it talking about an exact fetch? Isn't this the same error I get when I do a SELECT-INTO that returns more than one row (that is, the TOO_MANY_ROWS exception)? Yes! And that's because RETURNING-INTO acts just like a SELECT-INTO. It expects just one row of information to be returned. So...if you are expecting more than one row, do the same thing you would do with SELECT-INTO: add BULK COLLECT!

DECLARE
   l_part_numbers   DBMS_SQL.number_table;
BEGIN
      UPDATE parts
         SET part_name = part_name || '1'
   RETURNING part_number
        BULK COLLECT INTO l_part_numbers;

   FOR indx IN 1 .. l_part_numbers.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_part_numbers (indx));
   END LOOP;
END;
/

RETURNING a Record's Worth

OK, so what if I want to return a whole record's worth of information? Can I use the ROW keyword?

DECLARE
   l_part   parts%ROWTYPE;
BEGIN
      UPDATE parts
         SET part_number = -1 * part_number
           , part_name = UPPER (part_name)
       WHERE part_number = 1
   RETURNING ROW      /* WILL NOT WORK */
        INTO l_part;

   DBMS_OUTPUT.put_line (l_part.part_name);
END;
/

No! You can "UPDATE table_name SET ROW =" to perform a record-level update, but you cannot use the ROW keyword in that same way in a RETURNING clause. Sorry, you must list each column, with compatible number and type to the fields of the "receiving" record, as you below.

DECLARE
   l_part   parts%ROWTYPE;
BEGIN
      UPDATE parts
         SET part_number = -1 * part_number
           , part_name = UPPER (part_name)
       WHERE part_number = 1
   RETURNING part_number, part_name
        INTO l_part;

   DBMS_OUTPUT.put_line (l_part.part_name);
END;
/

Aggregate Functions and RETURNING

What if I want to perform some operations on the data returned by the RETURNING? Well, fall back on first principles. From Tom Kyte: "Do it in SQL is possible." Suppose I need to get the total of salaries changed by my update statement. I could execute a SELECT after the UPDATE:

DECLARE
   l_total   INTEGER;
BEGIN
   UPDATE employees
      SET salary = salary * 2
    WHERE INSTR (last_name, 'e') > 0;

   SELECT SUM (salary)
     INTO l_total
     FROM employees
    WHERE INSTR (last_name, 'e') > 0;

   DBMS_OUTPUT.put_line (l_total);
END;
/

Not necessary! You can execute aggregate functions right inside the RETURNING clause!

DECLARE
   l_total   INTEGER;
BEGIN
      UPDATE employees
         SET salary = salary * 2
       WHERE INSTR (last_name, 'e') > 0
   RETURNING SUM (salary)
        INTO l_total;

   DBMS_OUTPUT.put_line (l_total);
END;
/