/* Function to receive an order_id value as an input parameter, and
return the shipping & handling amount */
FUNCTION calc_sh (current_order_id NUMBER) RETURN NUMBER
IS
total_order_amount NUMBER;
sh_amount NUMBER;
BEGIN
--determine total order amount
SELECT SUM(price * order_quantity)
INTO total_order_amount
FROM cust_order, order_line, inventory
WHERE cust_order.order_id = order_line.order_id
AND order_line.inv_id = inventory.inv_id
AND cust_order.order_id = current_order_id;
--determine shipping & hanlding amount based on total order amount
IF total_order_amount <= 25 THEN
sh_amount := 5;
ELSIF total_order_amount > 25 AND total_order_amount <= 50 THEN
sh_amount := 8;
ELSIF total_order_amount > 50 AND total_order_amount <= 75 THEN
sh_amount := 10;
ELSE
sh_amount := 15;
END IF;
--return function value
RETURN sh_amount;
END;