Creating stored functions in MySQL - Visual Walkthrough

Concept Flow - Creating stored functions

Start

↓

Define function header

↓

Write function body

↓

Declare variables if needed

↓

Use RETURN to send back result

↓

End function definition

↓

Function ready to use

↓

Call function in queries

↓

Get result from function

You start by defining the function name and parameters, write the body with logic, use RETURN to output a value, then save it. After that, you can call the function in your SQL queries.

Execution Sample

MySQL

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

This code creates a stored function named add_two_numbers that takes two numbers and returns their sum.

Execution Table

Step	Action	Details	Result
1	Set DELIMITER	Change delimiter to $$ to define function	DELIMITER set to $$
2	Create FUNCTION	Define function add_two_numbers with parameters a and b	Function header created
3	Begin block	Start function body with BEGIN	Function body started
4	RETURN statement	Return sum of a and b	Returns a + b
5	End block	Close function body with END	Function body ended
6	Reset DELIMITER	Change delimiter back to ;	DELIMITER reset to ;
7	Call function	Execute SELECT add_two_numbers(3, 5);	Returns 8
8	Call function	Execute SELECT add_two_numbers(10, -2);	Returns 8
9	Exit	Function created and tested successfully	Execution complete

💡 Function created and tested; calls return expected sums.

Variable Tracker

Variable	Start	Call 1 (a=3,b=5)	Call 2 (a=10,b=-2)
a	undefined	3	10
b	undefined	5	-2
RETURN value	undefined	8	8

Key Moments - 3 Insights

Why do we change the DELIMITER before and after creating the function?

We change DELIMITER to $$ so MySQL knows the function definition ends at END$$, not at the usual ; which appears inside the function. After defining, we reset DELIMITER to ; as shown in execution_table rows 1 and 6.

What happens if we forget the RETURN statement inside the function?

Without RETURN, the function does not send back a value, causing errors or NULL results. The RETURN in row 4 is essential to output the sum.

Can we call the function before it is created?

No, the function must be created first (rows 2-6). Calling it before creation will cause an error, as shown in row 7 where the function is successfully called.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of calling add_two_numbers(3, 5)?

A 35

B 15

C 8

D Error

💡 Hint

Check row 7 in the execution_table where the function is called with (3,5).

At which step do we reset the DELIMITER back to semicolon?

A Step 2

B Step 6

C Step 4

D Step 1

💡 Hint

Look at the execution_table row describing resetting DELIMITER.

If we remove the RETURN statement, what will happen when calling the function?

A It will return NULL or cause an error

B It will return the sum anyway

C It will return zero

D It will return the first parameter

💡 Hint

Refer to key_moments about the importance of RETURN statement.

Concept Snapshot

CREATE FUNCTION name(params) RETURNS type
BEGIN
  -- function body
  RETURN value;
END;

Use DELIMITER to define functions with multiple statements.
RETURN sends back the result.
Call function in queries like SELECT name(args);

Full Transcript

Creating stored functions in MySQL involves defining a function with a name and parameters, writing the logic inside a BEGIN...END block, and using RETURN to output a value. We change the DELIMITER temporarily to allow semicolons inside the function body without ending the definition early. After creating the function, we reset the DELIMITER and can call the function in SQL queries to get results. The RETURN statement is essential to send back the computed value. This process helps reuse logic easily in queries.

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