Merge Oracle

Merge is used to combine one or more DML statements into one. Merge command introduced in Oracle 9i.

Syntax :-

merge into tablename
using (select …….)
on (join condition )
when not matched then
[insert/delete/update] command
when matched then
[insert/delete/update] command;

Example :-

Consider below scenario.
An Application try to add/update an employee details.Application has the Employee Name and Number but not sure details added to the system.

SQL> desc emp;
Name                                      Null?    Type
—————————————– ——– ————-
EMPNO                                     NOT NULL NUMBER(10)
ENAME                                     NOT NULL VARCHAR2(50)

SQL> select * from emp;

EMPNO ENAME
———- ————–
10        Bill
11         Solomon

Create or replace procedure process_emp
(
p_empno emp.empno%type,
p_ename emp.ename%type
)
is
l_count pls_integer;
Begin

select count(1) into l_count from emp
where empno = p_empno;
Exception
When no_data_found then
l_count := 0;
End;

If l_count = 0 then
insert into emp(empno,ename) values(p_empno,p_ename);
else
update emp set ename = p_ename where empno = p_empno;
end if;
commit;
End process_emp;

I am re-writing above procedure with merge command

Create or replace procedure process_emp_merge
(
p_empno emp.empno%type,
p_ename emp.ename%type
)
is
l_count pls_integer;
Begin
merge into emp e
using (select p_empno as empno,p_ename as ename from dual) d
on (e.empno = d.empno)
when not matched then
insert (empno,ename) values(d.empno,d.ename)
when matched then
update set ename = d.ename where empno = d.empno;
commit;
End process_emp_merge;

Running both procedure

Begin
process_emp(12,’Mark’);
Process_emp_merge(13,’John’);
End;
/

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPNO ENAME
———- ————
10      Bill
11      Solomon
12      Mark
13      John

Two Employees added. Now running again those procedure with ename in upper case

Begin
process_emp(12,’MARK’);
Process_emp_merge(13,’JOHN’);
End;
/

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPNO ENAME
———- ————
10       Bill
11       Solomon
12       MARK
 13       JOHN

Merge is very useful in setting basic configurations. ( Running sql sripts ). We can avoid duplicates upto some extend by using merge command instead of insert statements.

Limitation on Merge

1 You cannot update the column referenced in on-clause. (In above example empno )

2. Cannot use default when updating a view

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

3 thoughts on “Merge Oracle”

  1. merge is mainly used in ETL tool.it is used to select rows from one or more table for update or insert into one or more tables.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s