Oracle:从程序内更新不起作用 [英] Oracle: Update from within procedure not working

问题描述

在我的 Oracle PL/SQL 过程中，我试图更新这样的一行:

In my Oracle PL/SQL procedure I am trying to update a row like this:

UPDATE personal p
SET p.surname = surname, p.name = name, p."alter" = alter, p.sex = sex, p.jobcode = jobcode, p.year_wage = month_wage * 12
WHERE p.personalnr = personalnr;
COMMIT;

我在提交后立即添加了这两个语句以确认代码到达并使用正确的参数执行(例如，在这里我想更改名称):

I have added these two statements right after the commit to confirm the code is reached and executed with the right arguments (e.g. here I want to change the name):

DBMS_OUTPUT.put_line('updated ' || name);
DBMS_OUTPUT.put_line('personalnr ' || personalnr);

现在这个更新语句是从另一个过程中调用的过程的一部分.

Now this update-statement is part of a procedure that is called from within another procedure.

但是，不会应用更改，即使执行更新，名称也将保持不变.我也尝试使用异常处理程序，但似乎没有发生任何异常.我可以确认 WHERE 子句是意图.有一条记录与谓词匹配.

However, the changes are not applied and the name will remain the same even tho the update was executed. I have tried to use an exception-handler as well and there doesn't seem to be any exception happening. I can confirm that the WHERE-clause is as intendet. There is one record that matches the predicate.

现在奇怪的是:当我将代码更改为下面的示例时，会发生更新.然而，它会更新每条记录，而不仅仅是具有正确个人信息的记录.再次重申:该例程仅使用一个 personalnr 调用一次，该personalnr 仅与表中的一个条目匹配.

Now the strange thing: When I change the code to the example below, an update happens. However it updates every record and not only the one with the right personalnr. Again: the routine is called only once with one personalnr that matches only one entry in the table.

UPDATE personal p
SET p.name = 'test'
WHERE p.personalnr = personalnr;
COMMIT;

推荐答案

它正在工作，但它正在更新表中的所有行(或者至少，那些personalnr 不为空)，而不仅仅是您期望的那个.

It is working, but it's updating all rows in the table (or at least, those where personalnr is not null), not just the one you expect.

来自文档:

如果 SQL 语句引用的名称既属于列又属于局部变量或形式参数，则列名优先.

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.

您有一个与列同名的 PL/SQL 变量.当你这样做

You have a PL/SQL variable that has the same name as a column. When you do

where p.personalnr = personalnr

你真的在做:

where p.personalnr = p.personalnr

同样的事情发生在 set 部分；SET p.surname = surname 将列值更新为它之前的任何值，而不是 PL/SQL 变量的值.所以它看起来更新没有发生 - 它实际上发生了，但是因为一切都设置为与它的原始值相同，所以它看起来没有发生任何事情.(除了 - 所有行现在都将具有相同的 year_wage 值...)

and the same thing happens in the set part; SET p.surname = surname updates the column value to whatever value it had before, not the PL/SQL variable's value. So it looks like the update didn't happen- it actually did, but because everything was set to the same as it's original value it doesn't look like anything happened. (Except - all rows will now have the same year_wage value...)

您可以使用过程名称作为变量的前缀:

You can either prefix your variables with the procedure name:

where p.personalnr = my_proc.personalnr

或更改变量名称，使它们不冲突；通常使用短前缀，例如l_ 用于局部变量，或 p_ 用于传入参数等

or change the variable names so they don't conflict; it's common to use a short prefix, e.g. l_ for a local variable, or p_ for a passed-in parameter, etc.

where p.personalnr = l_personalnr

记得对 set 部分也这样做，否则你的更新似乎仍然不会做任何事情.

Remember to do that for the set part too, or your update still won't appear to do anything.

UPDATE personal p
SET p.surname = l_surname, p.name = l_name, p."alter" = l_alter,
  p.sex = l_sex, p.jobcode = l_jobcode, p.year_wage = l_month_wage * 12
WHERE p.personalnr = l_personalnr;

这篇关于Oracle:从程序内更新不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！