CODEWITHSUNDEEP
javaoracle-databaseplsql
viavad
viavad

Reputation: 373

Oracle insert or update or throw exception

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?

Upvotes: 1

Views: 886

Answers (1)

Andrew Paes
Andrew Paes

Reputation: 2012

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;

Upvotes: 1

Related Questions