CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
nsuriya239
nsuriya239

Reputation: 89

Can't Update Table via Stored Procedure

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

I am using an Oracle database in SQL Developer.

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

Upvotes: 1

Views: 105

Answers (2)

William Robertson
William Robertson

Reputation: 16001

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

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:

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:

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.

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.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

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.

Upvotes: 2

Related Questions