Creating stored functions in MySQL - Try It Yourself

Creating Stored Functions in MySQL

📖 Scenario: You work at a small online bookstore. You want to create a stored function in MySQL that calculates the discounted price of a book based on a discount percentage.

🎯 Goal: Create a stored function called calculate_discount that takes the original price and discount percentage as inputs and returns the discounted price.

📋 What You'll Learn

Create a stored function named calculate_discount.

The function must accept two parameters: original_price (DECIMAL) and discount_percent (INT).

The function must return the price after applying the discount.

Use proper MySQL syntax for creating stored functions.

💡 Why This Matters

🌍 Real World

Stored functions help automate calculations inside the database, making queries simpler and more efficient for applications like online stores.

💼 Career

Knowing how to create stored functions is useful for database developers and backend engineers who optimize data processing and business logic within databases.

1

Create the basic stored function structure

Write the MySQL code to start creating a stored function named calculate_discount that accepts two parameters: original_price as DECIMAL(10,2) and discount_percent as INT. Begin the function with the CREATE FUNCTION statement and open the function body with BEGIN.

MySQL

-- Your code here to start the stored function creation

Need a hint?

Use CREATE FUNCTION followed by the function name and parameters. Remember to set the delimiter to $$ before creating the function.

2

Add a variable to hold the discounted price

Inside the BEGIN and END block of the calculate_discount function, declare a variable named discounted_price of type DECIMAL(10,2) to store the calculated discounted price.

MySQL

DELIMITER $$
CREATE FUNCTION calculate_discount(original_price DECIMAL(10,2), discount_percent INT)
RETURNS DECIMAL(10,2)
BEGIN
    -- Declare discounted_price variable here
END$$
DELIMITER ;

Need a hint?

Use DECLARE discounted_price DECIMAL(10,2); inside the function body.

3

Calculate the discounted price

Assign to the variable discounted_price the value of original_price minus the discount amount. Calculate the discount amount by multiplying original_price by discount_percent divided by 100.

MySQL

DELIMITER $$
CREATE FUNCTION calculate_discount(original_price DECIMAL(10,2), discount_percent INT)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE discounted_price DECIMAL(10,2);
    -- Calculate discounted_price here
END$$
DELIMITER ;

Need a hint?

Use SET discounted_price = original_price - (original_price * discount_percent / 100); to calculate the discounted price.

4

Return the discounted price

Add a RETURN statement to return the value of discounted_price from the calculate_discount function. Close the function with END and reset the delimiter.

MySQL

DELIMITER $$
CREATE FUNCTION calculate_discount(original_price DECIMAL(10,2), discount_percent INT)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE discounted_price DECIMAL(10,2);
    SET discounted_price = original_price - (original_price * discount_percent / 100);
    -- Return discounted_price here
END$$
DELIMITER ;

Need a hint?

Use RETURN discounted_price; to return the calculated value.