Wednesday, June 19, 2024

DML RETURNING Clause Enhancements in Oracle Database 23ai

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

Related Posts

0 comments:

Post a Comment