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 rowsExact 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; /