Name- Abhishek Bharat Dadhel PRN-124M1H006
Solution:-
CREATE OR REPLACE FUNCTION Calculate_Price (
P_Product_ID IN NUMBER,
P_Quantity IN NUMBER
) RETURN NUMBER IS
v_unit_price NUMBER(10, 2);
v_discount NUMBER(5, 2);
v_final_price NUMBER(10, 2);
BEGIN
SELECT Unit_Price
INTO v_unit_price
FROM Product
WHERE Product_ID = P_Product_ID;
SELECT Discount
INTO v_discount
FROM Promotions
WHERE Product_ID = P_Product_ID
AND SYSDATE BETWEEN StartDate AND EndDate;
v_final_price := v_unit_price * P_Quantity;
IF v_discount IS NOT NULL THEN
v_final_price := v_final_price * (1 - (v_discount / 100));
END IF;
RETURN v_final_price;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_final_price := v_unit_price * P_Quantity;
RETURN v_final_price;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RETURN NULL;
END Calculate_Price;
/
-- Test the Function
DECLARE
v_final_price NUMBER(10, 2);
BEGIN
-- Test the function for Product ID 1 (Laptop) with quantity 2
v_final_price := Calculate_Price(1, 2);
DBMS_OUTPUT.PUT_LINE('Final Price for Laptop (Quantity 2): Rs ' ||
v_final_price);
-- Test the function for Product ID 2 (Smartphone) with quantity 3
v_final_price := Calculate_Price(2, 3);
DBMS_OUTPUT.PUT_LINE('Final Price for Smartphone (Quantity 3): Rs ' ||
v_final_price);
END;
/
