Use RETURNING Clause to Avoid Unnecessary SQL Statements

View All Scripts Login to Run Script

Script Name Use RETURNING Clause to Avoid Unnecessary SQL Statements
Description 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.
Area PL/SQL General
Contributor Steven Feuerstein
Created Friday April 01, 2016

Statement 1

Create Some Test Data

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

Table created.

Statement 2

BEGIN
   INSERT INTO parts
        VALUES (1, 'Mouse');

   INSERT INTO parts
        VALUES (100, 'Keyboard');

   INSERT INTO parts
        VALUES (500, 'Monitor');

   COMMIT;
END;

Statement processed.

Statement 3
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.
Which rows did I update? (the wrong way)
```
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;
```
```
Statement processed.
100
```
Statement 4
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.
Which rows did I update? (the right way)
```
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;
```
```
Statement processed.
100
```
Statement 5
Take a snapshot of the table before changing it.
Or maybe we could do this....
```
CREATE TABLE parts_copy 
AS 
   SELECT * FROM parts
```
```
Table created.
```
Statement 6
This approach gets the job done, but at what price? This is truly overkill and a good example of how over-reliance on SQL can result in a more complicated and slower solution. I make a copy of my table, perform the update, and then use the MINUS SQL operator to find all the rows that are changed. Like I say, it gets the job done, but you would be much, much better off using the RETURNING clause. Beyond the issue of performance and complexity, this solution also assumes that no one else using the application will have made changes to the parts table. That's something you can assume safely in the context of the PL/SQL Challenge quiz. In the real world, however, this assumption will almost surely be wrong.
What rows were changed? Let's MINUS that! (ugh)
```
BEGIN 
   UPDATE parts 
      SET part_name = part_name || '1'; 
 
   FOR rec IN (SELECT part_number, part_name FROM parts 
               MINUS 
               SELECT part_number, part_name FROM parts_copy) 
   LOOP 
      DBMS_OUTPUT.put_line (rec.part_number); 
   END LOOP; 
END;
```
```
Statement processed.
1
100
500
```

Statement 7

If your non-query DML statement changes (or might change) more than one row, you will want to add BULK COLLECT to your RETURNING INTO clause and populate an array with information from each changed row.

Use RETURNING with BULK COLLECT INTO when changing multiple rows

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;

Statement processed.
1
100
500

Statement 8

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.

Return an entire row? Not with 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 
        INTO l_part; 
 
   DBMS_OUTPUT.put_line (l_part.part_name); 
END;

ORA-06550: line 7, column 14:
PL/SQL: ORA-00904: "ROW": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 9

Sorry, you must list each column, with compatible number and type to the fields of the "receiving" record.

Populate record in RETURNING with list of columns

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;

Statement processed.
MOUSE11

Statement 10

More test data

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

Table created.

Statement 11

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;

Statement processed.

Statement 12

Sure, you could execute ANOTHER SQL statement to retrieve that information, using group functions. Or....

Need aggregate information about impact of DML?

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;

Statement processed.
16000

Statement 13

Use RETURNING to get back all the modified salaries. Then iterate through them, summing up the total along the way. Hmmm. That's a lot of code to write to do a SUM operation. Maybe....

You could perform a computation in PL/SQL....

DECLARE 
   l_salaries   DBMS_SQL.number_table; 
   l_total      INTEGER := 0; 
BEGIN 
      UPDATE employees 
         SET salary = salary * 2 
       WHERE INSTR (last_name, 'e') > 0 
   RETURNING salary 
        BULK COLLECT INTO l_salaries; 
 
   FOR indx IN 1 .. l_salaries.COUNT 
   LOOP 
      l_total := l_total + l_salaries (indx); 
   END LOOP; 
 
   DBMS_OUTPUT.put_line (l_total); 
END;

Statement processed.
32000

Statement 14

Yes! You can call SUM, COUNT, etc. directly in the RETURNING clause and thereby perform analytics before you return the data back to your PL/SQL block. Very cool.

Call aggregate function in 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;

Statement processed.
64000

Statement 15

You can also take advantage of the RETURNING clause when executing a dynamic SQL statement!

Use RETURNING with EXECUTE IMMEDIATE

DECLARE  
   l_part_number   parts.part_number%TYPE;  
BEGIN  
   EXECUTE IMMEDIATE 
   q'[UPDATE parts  
         SET part_name = part_name || '1' 
       WHERE part_number = 100 
      RETURNING part_number INTO :one_pn]'       
   RETURNING INTO l_part_number;  
  
   DBMS_OUTPUT.put_line (l_part_number);   
END;

Statement processed.
100

Statement 16

In this variation you see how to use RETURNING with a dynamic SQL statement that modifies more than one row.

RETURNING Multiple Rows in EXECUTE IMMEDIATE

DECLARE  
   l_part_numbers   DBMS_SQL.number_table;  
BEGIN  
   EXECUTE IMMEDIATE 
   q'[UPDATE parts  
         SET part_name = part_name || '1' 
      RETURNING part_number INTO :pn_list]'       
   RETURNING 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;

Statement processed.
-1
100
500

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database