Oracle SQL, Insert statement inside if not running -

so i'm trying create small oracle apex app allows people make appointments @ clinic. made procedure responsible make appointment. app has table saves data people made appointment , table appointments. procedure checks if user in patients table, if there should add row in appointments table, else adds them in patients table.

select count(p.name) da patients p p.cnp = cnp; if (da = 0)     insert patients values(cnp,name,surname,sex,birth_date,phone_no,email);   end if; insert appointments values(appointment_sequence.nextval,cnp,id_medic,date,time); 

the problem never runs insert inside if clause. if patient not in database, jumps second insert , generates error because foreing key appointments table (cnp) not created.

the issue down identifier scope; i'm guessing have parameter (cnp) procedure same name column in patients table. because didn't qualify parameter reference (i.e. where p.cnp = procedure_name.cnp) in query, oracle thinks after rows column equals column (i.e. where p.cnp = p.cnp) meaning you'll never count of 0 unless there no rows in table.

to avoid issue, qualify parameter when referencing (which bryn llewellyn - product manager pl/sql recommend do) or can change name of parameter (e.g. p_cnp).

having said that, doing count work out whether need insert or not wasteful. why query table insert next when insert record , catch dup_val_on_index error (assuming have primary/unique keys defined! have, right?) or use merge statement instead? should aim least amount of work possible in order build performant code.

i'd go merge, like:

merge patients tgt   using (select procedure_name.cnp dual) src     on tgt.cnp = src.cnp when not matched   insert (tgt.cnp, tgt.name, ....)   values (src.cnp, procedure_name.name, ....);