Oracle 23c - SQL

RETURNING Clause in Oracle Database 23c

/ Oracle DB 23c New Features, SQL 23c / By

Oracle Database 23c introduces a powerful enhancement to the RETURNING clause for DML statements, enabling developers to retrieve both old and new values directly within UPDATE statements. This feature streamlines application development, improves performance, and simplifies tasks like auditing and change data capture (CDC).

Understanding the Enhanced RETURNING Clause

The syntax of using this clause goes as follows:

UPDATE table_name
SET column1 = value1 [, column2 = value2]
[WHERE condition]
RETURNING (OLD | NEW) expr [, (OLD | NEW) expr] 
     INTO data_item [, data_item]

New Keywords:

  • OLD: Retrieves the value of a column before the update.
  • NEW: Retrieves the value of a column after the update.

Retrieving Old and New Values for a Single Row:

set serveroutput on
DECLARE
    old_value VARCHAR2(20);
    new_value VARCHAR2(20);
BEGIN
    UPDATE employees
    SET salary = salary + 500
    WHERE employee_id = 100
    RETURNING OLD salary, NEW salary INTO old_value, new_value;

    DBMS_OUTPUT.PUT_LINE('Old salary: ' || old_value);
    DBMS_OUTPUT.PUT_LINE('New salary: ' || new_value);
END;
/

Old salary: 40000
New salary: 40500

In Pre-23c, we would use SELECT statements before and after updates to retrieve values, which requires additional code and potentially multiple database interactions.

Retrieving Multiple Rows

Retrieving updated values for multiple rows involves collections. Define collections for each returned value, update the table using BULK COLLECT INTO, and then loop through the collections to access the data.

SET SERVEROUTPUT ON
DECLARE
  TYPE L_OLD_SALARY_T IS TABLE OF EMPLOYEES.salary%TYPE;
  TYPE L_NEW_SALARY_T IS TABLE OF EMPLOYEES.salary%TYPE;
  L_OLD_SALARY L_OLD_SALARY_T;
  L_NEW_SALARY L_NEW_SALARY_T;
BEGIN

  UPDATE employees
  SET salary = salary + 500
  RETURNING OLD salary, NEW salary
  BULK COLLECT INTO L_OLD_SALARY, L_NEW_SALARY;

  FOR I IN 1 .. L_OLD_SALARY.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('old salary        = ' || L_OLD_SALARY(I));
    DBMS_OUTPUT.PUT_LINE('new salary        = ' || L_NEW_SALARY(I));
  END LOOP;

  ROLLBACK;
END;
/


Old salary        = 60000
New salary        = 60500
Old salary        = 75000
New salary        = 75500
Old salary        = 40500
New salary        = 41000

This approach constrains us to individually pick the desired columns, which is cumbersome. It would be much better to have a way to fetch all columns in one go and store them in a single collection object.

Benefits

  • Streamlined Application Development: Simplifies code for tasks requiring old and new values.
  • Improved Performance: Reduces database calls and context switches.
  • Simplified Auditing and CDC: Enables efficient tracking of data changes.
  • Enhanced Flexibility: Provides a single construct for accessing old and new values across DML statements.

Conclusion

The enhanced RETURNING clause in Oracle Database 23c offers an efficient way to access both old and new values in UPDATE statements, enabling more streamlined application development, improved performance, and simplified data management tasks. Embrace this feature to enhance your Oracle development experience.

Scroll to Top