After an UPDATE, code often needs to know what changed: the old fare and the new one, for an audit entry or a message. Selecting before and after the update costs two extra queries. The RETURNING clause returns values of the changed rows directly, and in Oracle AI Database 26ai an UPDATE can return both the OLD and the NEW value of a column.
Code for This Guide
The main examples are in the examples/dml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
update table set ... where ... returning [old | new] column [, ...] into variable [, ...]; ... returning ... bulk collect into collection [, ...];
Without OLD or NEW, an UPDATE returns the new values. RETURNING works with INSERT, UPDATE, DELETE, and MERGE; BULK COLLECT INTO returns the values of many rows into collections.
Old and New Value of One Row
Example:
declare
v_old_fare tickets.fare%type;
v_new_fare tickets.fare%type;
begin
update tickets
set fare = fare + 25
where ticket_id = 1
returning old fare, new fare into v_old_fare, v_new_fare;
dbms_output.put_line('Fare changed from ' || v_old_fare || ' to ' || v_new_fare);
rollback;
end;
/Output:
Fare changed from 513.01 to 538.01 PL/SQL procedure successfully completed.
One statement updates the fare and returns both values. The block rolls back, so the sample data stays as it was.
Several Rows with BULK COLLECT
When the update changes several rows, BULK COLLECT INTO fills collections with the values of every row.
Example:
-- RETURNING with BULK COLLECT: old and new values of several rows
declare
type t_ids is table of tickets.ticket_id%type;
type t_fares is table of tickets.fare%type;
v_ids t_ids; v_old t_fares; v_new t_fares;
begin
update tickets set fare = round(fare * 1.1, 2)
where ticket_id in (1, 2, 3)
returning ticket_id, old fare, new fare bulk collect into v_ids, v_old, v_new;
for i in 1 .. v_ids.count loop
dbms_output.put_line(v_ids(i) || ': ' || v_old(i) || ' -> ' || v_new(i));
end loop;
rollback;
end;
/Output:
1: 513.01 -> 564.31 2: 125.04 -> 137.54 3: 130.69 -> 143.76 PL/SQL procedure successfully completed.
Each ticket's ID, old fare, and new fare arrive in three collections, in the same order. The block rolls back as well.
Things to Know
- RETURNING is a PL/SQL and client-driver feature: it returns into variables or bind variables, not into a result set.
- Without BULK COLLECT, the statement may change at most one row; more raise ORA-01422, and none leave the variables NULL.
- OLD and NEW also work for INSERT (where OLD is NULL) and DELETE (where NEW is NULL), keeping audit code uniform.
Related Guides
Conclusion
RETURNING returns values of the rows a DML statement changed, without another query, and Oracle AI Database 26ai adds OLD and NEW so an UPDATE returns values from before and after the change. Use BULK COLLECT INTO when several rows change.
