[PL/SQL] select in a procedure

[hatebin]

Hello

My task is to write a procedure which has to get information about price of a product and
updates the data base for produc_id=777.

if product_pirce < 400
then
product_price=500
if product_price > 400
then
product_price=300

IN PL/SQL:

create or replace procedure change_price
is begin

if (select price from product where produc_id=777) <400
then
update product
set price=500 where produc_id=777;
end if;

if (select price from product where produc_id=777) > 400
then
update product
set price=300 where produc_id=777;
end if;

end change_price;

WARNING:

Encountered the symbol "SELECT" when expecting one of the following:

( - + case mod new not null others

Can some one show my how should my procedure be written if I want to get data using SELECT ?

maby sholud I create a cursor ?

As you see
the
Procedure goals:
- it needs to get information from tables (SELECT something)
- it needs to work witch that information (if>?)

Thanks for any help.

[debasisdas]

you need to use

SELECT price into product_price FROM product WHERE produc_id=777;

and then check the value of the variable product_price in a IF...END IF block.

[hatebin]

I've changed it like you said

CREATE OR REPLACE PROCEDURE change_price
IS BEGIN
select price into product_price from product where produc_id=777; 
IF product_price <400
THEN
UPDATE product
SET price=500 WHERE produc_id=777;
END IF;
 
IF product_price > 400
THEN
UPDATE product
SET price=300 WHERE produc_id=777;
END IF;
end;

ERROR at line 10: PL/SQL: Statement ignored

[hfx642]

First of all, you haven't defined product_price.
Second, your logic will always set the price to 300, unless product_price was exactly 400.
Try...

CREATE OR REPLACE PROCEDURE change_price IS
   product_price Number;
BEGIN
   select price into product_price from product where produc_id = 777; 

   IF product_price < 400 THEN
      UPDATE product SET price = 500 WHERE produc_id = 777;
   ELSE
      UPDATE product SET price = 300 WHERE produc_id = 777;
   END IF;
   
end;
/
Show Errors;

Also, you should make your numbers (the; 777, 400, 500, 300) parameters.
This would make the functionality of the procedure generic.
(ie. STOP HARD-CODING VALUES!!!)

[arshadshabbir]

Hence answers given above fulfill the requirement only advancement in code.

Try the following generic procedure pass Product Code as a parameter, also handling the exception if product_id not found in table

CREATE OR REPLACE PROCEDURE change_price(vProduc_id number) IS
       product_price NUMBER;
       new_price number;
    BEGIN
   
       SELECT price INTO product_price FROM product WHERE produc_id = vProduc_id;
     
       IF product_price < 400 THEN
          new_price := 500;
       ELSE
         new_price := 300;
       end if;

       UPDATE product SET price = new_price WHERE produc_id = vProduc_ID;
    exception
       when no_data_found then
            return;     
    END;
    /
    Show Errors;

[hatebin]

Thanks for bringing it to work getntelman.