Creating stored functions in MySQL

Introduction

Stored functions let you save a small program inside the database that you can use again and again to get a result.

When you want to calculate a value many times without rewriting the code.

When you need to reuse a common calculation in different queries.

When you want to keep your database logic organized and easy to maintain.

When you want to simplify complex queries by using a function.

When you want to share a calculation with other people using the database.

Syntax

MySQL

CREATE FUNCTION function_name (parameters) 
RETURNS data_type
BEGIN
  -- function code here
  RETURN value;
END

The function must return a value using RETURN.

Parameters are optional if your function does not need inputs.

Examples

This function adds two numbers and returns the sum.

MySQL

DELIMITER $$
CREATE FUNCTION add_two_numbers(a INT, b INT) 
RETURNS INT
BEGIN
  RETURN a + b;
END$$
DELIMITER ;

This function returns the value of pi without any input.

MySQL

DELIMITER $$
CREATE FUNCTION get_pi() 
RETURNS FLOAT
BEGIN
  RETURN 3.14159;
END$$
DELIMITER ;

Sample Program

This creates a function that squares a number and then uses it to find the square of 5.

MySQL

DELIMITER $$
CREATE FUNCTION square_number(n INT) 
RETURNS INT
BEGIN
  RETURN n * n;
END$$
DELIMITER ;

SELECT square_number(5) AS result;

Important Notes

Use DELIMITER to change the statement end symbol when creating functions in MySQL.

Stored functions can be used in SELECT, WHERE, and other SQL clauses.

Make sure your function always returns a value to avoid errors.

Summary

Stored functions save reusable code inside the database.

They must return a value using RETURN.

Use DELIMITER to define the function body in MySQL.

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