Oracle ACE Andrej Pashchenko recently did a blog post covering a nice addition in 23ai, the ability for MERGE to use the RETURNING clause.
Check his post for several examples, but as quick TL;DR, here is a simple example of the evolution.
My base will be all people in department 10 and 20 from the HR.EMPLOYEES
table.
SQL> create table emp_temp as
2 select * from hr.employees
3 where department_id in (10,20);
Table created.
and I will merge into it everyone from department 20 and 30 in HR.EMPLOYEES, so we will get some UPDATE activity (department 20) and INSERT activity
(department 30)
SQL> merge into emp_temp t
2 using ( select * from hr.employees
3 where department_id in (20,30) ) x
4 on (t.employee_id = x.employee_id)
5 when matched then
6 update set t.salary = x.salary
7 when not matched then
8 insert values
9 (x.employee_id,x.first_name
10 ,x.last_name,x.email
11 ,x.phone_number,x.hire_date
12 ,x.job_id,x.salary
13 ,x.commission_pct,x.manager_id
14 ,x.department_id );
Pre 23ai
Before 23ai, a MERGE works fine, but a MERGE-RETURNING bombs out with an error
SQL> select banner_full from v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.16.0.0.0
SQL> merge into emp_temp t
2 using ( select * from hr.employees
3 where department_id in (20,30) ) x
4 on (t.employee_id = x.employee_id)
5 when matched then
6 update set t.salary = x.salary
7 when not matched then
8 insert values
9 (x.employee_id,x.first_name
10 ,x.last_name,x.email
11 ,x.phone_number,x.hire_date
12 ,x.job_id,x.salary
13 ,x.commission_pct,x.manager_id
14 ,x.department_id );
8 rows merged.
SQL>
SQL> roll;
Rollback complete.
SQL>
SQL> declare
2 l_enums sys.odcinumberlist;
3 l_names sys.odcivarchar2list;
4 begin
5 merge into emp_temp t
6 using ( select * from hr.employees
7 where department_id in (20,30) ) x
8 on (t.employee_id = x.employee_id)
9 when matched then
10 update set t.salary = x.salary
11 when not matched then
12 insert values
13 (x.employee_id,x.first_name
14 ,x.last_name,x.email
15 ,x.phone_number,x.hire_date
16 ,x.job_id,x.salary
17 ,x.commission_pct,x.manager_id
18 ,x.department_id )
19 RETURNING employee_id, last_name
20 bulk collect into l_enums, l_names;
21 end;
22 /
RETURNING employee_id, last_name
*
ERROR at line 19:
ORA-06550: line 19, column 13:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored
23ai onwards
Now in 23ai, the RETURNING clause is allowed and we can get the rows returned by the operation.
SQL> set serverout on
SQL> declare
2 l_enums sys.odcinumberlist;
3 l_names sys.odcivarchar2list;
4 begin
5 merge into emp_temp t
6 using ( select * from hr.employees
7 where department_id in (20,30) ) x
8 on (t.employee_id = x.employee_id)
9 when matched then
10 update set t.salary = x.salary
11 when not matched then
12 insert values
13 (x.employee_id,x.first_name
14 ,x.last_name,x.email
15 ,x.phone_number,x.hire_date
16 ,x.job_id,x.salary
17 ,x.commission_pct,x.manager_id
18 ,x.department_id )
19 returning employee_id, last_name
20 bulk collect into l_enums, l_names;
21
22 for i in l_enums.first .. l_enums.last
23 loop
24 dbms_output.put_line('emp-'||l_enums(i)||':'||l_names(i));
25 end loop;
26 end;
27 /
emp-201:Martinez
emp-202:Davis
emp-118:Himuro
emp-115:Khoo
emp-116:Baida
emp-119:Colmenares
emp-114:Li
emp-117:Tobias
PL/SQL procedure successfully completed.
But that got me thinking – maybe we can use this feature to boost other features.
Using new MERGE to boost other functionality.
You might think having a RETURNING clause is just a nice convenience, however it immediately opens up opportunities to fill in feature “gaps” that people have been asking for over the many versions of Oracle Database since 9i when MERGE was introduced.
For example, the RETURNING clause in MERGE also supports the extended NEW and OLD syntax that is also introduced in 23ai. In the case below I can pick up the old and new values for any primary key (EMPLOYEE_ID) values.
SQL> set serverout on
SQL> declare
2 l_enums sys.odcinumberlist;
3 l_enums2 sys.odcivarchar2list;
4 begin
5 merge into emp_temp t
6 using ( select * from hr.employees
7 where department_id in (20,30) ) x
8 on (t.employee_id = x.employee_id)
9 when matched then
10 update set t.salary = x.salary
11 when not matched then
12 insert values
13 (x.employee_id,x.first_name
14 ,x.last_name,x.email
15 ,x.phone_number,x.hire_date
16 ,x.job_id,x.salary
17 ,x.commission_pct,x.manager_id
18 ,x.department_id )
19 returning old employee_id, new employee_id
20 bulk collect into l_enums, l_enums2;
21
22 for i in l_enums.first .. l_enums2.last
23 loop
24 dbms_output.put_line('emp-'||l_enums(i)||':'||l_enums2(i));
25 end loop;
26 end;
27 /
emp-201:201
emp-202:202
emp-:118
emp-:115
emp-:116
emp-:119
emp-:114
emp-:117
PL/SQL procedure successfully completed.
Why is that useful? Once I have old and new values for the primary keys, I can deduce that
- old and new present? It’s an update
- Just new present? It’s an insert
By flipping my varray’s into nested tables to get the MULTISET functionality, I can now also use the values returned to come up with separate INSERT and UPDATE rowcounts, rather than just having SQL%ROWCOUNT.
SQL> set serverout on
SQL> declare
2 type nt is table of number;
3 l_enums nt;
4 l_enums2 nt;
5 l_ins nt;
6 l_upd nt;
7 begin
8 merge into emp_temp t
9 using ( select * from hr.employees
10 where department_id in (20,30) ) x
11 on (t.employee_id = x.employee_id)
12 when matched then
13 update set t.salary = x.salary
14 when not matched then
15 insert values
16 (x.employee_id,x.first_name
17 ,x.last_name,x.email
18 ,x.phone_number,x.hire_date
19 ,x.job_id,x.salary
20 ,x.commission_pct,x.manager_id
21 ,x.department_id )
22 returning old employee_id, new employee_id
23 bulk collect into l_enums, l_enums2;
24
25 l_upd := l_enums2 multiset intersect l_enums;
26 dbms_output.put_line('update='||l_upd.count);
27 l_ins := l_enums2 multiset except l_enums;
28 dbms_output.put_line('insert='||l_ins.count);
29
30 end;
31 /
update=2
insert=6
Now that MERGE has a RETURNING clause, you might be hopeful that INSERT-SELECT would also get a RETURNING extension but unfortunately not (yet).
SQL> declare
2 l_enums sys.odcinumberlist;
3 l_names sys.odcivarchar2list;
4 begin
5 insert into emp_temp t
6 select * from hr.employees
7 where department_id = 30
8 returning employee_id, last_name
9 bulk collect into l_enums, l_names;
10
11 for i in l_enums.first .. l_enums.last
12 loop
13 dbms_output.put_line('emp-'||l_enums(i)||':'||l_names(i));
14 end loop;
15 end;
16 /
returning employee_id, last_name
*
ERROR at line 8:
ORA-06550: line 8, column 3:
PL/SQL: ORA-03049: SQL keyword 'RETURNING' is not syntactically valid following '...department_id in (20,30)
'
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored
However, since the WHEN MATCHED part of a MERGE command is optional, an INSERT command can (loosely) be re-phrased as an MERGE command. (There may be some differences between the the in the handling of duplicates here, but often you should be able to swap them out). For example, the INSERT above can be rewritten as a MERGE below where I have an always false condition which means every row will fall into the NOT MATCHED processing.
SQL> merge into emp_temp t
2 using ( select * from hr.employees
3 where department_id = 30 ) x
4 on (1=0)
5 when not matched then
6 insert values
7 (x.employee_id,x.first_name
8 ,x.last_name,x.email
9 ,x.phone_number,x.hire_date
10 ,x.job_id,x.salary
11 ,x.commission_pct,x.manager_id
12 ,x.department_id )
13 /
6 rows merged.
At this point, from what we have seen above, I can add a RETURNING clause to the MERGE in order to pick up the rows I’ve inserted. However, unfortunately, PLSQL did not like my coding hack
SQL> roll;
Rollback complete.
SQL> declare
2 l_enums sys.odcinumberlist;
3 l_names sys.odcivarchar2list;
4 begin
5 merge into emp_temp t
6 using ( select * from hr.employees
7 where department_id = 30 ) x
8 on (1=0)
9 when not matched then
10 insert values
11 (x.employee_id,x.first_name
12 ,x.last_name,x.email
13 ,x.phone_number,x.hire_date
14 ,x.job_id,x.salary
15 ,x.commission_pct,x.manager_id
16 ,x.department_id )
17 returning employee_id, last_name
18 bulk collect into l_enums, l_names;
19
20 for i in l_enums.first .. l_enums.last
21 loop
22 dbms_output.put_line('emp-'||l_enums(i)||':'||l_names(i));
23 end loop;
24 end;
25 /
declare
*
ERROR at line 1:
ORA-00932: expression is of data type -, which is incompatible with expected data type NUMBER
ORA-06512: at line 5
I played around for a while, and it turned out that the “1=0” in the ON condition was the cause of PLSQL’s complaint. Once I changed that to the more conventional column join, but still ensuring it was always false, the code now works.
SQL> declare
2 l_enums sys.odcinumberlist;
3 l_names sys.odcivarchar2list;
4 begin
5 merge into emp_temp t
6 using ( select * from hr.employees
7 where department_id = 30 ) x
8 on (x.employee_id = x.employee_id-1)
9 when not matched then
10 insert values
11 (x.employee_id,x.first_name
12 ,x.last_name,x.email
13 ,x.phone_number,x.hire_date
14 ,x.job_id,x.salary
15 ,x.commission_pct,x.manager_id
16 ,x.department_id )
17 returning employee_id, last_name
18 bulk collect into l_enums, l_names;
19
20 for i in l_enums.first .. l_enums.last
21 loop
22 dbms_output.put_line('emp-'||l_enums(i)||':'||l_names(i));
23 end loop;
24 end;
25 /
emp-114:Li
emp-115:Khoo
emp-116:Baida
emp-117:Tobias
emp-118:Himuro
emp-119:Colmenares
PL/SQL procedure successfully completed.
SQL>
And there we go. You can have INSERT-SELECT-RETURNING by using a MERGE command!
But…watch this space. We might have some even more powerful SQL extensions coming your way in future release updates which make things even easier.
Learning is not a spectator sport 
Got some thoughts? Leave a comment