Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    10

    Unanswered: stored procedure update

    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;
    /

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try this:

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

  3. #3
    Join Date
    Jul 2003
    Posts
    10
    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;
    /


    Originally posted by LKBrwn_DBA
    Try this:

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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by izy
    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:

    Code:
    ACCEPT  prodno  PROMPT 'Enter prodno: '
    ACCEPT  newprice  PROMPT 'Enter new price: '
    
    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);
     
    end;
    /
    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?

  5. #5
    Join Date
    Jul 2003
    Posts
    10

    creating stored procedure updates

    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.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: creating stored procedure updates

    Originally posted by izy
    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:

    Code:
    create or replace procedure update_price
      ( p_pno in products.pno%TYPE
      , p_newprice in number
      )
    is
      myprice product.price%TYPE;
    begin 
    
      select PRICE into myprice from PRODUCTS where PNO = p_pno;
    
      update PRODUCTS set PRICE = p_price 
      where PNO = p_pno;
     
      update ODETAILS set COST = p_price * QTY
      where PNO = p_pno;
    
      UPDATE ORDERS SET ORDER_COST = (SELECT SUM(COST) FROM ODETAILS
      WHERE ODETAILS.ONO = ORDERS.ONO AND ODETAILS.PNO = p_pno);
    
    exception
      when no_data_found then
        raise_application_error(-20001,'Product not found' ); 
    end;
    /
    Then to update a price:

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •