如果没有运行，Oracle SQL，Insert语句在里面(Oracle SQL, Insert statement inside if not running)

如果没有运行，Oracle SQL，Insert语句在里面(Oracle SQL, Insert statement inside if not running)

所以我正在尝试创建一个小型的Oracle APEX应用程序，允许人们在诊所预约。 我做了一个负责预约的程序。 该应用程序有一个表，用于保存有关约会人员和约会表的数据。 该过程检查用户是否已经在患者表中，如果他在那里，它应该只在约会表中添加一行，否则它也将它们添加到患者表中。

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);

问题是它永远不会运行if子句中的插入。 即使患者不在数据库中，它也会跳转到第二个插入，这会产生错误，因为未创建约会表（cnp）的外键。

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.

最满意答案

这个问题最有可能归结为标识符范围; 我猜你的程序参数（cnp）与Patients表中的列名相同。 因为您没有在查询中限定参数引用（即where p.cnp = procedure_name.cnp ），Oracle认为您在该列等于该列的行之后（即where p.cnp = p.cnp ），这意味着您除非表中没有行，否则永远不会得到0的计数。

为避免此问题，您可以在引用参数时对参数进行限定（Bryn Llewellyn - PL / SQL的产品经理会建议您这样做），或者您可以更改参数的名称（例如，更改为p_cnp ）。

说了这么多，做一个计数只是为了弄清楚你是否需要插入是浪费。 为什么查询表只是为了插入记录并捕获dup_val_on_index错误（假设你已经定义了主键/唯一键！你有，对吗？）或者使用合并语句而只执行插入操作？ 您应该尽可能少地完成工作，以便构建高性能代码。

我可能会选择合并，例如：

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, ....);

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, ....);