Sometimes when we need to insert values in a table from a select, some of the rows can exists or not in the new table. In this case maybe we need to update or insert in the table. The sentence merge help us to do that:
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];
You can see the entire documentation in the oracle docs here.
Unfortunatelly one of the biggest inconvenients of merge is that we don’t know which registers are been inserted and which are been updated. In Sql Server we have the “OUTPUT” clause to retrieve these values but in Oracle this clause does’t exists. So we will try to make something similar to this behaviour. Reviewing the idea posted by Adrian Billington in his article, I have created an improvement to be able to know which values are been updated or inserted, how many, etc.
We will have 2 procedures, one for initialize and another to reset and 3 functions (store, get total and get values).
CREATE OR REPLACE PACKAGE MergeOptions AS
PROCEDURE Initialize(v_num in integer);
Function GetCount(v_num in integer) RETURN NUMBER;
Function SetValue(v_num in integer, merge_val_in IN varchar2) return varchar2;
Function GetValues(v_num in integer) return varchar2;
PROCEDURE Reset;
END MergeOptions;
The idea is store the values instead of the counters as Adrian post and be able to store the values that we want (inserted, updated, deleted or multiple values).
We will have an array (the size depends how many values we want) with the values:
type typ is record(val varchar2(32767));
type tab is table of typ;
array_t tab := tab();
And the procedures to manage this values:
PROCEDURE Initialize(v_num in integer) is
begin
array_t.extend(v_num);
end Initialize;
PROCEDURE Reset is
begin
array_t := tab();
end Reset;
Function GetCount(v_num in integer)
RETURN NUMBER is
total number := 0;
begin
select nvl(max(rownum), 0)
into total
from (SELECT TRIM(REGEXP_SUBSTR(array_t(v_num).val, '[^;]+', 1, LEVEL)) AS UpdatedRows
FROM DUAL
CONNECT BY TRIM(REGEXP_SUBSTR(array_t(v_num).val,
'[^;]+',
1,
LEVEL)) IS NOT NULL) V
where V.UPdatedRows is not null;
return total;
end GetCount;
Function SetValue(v_num in integer, merge_val_in IN varchar2)
return varchar2 is
begin
array_t(v_num).val := array_t(v_num).val || merge_val_in || ';';
return merge_val_in;
end SetValue;
Function GetValues(v_num in integer)
return varchar2 is
begin
return array_t(v_num).val;
end GetValues;
This solution is available only for versions greater or equal to 10g. If you are using previous versions you should replace function GetCount to find how many times appear “;” for example.
Ok. Now is time to test it:
declare
begin
Mergeoptions2.Initialize(2);
Merge into ships S
using (select * from NewShips) NS
on (S.SHIPGUID = NS.Shipguid)
when matched then
update set S.LENGTH = NS.Length,
S.NAME = case Mergeoptions.SetValue(1, S.Shipguid) when null then S.NAME else S.NAME end
when not matched then
insert
(SHIPGUID, Name, Class, Length)
values
(Mergeoptions.SetValue(2, sys_guid()), NS.Name, NS.Class, NS.Length);
dbms_output.put_line('You have inserted ' || Mergeoptions.GetCount(1) || ' values. The values are: ' || Mergeoptions.GetValues(1));
dbms_output.put_line('You have updated ' || Mergeoptions.GetCount(2) || ' values. The values are: ' || Mergeoptions.GetValues(2));
end;
We want to know the Guid provided by oracle so we “keep” the guid when we insert the value in the merge. The problem here is, what happend with update? Guid is part of the on clause so we cannot update this value. We update another value with himself but calling the setvalue function to keep the data desired, so our value is registered in the array.
The final result is:
You have inserted 2 values. The values are: F0823C41AD74F038E0430A1FE534F038;F0823C41AD75F038E0430A1FE534F038;
You have updated 4 values. The values are: F0839C11332330DCE0430A1FE53430DC;F0839C11332430DCE0430A1FE53430DC;F0839C11332530DCE0430A1FE53430DC;F0839C11332630DCE0430A1FE53430DC;
Now we know how many rows are inserted and updated and the primary keys provides by the database. We can use this method to get other values, etc.
You can download the example here.
