I redid the program, and I am able to get everything working and the right output, except updating the order_cost field. The order_cost update gives me errors and I have no idea what the problem is. Here is what I have so far:

ACCEPT prodno PROMPT 'Enter prodno: '
ACCEPT newprice PROMPT 'Enter new price: '


CREATE OR REPLACE PROCEDURE udpatePrice

AS

myprice PRODUCTS.PRICE%TYPE;





begin

select PRICE into myprice from PRODUCTS where PNO = &prodno;



end;

/

begin




update PRODUCTS set PRICE = &newprice
where PNO = &prodno;


update ODETAILS set COST = &newprice * QTY
where PNO = &prodno;


update ORDERS set ORDER_COST = SUM(COST)
where PNO = &prodno;





end;
/

Try this:

UPDATE ORDERS
SET ORDER_COST = (
SELECT SUM(COST) FROM ODETAILS
WHERE ODETAILS.ONO = ORDERS.ONO
AND ODETAILS.PNO = &prodno);

This compiles fine, but i don't think it got updated. Also, i have added in an exception in case there is no product number. This compiles fine, but also, it doesn't print out the error. I have tried so many times, but I'm having no luck.

ACCEPT prodno PROMPT 'Enter prodno: '
ACCEPT newprice PROMPT 'Enter new price: '


CREATE OR REPLACE PROCEDURE udpatePrice

AS

myprice PRODUCTS.PRICE%TYPE;


NO_DATA_FOUND EXCEPTION;




begin

select PRICE into myprice from PRODUCTS where PNO = &prodno;
RAISE NO_DATA_FOUND;



end;

/

begin





update PRODUCTS set PRICE = &newprice
where PNO = &prodno;


update ODETAILS set COST = &newprice * QTY
where PNO = &prodno;


UPDATE ORDERS SET ORDER_COST = (SELECT SUM(COST) FROM ODETAILS
WHERE ODETAILS.ONO = ORDERS.ONO AND ODETAILS.PNO = &prodno);


EXCEPTION
when NO_DATA_FOUND then
dbms_output.put_line('Error: ' || SQLERRM);




end;
/

Why do you create a procedure called udpatePrice that doesn't do anything, and never gets called?

Also, an UPDATE will never raise NO_DATA_FOUND, so that exception handler is redundant.

Your code can be reduced to:

That last update looks wrong: it updates the order_cost of all orders to be the sum of associated odetails.cost for one specific product only. What if the order has other products also? Don't the cost of those products count towards the total?

I am told to write a stored procedure that processes updates of PRICE, COST and ORDER_COST. I am told to include an exception handler for the exception raised, in case the product number is not in the PRODUCTS table.

In that case, move all the code into the procedure:

Then to update a price:

begin
update_price( 'XYZ', 19.99 );
commit;
end;
/

(I'm still suspicious about that last update though).