"If not exist"-condition in a case in SQL-procedure from Oracle-Database

Question

I´m kind of new to SQL-Procedures, but I´ve written a procedure which adds a line to my datatable. This works pretty fine, but when i run trough the procedure twice i want to avoid the line being added again into the datatable based on an Oracle-Database. So my question is, how to set the "if already exists"-condition within the case command.

My code looks like that:

...
case number

when 5 then 

/* if not exists` <<--- i need something to avoid doubled entrys */

  pi_event_line_add(5,'xxx' ....);


end case;
end procedure;

[Possible duplicate](http://stackoverflow.com/q/13217600/266304)? — Alex Poole, Jun 23 '16 at 11:13
add a unique constraint on the target table. This is the best solution. — Florin Ghita, Jun 23 '16 at 11:15
@AlexPoole i´ll try this out, that looks like a good solution. — Mattis Seehaus, Jun 23 '16 at 11:19
@MattisSeehaus - [look at this one too](http://stackoverflow.com/q/3841441/266304); leans towards catching the exception (as Florin suggests). — Alex Poole, Jun 23 '16 at 11:21
Thank you @AlexPoole i made it a little different, but it works perfectly! I´ll share my answer soon. Thank you all for your answers! — Mattis Seehaus, Jun 23 '16 at 12:34
Sharing what you did probably isn't going to add much to the general store of knowledge, if it's based on one of those existing answers. This can be closed as a duplicate of whichever old question you used. — Alex Poole, Jun 23 '16 at 12:38
hm yes you´re right. i just used two different case-statements now and combined them into another referring to [this solution](http://stackoverflow.com/questions/13217600/if-exists-condition-not-working-with-plsql) — Mattis Seehaus, Jun 24 '16 at 18:20

score 0 · Answer 1 · answered Jun 23 '16 at 11:16

One solution, is to interrogate your table first then decide to insert. Like:

declare cnt number;

begin
select count(*) into cnt from your_table where id = 5;

if cnt = 0 then
   pi_event_line_add(5,'xxx'...);
end if;

Also, you probably want to add a unique constraint on the column that stores "5" if it truly should be unique.

score 0 · Answer 2 · answered Jun 23 '16 at 11:38

0

Use a MERGE statement in pi_event_line_add instead of an INSERT. In the MERGE only implement the WHEN NOT MATCHED INSERT portion of the statement.

Best of luck.

answered Jun 23 '16 at 11:38

Bob Jarvis - Слава Україні

48,992
9
77
110

"If not exist"-condition in a case in SQL-procedure from Oracle-Database

2 Answers2