Functions

Introduction

Earlier lessons covered MySQL's built-in String, Numeric, and Date functions — ready-made tools like UPPER(), ROUND(), and NOW() that ship with MySQL itself. This lesson introduces something different: writing your own custom functions.

A user-defined stored function lets you package your own calculation or logic under a name, store it inside the database, and then call it just like any built-in function inside a SELECT statement.

Not the Built-in Functions

Both kinds are called the same way, with parentheses after a name, but a user-defined function is something you author, using CREATE FUNCTION, to encapsulate your own logic — logic MySQL has no built-in equivalent for.

What is a Stored Function?

A stored function is a saved, reusable block of SQL logic, similar to a stored procedure, but with one defining rule: a function must return exactly one value, and because of that, it can be used directly inside a SELECT statement or any other expression.

Creating a Function

A function is created with CREATE FUNCTION, declaring its parameters, the type of value it RETURNS, and a body that ends by returning that value with RETURN. Just like a stored procedure, DELIMITER is needed to define a function containing semicolons.

Consider a "students" table where each student has a numeric score. A function can calculate a letter grade from that score.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    score INT
);

INSERT INTO students (name, score) VALUES
    ('Alex', 78),
    ('Sam', 45),
    ('Priya', 92);

DELIMITER $$

CREATE FUNCTION letter_grade(score INT)
RETURNS CHAR(1)
DETERMINISTIC
BEGIN
    DECLARE grade CHAR(1);

    IF score >= 90 THEN
        SET grade = 'A';
    ELSEIF score >= 75 THEN
        SET grade = 'B';
    ELSEIF score >= 60 THEN
        SET grade = 'C';
    ELSE
        SET grade = 'F';
    END IF;

    RETURN grade;
END$$

DELIMITER ;

The "letter_grade" function takes a numeric score and always returns a single CHAR(1) value — the corresponding letter grade. DETERMINISTIC tells MySQL that the same input will always produce the same output, which MySQL requires functions to declare.

Using a Function in a SELECT

Because a stored function always returns a single value, it can be called directly inside a SELECT statement, exactly like a built-in function such as ROUND() or UPPER().

SELECT name, score, letter_grade(score) AS grade
FROM students;

+-------+-------+-------+
| name  | score | grade |
+-------+-------+-------+
| Alex  | 78    | B     |
| Sam   | 45    | F     |
| Priya | 92    | A     |
+-------+-------+-------+

The function runs once per row, transforming each numeric score into its letter grade directly inside the query results.

Function vs Procedure

Stored functions and stored procedures look similar, but they serve different purposes.

Dropping a Function

A function that is no longer needed can be removed with DROP FUNCTION.

DROP FUNCTION letter_grade;

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

User-defined stored functions let you package your own single-value calculations inside the database, so they can be reused directly inside SELECT statements and other expressions, just like MySQL's built-in functions.

In this lesson, you learned the difference between built-in functions and user-defined stored functions, how to create one with CREATE FUNCTION, and how it differs from a stored procedure. Next, you will learn about triggers — logic that runs automatically in response to table changes.