Scalar Function to Calculate Discount Price
Scalar Function to Calculate Discount Price
Query Explanation:
-
USE SalesInventoryDB → Tells MySQL to work inside the "SalesInventoryDB" database.
-
DELIMITER // → Temporarily changes the command separator so MySQL knows where the function ends.
-
CREATE FUNCTION → Defines a new function named
GetDiscountPrice. -
Parameters:
-
Original_Price: Actual product price. -
Discount_Percent: Discount to be applied (in percentage).
-
-
Formula used:
Discounted Price = Original_Price - (Original_Price * Discount_Percent / 100) -
RETURN → Gives the final calculated price.
-
CALL / SELECT → You test it by passing values like 1000 and 10 → Output will be
900.00.
SQL Query:
-- Step 1: Set the database first
USE SalesInventoryDB;
-- Step 2: Change delimiter for function definition
DELIMITER //
-- Step 3: Create function
CREATE FUNCTION GetDiscountPrice(
Original_Price DECIMAL(10,2),
Discount_Percent DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE discount_price DECIMAL(10,2);
SET discount_price = Original_Price - (Original_Price * Discount_Percent / 100);
RETURN discount_price;
END //
-- Step 4: Reset the delimiter back
DELIMITER ;
-- Step 5: Use the function
SELECT GetDiscountPrice(1000, 10) AS DiscountedPrice;
Output:
