oracle sql : “get or insert” stored procedure

I would like to do a stored procedure that receive an input param and then

• if targeted table does not contains that value, a new row is created and then the id of the created row is returned
• if targeted table already contain input param, the id of the row is returned

For moment I only manage to insert new row only if input param is new:

--exemple of table with a primary id a column with value
create table unique_number_table  (
 id             NUMBER(12)   not null,
 UNIQUE_NUMBER VARCHAR2(80)  not null,

 constraint PK_ID primary key (ID)
);

    create sequence SEQ_NUMBER
    INCREMENT BY 1
    START WITH 2
    MAXVALUE 999999999 
    MINVALUE 0; 

   create or replace procedure insert_or_get_unique_number ( input_number in varchar ) is     
    begin 
    insert into unique_number_table (id, UNIQUE_NUMBER) 
    select SEQ_NUMBER.NEXTVAL ,input_number
    from dual
    where not exists(select *  from unique_number_table 
                 where UNIQUE_NUMBER =input_number);   
     end insert_or_get_unique_number;

Do you know how to do this?

Answer

Seems to me like you want a stored function and not a procedure.

create or replace function insert_or_get_unique_number (input_number varchar2)
   return UNIQUE_NUMBER_TABLE.ID%type
is
  L_NUM  UNIQUE_NUMBER_TABLE.ID%type;
begin
   select ID
     into L_NUM
     from UNIQUE_NUMBER_TABLE
    where UNIQUE_NUMBER = input_number;
   return L_NUM;
exception
   when NO_DATA_FOUND then
      insert into unique_number_table (id, UNIQUE_NUMBER)
      values (SEQ_NUMBER.NEXTVAL, input_number)
      returning ID into L_NUM;
      return L_NUM;
end insert_or_get_unique_number;