0

I have 3 tables:

products (prod_id, prod_name, prod_sale_price), order_details (order_id, prod_id, prod_quantity) 

and orders (order_id, order_date, cust_id, emp, id).
Basically Im trying to create a function that gets the total order cost when the order_id is input but Im am having trouble trying to put it into code.
Specifically trying to put each product into a separate variable.

CREATE OR REPLACE FUNCTION TotalOrderCost (input_id IN NUMBER)
RETURN NUMBER
IS

    given_id NUMBER := input_id;
    prod_count NUMBER;
    prod_id NUMBER;
    prod_price_by_quantity NUMBER := 0;
    total_order_cost NUMBER := 0;

BEGIN

    SELECT  COUNT(prod_id)
    INTO    prod_count
    FROM    order_details
    WHERE   order_id = given_id;

    FOR i IN 1..prod_count
        LOOP

            /* select into that returns more then 1 row when there is more than one product in the order*/
            SELECT  prod_sale_price*prod_quantity
            INTO    prod_price_by_quantity
            FROM    order_details od
                JOIN products p
                    ON od.prod_id = p.prod_id
            WHERE   order_id = given_id;

            total_order_cost := total_order_cost + prod_price_by_quantity;

        END LOOP;

    RETURN total_order_cost;

END TotalOrderCost;
/
Ouadie
  • 13,005
  • 4
  • 52
  • 62
Michael
  • 199
  • 1
  • 9
  • Do you mean you just want to `select sum(prod_sale_price * prod_quantity`? You don't need to loop though, that will just mean you calculate the same value mltiple times, and add those together, which will give too high a total. Not sure if I'm missing something though...? – Alex Poole Apr 27 '13 at 12:02
  • You got it spot on. Just though I would have to loop given that there are multiple products in an order, but was well off. Thanks a lot for your help. – Michael Apr 27 '13 at 20:00

1 Answers1

1

It sounds like you just want a SUM

SELECT  SUM(prod_sale_price*prod_quantity)
  INTO  total_order_cost 
  FROM    order_details od
     JOIN products p
       ON od.prod_id = p.prod_id
 WHERE   order_id = given_id;

There doesn't appear to be any need to do the COUNT or to introduce a loop.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384