sqlplsqloracle11gsql-merge

What’s wrong in this merge operation?


I am performing a MERGE operation in Oracle 11g, but they are returning more rows than the expected.

create table sales(product varchar2(20),month date, amount number(10))
insert into sales values('LG','01-Jan-17',20000);
insert into sales values('sony','01-Jan-18',22000);
insert into sales values('panasonic', '22-dec-17',18000);

create table sales_history(product varchar2(20),month date, amount number(10))
insert into sales_history values('sony', '22-dec-17',24000);
insert into sales_history values('panasonic', '22-dec-17',18000);

select * from sales;
select * from sales_history

merge into sales_history sh using(select product,month,amount from sales)s
on (s.product=sh.product)
when matched then update set sh.month=s.month,sh.amount=s.amount
when not matched then insert(sh.product,sh.month,sh.amount)
values(s.product,s.month,s.amount);

And I tried to do the same query in Pl/SQL which will give me the same results but it is returning more rows that is duplicated rows.Why is it so?

set serveroutput on
declare
s_product varchar2(20);
s_month date;
s_amount number(10);
p_product s_product%type;
m_month s_month%type;
a_amount s_amount%type;

cursor sc1 is
select product,month,amount from sales;
cursor shc2 is
select product,month,amount from sales_history;

begin
open sc1;
open shc2;
loop <<l1>>
fetch sc1 into s_product,s_month,s_amount;
fetch shc2 into p_product,m_month,a_amount;
if s_product = p_product then
  if s_month <> m_month then
  update sales_history set month = s_month where product = s_product;
  end if;
  if s_amount <> a_amount then
  update sales_history set amount = s_amount where product = s_product;
  end if;
else
  INSERT INTO  sales_history(product, month, amount)
  SELECT product, month, amount FROM sales;
  dbms_output.put_line('DATA IS UPDATED');
end if;
exit when sc1%notfound;
exit when shc2%notfound;
end loop l1;
close sc1;
close shc2;
end;

select * from sales_history

Solution

  • I have tried executing the mentioned case in Oracle 12c and it is working. Please see the output mentioned below:

    create table sales(product varchar2(20),month date, amount number(10))
    

    Affected rows: 0
    Time: 0.012s

    insert into sales values('LG','01-Jan-17',20000)
    

    Affected rows: 1
    Time: 0.003s

    insert into sales values('sony','01-Jan-18',22000)
    

    Affected rows: 1
    Time: 0.004s

    insert into sales values('panasonic', '22-dec-17',18000)
    

    Affected rows: 1
    Time: 0.003s

    create table sales_history(product varchar2(20),month date, amount number(10))
    

    Affected rows: 0
    Time: 0.004s

    insert into sales_history values('sony', '22-dec-17',24000)
    

    Affected rows: 1
    Time: 0.002s

    insert into sales_history values('panasonic', '22-dec-17',18000)
    

    Affected rows: 1
    Time: 0.003s

    merge into sales_history sh using(select product,month,amount from sales)s
    on (s.product=sh.product)
    when matched then update set sh.month=s.month,sh.amount=s.amount
    when not matched then insert(sh.product,sh.month,sh.amount)
    values(s.product,s.month,s.amount)
    

    Affected rows: 3
    Time: 0.015s