This article demonstrates the enhancements to the DML RETURNING clause introduced in Oracle database 23c.
◉ 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
0 comments:
Post a Comment