Oracle insert or update or throw exception

Question

There is a table 'EXAMPLE_TABLE' which contains two columns. The first column 'ID' store value '5555' and the second 'IS_EXIST' store char 1 byte '0'. How create a procedure which will do 'INSERT INTO' if this values doesn't exist, or 'UPDATE' if 'ID' the same as in a query and 'IS_EXIST' == 0, or throw some exception which will be handled in java if 'ID' the same and 'IS_EXIST' != 0. I considered the merge and primarily insert ways to resolve this problem.

it have to approximately look like :

if(ID doesn't exist)
  insert into
if(ID exist and IS_EXIST equals 0)
  update 
else 
  throw Exception

but how this will look in procedure?

You are facing the classic "upsert" conundrum. I don't know what kind of solution Oracle implements, but it's a real issue sometimes for Postgresql users like myself. This might be helpful to you: http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table — pens-fan-69, May 06 '15 at 20:14
Duplicate... http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table — Mike, May 06 '15 at 20:17
i don't think so, in that case we consider if only one field equal — viavad, May 06 '15 at 20:24

score 1 · Accepted Answer · answered May 06 '15 at 20:23

This is a simple way to do it if you want to throw or raise some exception using procedure without merging:

procedure PC_INSERT_OR_UPDATE(P_ID number) as
cursor C_1 is
  select M.ID,
         C.IS_EXIST
    from MY_TABLE M
   where M.ID = P_ID;
MSG clob;
begin
for C in C_1 loop
    begin
        if C.ID is null then
            insert into MY_TABLE
                (ID,
                IS_EXIST)
            values
                (P_ID,
                1);
        elsif C.ID is not null and C.IS_EXIST = 0 then
            update MY_TABLE M
            set M.IS_EXIST = 1
            where M.ID = P_ID;
        else
            RAISE_APPLICATION_ERROR(-20001, 'My exception was raised');
        end if;
    exception
        when others then
          rollback;
          MSG := 'Error - ' || TO_CHAR(sqlcode) || ' - ' || sqlerrm;
    end;
end loop;
end;

thank. nice resolve very simple and clear. can you append others if they are? — viavad, May 06 '15 at 20:34
I didn't understand you. Can you explain what do you mean with append others? — Andrew Paes, May 06 '15 at 20:46
i mean post another ways, and will be this way work correct if ID have primary key ? — viavad, May 06 '15 at 20:50

Oracle insert or update or throw exception

1 Answers1