DML RETURNING Clause Enhancements in Oracle Database 23ai

This article demonstrates the enhancements to the DML RETURNING clause introduced in Oracle database 23ai.

Setup

The following tables are required to run the examples in this article.

drop table if exists t1;

create table t1 (

  id number,

  code varchar2(6),

  description varchar(25),

  constraint t1_pk primary key (id)

);

insert into t1 values (1, 'ONE', 'Description for ONE');

insert into t1 values (2, 'TWO', 'Description for TWO');

commit;

drop table if exists t2;

create table t2 (

  id          number,

  code        varchar2(6),

  description varchar(25),

  constraint t2_pk primary key (id)

);

insert into t2 values (3, 'THREE', 'Description for THREE');

insert into t2 values (4, 'FOUR', 'Description for FOUR');

commit;

Single Row Update

We can now return old and new values of columns from rows that are touched by an UPDATE statement.

( RETURN | RETURNING) ( OLD | NEW )  expr [ , ( OLD | NEW ) expr ]+ 

INTO  [data_item] ...

In this example we define a variables to hold the old and new values of the CODE and DESCRIPTION columns. We update the T1 table, appending "2" on to the end of the CODE and DESCRIPTION column values for the second row. The RETURNING clause defines the values we want to return, using the OLD and NEW keywords to indicate which value. The INTO clause lists the variables to hold those values.

set serveroutput on

declare

  l_old_code        t1.code%type;

  l_old_description t1.description%type;

  l_new_code        t1.code%type;

  l_new_description t1.description%type;

begin

  update t1

  set    code        = code || '2',

         description = description || '2'

  where  id          = 2

  returning old code , old description, new code, new description

  into l_old_code, l_old_description, l_new_code, l_new_description;

  dbms_output.put_line('l_old_code        = ' || l_old_code);

  dbms_output.put_line('l_old_description = ' || l_old_description);

  dbms_output.put_line('l_new_code        = ' || l_new_code);

  dbms_output.put_line('l_new_description = ' || l_new_description);

  rollback;

END;

/

l_old_code        = TWO

l_old_description = Description for TWO

l_new_code        = TWO2

l_new_description = Description for TWO2

PL/SQL procedure successfully completed.

SQL>

Multiple Row Update

We can do something similar for updates that touch multiple rows, but this time we need to use collections to hold the returned values. In this example we define a collection for each of the returned values. We update the table in a similar way, but this time we change INTO to a BULK COLLECT INTO and list the collections to hold the returned values. We loop through the collections to display the returned values.

set serveroutput on

declare

  type l_old_code_t is table of t1.code%type;

  type l_old_description_t is table of t1.description%type;

  type l_new_code_t is table of t1.code%type;

  type l_new_description_t is table of t1.description%type;

  l_old_code        l_old_code_t;

  l_old_description l_old_description_t;

  l_new_code        l_new_code_t;

  l_new_description l_new_description_t;

begin

  update t1

  set    code        = code || '1',

         description = description || '1'

  returning old code, old description, new code, new description

  bulk collect into l_old_code, l_old_description, l_new_code, l_new_description;

  for i in 1 .. l_old_code.count loop

    dbms_output.put_line('row               = ' || i);

    dbms_output.put_line('l_old.code        = ' || l_old_code(i));

    dbms_output.put_line('l_old.description = ' || l_old_description(i));

    dbms_output.put_line('l_new.code        = ' || l_new_code(i));

    dbms_output.put_line('l_new.description = ' || l_new_description(i));

  end loop;

  rollback;

end;

/

row               = 1

l_old.code        = ONE

l_old.description = Description for ONE

l_new.code        = ONE1

l_new.description = Description for ONE1

row               = 2

l_old.code        = TWO

l_old.description = Description for TWO

l_new.code        = TWO1

l_new.description = Description for TWO1

PL/SQL procedure successfully completed.

SQL>

Insert and Delete

The NEW and OLD keywords can be used on INSERT and DELETE also, but they don't bring anything new to the table. For inserts we have no old values, and for deletes we have no new values.

set serveroutput on

declare

  l_old_code        t1.code%type;

  l_old_description t1.description%type;

  l_new_code        t1.code%type;

  l_new_description t1.description%type;

begin

  insert into t1 (id, code, description)

  values (5, 'FIVE', 'Description for FIVE')

  returning old code , old description, new code, new description

  into l_old_code, l_old_description, l_new_code, l_new_description;

  dbms_output.put_line('l_old_code        = ' || l_old_code);

  dbms_output.put_line('l_old_description = ' || l_old_description);

  dbms_output.put_line('l_new_code        = ' || l_new_code);

  dbms_output.put_line('l_new_description = ' || l_new_description);

  rollback;

END;

/

l_old_code        =

l_old_description =

l_new_code        = FIVE

l_new_description = Description for FIVE

PL/SQL procedure successfully completed.

SQL>

set serveroutput on

declare

  l_old_code        t1.code%type;

  l_old_description t1.description%type;

  l_new_code        t1.code%type;

  l_new_description t1.description%type;

begin

  delete from t1

  where  id = 2

  returning old code , old description, new code, new description

  into l_old_code, l_old_description, l_new_code, l_new_description;

  dbms_output.put_line('l_old_code        = ' || l_old_code);

  dbms_output.put_line('l_old_description = ' || l_old_description);

  dbms_output.put_line('l_new_code        = ' || l_new_code);

  dbms_output.put_line('l_new_description = ' || l_new_description);

  rollback;

END;

/

l_old_code        = TWO

l_old_description = Description for TWO

l_new_code        =

l_new_description =

PL/SQL procedure successfully completed.

SQL>

Source: oracle-base.com