Functions in
MySQL
Instructor: Sriya Ivaturi
What is a MySQL Function?
➔ A function is a stored program that returns a single value.
➔ Can be used in SELECT, WHERE, SET clauses.
➔ Similar to procedures but return values.
➔ Called inside SQL expressions.
➔ Q1: What is the main difference between a function and a procedure in
MySQL?
Answer
A function must return a value and can be used in
expressions, unlike procedures.
Syntax of a Function
DELIMITER $$
CREATE FUNCTION name(params)
RETURNS datatype DETERMINISTIC
BEGIN
RETURN value;
END $$
DELIMITER ;
Q3: Why is DETERMINISTIC used in function definitions?
Answer
A: It tells MySQL the function always returns the same result for
the same input.
Simple Function – Add Two Numbers
DELIMITER $$
CREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT DETERMINISTIC
BEGIN
RETURN a + b;
END $$
DELIMITER ;
SELECT AddNumbers(10, 20);
Q4: What will happen if we call AddNumbers(10, NULL)?
Answer
A: It will return NULL, because any arithmetic with NULL gives
NULL.
Why Use Functions?
➔ Encapsulate reusable logic that returns a value.
➔ Used in queries.
➔ Improves code modularity and readability.
➔ Q2: Can a function perform multiple SELECT queries like a
procedure?
Answer: No, it cannot.
➔ Why?
◆ MySQL functions are designed to return a single value.
◆ They are used within SQL expressions, so:
● Must not produce multiple result sets.
● Cannot include statements that modify data (like INSERT, UPDATE,
DELETE).
● Should be deterministic.
➔ Allowed in Functions:
DECLARE total INT;
SELECT COUNT(*) INTO total FROM employees;
RETURN total;
➔ Not Allowed in Functions:
SELECT * FROM employees; -- This will cause an error
Important Points
Functions -
★ Must not produce multiple result sets.
★ Cannot include statements that modify data (like INSERT,
UPDATE, DELETE).
★ Should be deterministic.
Use Cases
➔ Calculations (e.g., tax, discounts)
➔ String manipulation
➔ Date formatting
➔ Input validations
Function with String Manipulation
DELIMITER $$
CREATE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100) DETERMINISTIC
BEGIN
RETURN CONCAT('Hello, ', name);
END $$
DELIMITER ;
SELECT Greet('Amit');
Q5: Can we use this function inside a WHERE clause?
Answer
A: Yes. Functions can be used in WHERE, SELECT, and ORDER BY
clauses.
Function to Calculate Square
DELIMITER $$
CREATE FUNCTION Square(x INT)
RETURNS INT DETERMINISTIC
BEGIN
RETURN x * x;
END $$
DELIMITER ;
SELECT Square(7);
Q6: Is it allowed to call a function from another function?
Answer
A: Yes, as long as it doesn't cause recursion or infinite
loops.
Function for Date Calculation
DELIMITER $$
CREATE FUNCTION YearsBetween(startDate DATE, endDate DATE)
RETURNS INT DETERMINISTIC
BEGIN
RETURN YEAR(endDate) - YEAR(startDate);
END $$
DELIMITER ;
SELECT YearsBetween('2010-01-01', '2024-01-01');
Summary
➔ Functions return a single value.
➔ Cannot modify data (no INSERT/UPDATE).
➔ Useful in SELECT, WHERE, and expressions.
➔ Must always include RETURN statement.
Practice Challenges
1. Function to return area of circle (input radius).
2. Function to return reverse of a string.
3. Function to check if number is even or odd.
4. Function to return factorial of a number.
