无法通过存储过程更新表

I am trying to update the quantity of the Products table via a stored procedure.

我试图通过存储过程更新Products表的数量。

I am using an Oracle database in SQL Developer.

我在SQL Developer中使用Oracle数据库。

create or replace procedure alter_products 
(
  pid in number :=''
  ,qty in number :=''
) 
as 
begin
    UPDATE PRODUCTS
    SET P_QUANTITY = (select P_QUANTITY from PRODUCTS where PID = pid) - qty
    WHERE PID = pid;
end alter_products;

The stored procedure was compiled successfully, but while trying to run it, I got this error:

存储过程已成功编译，但在尝试运行它时，我收到此错误：

ORA-01427: single-row subquery returns more than one row
ORA-06512: at "DB_NAME.ALTER_PRODUCTS", line 7
ORA-06512: at line 8

ORA-01427：单行子查询返回多行ORA-06512：位于“DB_NAME.ALTER_PRODUCTS”，第7行ORA-06512：第8行

2 个解决方案

#1

Your update statement should probably be something like this:

您的更新语句应该是这样的：

UPDATE PRODUCTS p
   SET p.P_QUANTITY = p.P_QUANTITY - qty
   WHERE p.PID = p_pid; --argument should have a different name than the column
-- to keep it clean.

EDIT: Refer @william Robertson's answer for detailed explanation.

编辑：请参阅@william Robertson的答案以获得详细解释。

#2

The parameter pid has the same name as a column in the table (Oracle identifier names are case-insensitive so writing one of them in uppercase doesn't help you), so where you have the condition

参数pid与表中的列具有相同的名称（Oracle标识符名称不区分大小写，因此以大写形式写入其中一个对您没有帮助），所以您有条件

where pid = pid

this will be true for every row in the table, because pid is always equal to itself (unless it's null, and you mentioned in a comment that it's the PK, so it can't be null). Therefore you need to either rename the parameter or else prefix it:

对于表中的每一行都是如此，因为pid总是等于它自己（除非它是null，并且你在注释中提到它是PK，所以它不能为null）。因此，您需要重命名参数或者为其添加前缀：

create or replace procedure alter_products
    ( pid in number
    , qty in number )
as
begin
    update products p
    set    p_quantity =
           ( select p_quantity
             from   products p1
             where  p1.pid = alter_products.pid ) - qty
    where  p.pid = alter_products.pid;
end alter_products;

But then if products.pid is the PK of products, the row you are trying to look up in the subquery is the row you already have, so the subquery is redundant. Why not just:

但是如果products.pid是产品的PK，那么您尝试在子查询中查找的行就是您已经拥有的行，因此子查询是多余的。为什么不呢：

create or replace procedure alter_products
    ( in_pid in products.pid%type
    , in_qty in products.p_quantity%type )
as
begin
    update products p
    set    p_quantity = p_quantity - in_qty
    where  p.pid = in_pid;
end alter_products;

The usual naming convention for parameters is p_, for example p_pid, but your table confusingly has a column named p_quantity, so I have used in_ as the prefix.

参数的通常命名约定是p_，例如p_pid，但是你的表容易混淆地有一个名为p_quantity的列，所以我使用了in_作为前缀。

I have also made the parameters mandatory as it doesn't seem to make any sense for them to be optional as in the original version, and anchored them to their corresponding column types.

我还将参数设置为强制参数，因为它们似乎没有任何意义，因为它们在原始版本中是可选的，并将它们锚定到相应的列类型。

#1