create table merge_sql_rowcount(
dummy VARCHAR2(1),
x number,
y number
);
truncate table merge_sql_rowcount;
select * from merge_sql_rowcount;
insert into merge_sql_rowcount values ('X',1,0);
commit;
set serveroutput on
begin
merge into merge_sql_rowcount using dual on (merge_sql_rowcount.dummy = dual.dummy)
when matched then update set y = y+1
when not matched then insert (x,y) values ( dual.dummy, 0 );
if sql%rowcount > 0 then
dbms_output.put_line( sql%rowcount || ' rows affected...' );
end if;
end;
/
select * from merge_sql_rowcount;
drop table merge_sql_rowcount;
>Table MERGE_SQL_ROWCOUNT created.
>Table MERGE_SQL_ROWCOUNT truncated.
>no rows selected
>1 row inserted.
>Commit complete.
>PL/SQL procedure successfully completed.
>1 rows affected…
D X Y
– ———- ———-
X 1 1
>Table MERGE_SQL_ROWCOUNT dropped.