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

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 ;

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 ;

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 ;

Hint

Use RETURN discounted_price; to return the calculated value.

Practice

1. What is the main purpose of creating a stored function in MySQL?

easy

A. To save reusable code that returns a value

B. To create a temporary table

C. To delete data from a table

D. To backup the database

Solution

1. Step 1: Understand stored function purpose

   Stored functions are designed to hold reusable code inside the database.

2. Step 2: Identify key feature

   They must return a value using RETURN, which distinguishes them from procedures.

3. Final Answer:

   To save reusable code that returns a value -> Option A

4. Quick Check:

   Stored function = reusable code + return value [OK]

Hint: Stored functions always return a value [OK]

Common Mistakes:

• Confusing stored functions with procedures
• Thinking stored functions do not return values
• Using stored functions to delete data
• Assuming stored functions create tables

2. Which of the following is the correct way to start defining a stored function in MySQL?

easy

A. CREATE FUNCTION funcName RETURNS INT BEGIN ... END

B. CREATE PROCEDURE funcName() RETURNS INT BEGIN ... END

C. CREATE FUNCTION funcName() RETURNS INT BEGIN ... END

D. CREATE FUNCTION funcName() RETURNS INT { ... }

Solution

1. Step 1: Check syntax for stored function

   MySQL stored functions start with CREATE FUNCTION, include parentheses for parameters, and specify RETURNS type.

2. Step 2: Identify correct syntax

   CREATE FUNCTION funcName() RETURNS INT BEGIN ... END uses correct syntax with parentheses and RETURNS INT, followed by BEGIN ... END block.

3. Final Answer:

   CREATE FUNCTION funcName() RETURNS INT BEGIN ... END -> Option C

4. Quick Check:

   CREATE FUNCTION + RETURNS + BEGIN/END = correct syntax [OK]

Hint: Use parentheses and RETURNS keyword in function definition [OK]

Common Mistakes:

• Using PROCEDURE instead of FUNCTION
• Omitting parentheses after function name
• Using curly braces instead of BEGIN/END
• Missing RETURNS keyword

3. Given the following stored function, what will be the output of SELECT addFive(10);?

DELIMITER $$
CREATE FUNCTION addFive(num INT) RETURNS INT
BEGIN
  RETURN num + 5;
END$$
DELIMITER ;

medium

A. 15

B. 5

C. 10

D. Syntax Error

Solution

1. Step 1: Understand function logic

   The function addFive takes an integer input and returns the input plus 5.

2. Step 2: Calculate output for input 10

   Input 10 plus 5 equals 15, so the function returns 15.

3. Final Answer:

   15 -> Option A

4. Quick Check:

   10 + 5 = 15 [OK]

Hint: Add input value to 5 as per function logic [OK]

Common Mistakes:

• Confusing input and output values
• Ignoring RETURN statement
• Assuming syntax error due to DELIMITER
• Forgetting to add 5

4. Identify the error in the following stored function definition:

DELIMITER $$
CREATE FUNCTION multiplyByTwo(num INT) RETURNS INT
BEGIN
  SET result = num * 2;
  RETURN result;
END$$
DELIMITER ;

medium

A. Function name is invalid

B. Incorrect use of DELIMITER

C. RETURN statement is missing

D. Missing variable declaration for 'result'

Solution

1. Step 1: Check variable usage inside function

   The variable 'result' is used but not declared with DECLARE inside the function.

2. Step 2: Understand MySQL variable rules

   Local variables must be declared before use; missing DECLARE causes error.

3. Final Answer:

   Missing variable declaration for 'result' -> Option D

4. Quick Check:

   Variables must be declared before use [OK]

Hint: Declare variables before using SET inside functions [OK]

Common Mistakes:

• Not declaring variables before SET
• Misusing DELIMITER keyword
• Forgetting RETURN statement
• Using invalid function names

5. You want to create a stored function isEven that returns 1 if a number is even and 0 if odd. Which of the following is the correct complete function definition?

hard

A. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
RETURN num / 2;
END$$
DELIMITER ;

B. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
IF num % 2 = 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;

C. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
RETURN num % 2;
END$$
DELIMITER ;

D. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
IF num / 2 = 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;

Solution

1. Step 1: Check logic for even number

   Even numbers have zero remainder when divided by 2, so use num % 2 = 0.

2. Step 2: Verify correct syntax and return values

   Uses IF with correct condition num % 2 = 0 and returns 1 or 0 accordingly, with proper BEGIN/END block, END IF, and DELIMITER.

3. Final Answer:

   DELIMITER $$ CREATE FUNCTION isEven(num INT) RETURNS INT BEGIN IF num % 2 = 0 THEN RETURN 1; ELSE RETURN 0; END IF; END$$ DELIMITER ; -> Option B

4. Quick Check:

   Use modulo operator and IF for even check [OK]

Hint: Use IF and modulo (%) to check even numbers [OK]

Common Mistakes:

• Using division instead of modulo for condition
• Returning wrong data types
• Omitting END IF statement
• Returning num % 2 without comparing to 0