DBA Hub

📋Steps in this guide1/4

DML RETURNING Clause Enhancements in Oracle Database 23ai/26ai

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

oracle 23configurationintermediate
by OracleDba
17 views
1

Setup

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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'),
       (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'),
       (4, 'FOUR', 'Description for FOUR');
commit;
2

Single Row Update

We can now return old and new values of columns from rows that are touched by an statement. In this example we define a variables to hold the old and new values of the and columns. We update the table, appending "2" on to the end of the and column values for the second row. The clause defines the values we want to return, using the and keywords to indicate which value. The clause lists the variables to hold those values.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
( RETURN | RETURNING) ( OLD | NEW )  expr [ , ( OLD | NEW ) expr ]+ 
INTO  [data_item] ...

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>
3

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 to a and list the collections to hold the returned values. We loop through the collections to display the returned values.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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>
4

Insert and Delete

The and keywords can be used on and 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. For more information see: - UPDATE - DML RETURNING INTO Clause Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!