So I'm trying to create a small Oracle APEX app that allows people to make appointments at a clinic. I made a procedure that is responsible to make the appointment. The app has a table that saves data about the people who made an appointment and a table for the appointments. The procedure checks if the user is already in the patients table, if he is there it should just add a row in the appointments table, else it also adds them in the patients table.
select COUNT(p.name) INTO da FROM patients p where p.cnp = cnp;
IF (da = 0) then
insert into patients values(cnp,name,surname,sex,birth_date,phone_no,email);
end if;
insert into appointments values(appointment_sequence.nextval,cnp,id_medic,date,time);
The problem is that it never runs the insert that is inside the if clause. Even if the patient is not in the database, it jumps to the second insert and this generates an error because the foreing key for appointments table (cnp) was not created.
The issue is most likely to be down to identifier scope; I'm guessing you have a parameter (cnp) for your procedure that is the same name as a column in the patients table. Because you didn't qualify the parameter reference (i.e. where p.cnp = procedure_name.cnp) in the query, Oracle thinks you are after rows where that column equals that column (i.e. where p.cnp = p.cnp) meaning you'll never get a count of 0 unless there are no rows in the table.
To avoid this issue, you could qualify the parameter when referencing it (which Bryn Llewellyn - product manager for PL/SQL would recommend you do) or you can change the name of the parameter (e.g. to something like p_cnp).
Having said all that, doing a count just to work out whether you need to do an insert or not is wasteful. Why query the table only to do the insert next when you could just insert the record and catch the dup_val_on_index error (assuming you have primary/unique keys defined! You have, right?) or use a merge statement instead? You should aim to do the least amount of work possible in order to build performant code.
I'd probably go with the merge, something like:
merge into patients tgt
using (select procedure_name.cnp from dual) src
on tgt.cnp = src.cnp
when not matched then
insert (tgt.cnp, tgt.name, ....)
values (src.cnp, procedure_name.name, ....);