Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table FOO (
    ai_id number primary key,
    name varchar(20),
    bar varchar(20),
    CONSTRAINT foo_uk_name UNIQUE (name)
);
create sequence  FOO_seq;
create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  :new.ai_id := FOO_seq.nextval;
end;
/
create PROCEDURE UPSERT_FOO(
  name_input IN VARCHAR2,
  bar_input IN VARCHAR2
) as
begin
  merge into foo
  using (
    select name_input as name, bar_input as bar from dual
  ) src
  on (foo.name = src.name)
  when matched then
    update set foo.bar = src.bar
  when not matched then
    insert (name, bar)
    values (src.name, src.bar);
end;
/
begin
  UPSERT_FOO('a', 'abc');
  UPSERT_FOO('a', 'bcd');
  UPSERT_FOO('a', 'cde');
  UPSERT_FOO('a', 'def');
  UPSERT_FOO('b', 'xyz');
end;
/
1 rows affected

select * from foo;


AI_ID
NAME
BAR


1
a
def

2
b
xyz