Use RETURNING Clause to Avoid Unnecessary SQL Statements
1. Overview
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, in order to obtain the values of the changed columns. So RETURNING helps avoid another roundtrip to the database, another context switch in a PL/SQL block.
2. Technologies and Tools Used
The following technologies have been used to achieve this
- Oracle PL/SQL
3. Use Case
First, I will create a table to use in my scripts:
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;
/
Which rows did I update? (The wrong way)
The code below 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.
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;
4. Examples
Here I have some worked examples below,
Which rows did I update? (The right way)
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. If more than one row is or may be changed, you will need to also use BULK COLLECT.
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;
