SQL%ROWCOUNT for merge

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.

Schreibe einen Kommentar