MySQL - Stored Functions

MySQL: Stored Functions

What is a Stored Function?

A stored function in MySQL is a named block of SQL code that is stored in the database. It is designed to perform a specific calculation or operation and, most importantly, return a single value. Once created, a stored function can be used directly in SQL statements just like built-in functions such as NOW() or CONCAT().

They are ideal for encapsulating complex business logic that needs to be reused across many queries, ensuring consistency and simplifying application code.

Stored Function vs. Stored Procedure

While similar, functions and procedures have key differences:

• Return Value: A function must return a single value. A procedure does not have a direct return value but can return data through OUT or INOUT parameters or by returning result sets.
• Invocation: A function can be called directly within a SELECT, WHERE, or SET statement. A procedure must be invoked using the CALL statement.
• Usage: Functions are for calculations and data transformation. Procedures are for performing a series of actions, such as complex data updates or administrative tasks.

Creating a Stored Function (CREATE FUNCTION)

To create a stored function, you need the CREATE ROUTINE privilege. The syntax involves defining the function name, its parameters, the return data type, and the function body.

Syntax

DELIMITER //

CREATE FUNCTION function_name(parameter1 datatype, ...)
RETURNS return_datatype
[CHARACTERISTICS]
BEGIN
    -- Declaration of local variables
    -- SQL statements and logic
    RETURN value;
END //

DELIMITER ;

Note on DELIMITER: We change the delimiter from the default semicolon (;) to something else (like //) so that MySQL processes the entire function body as a single statement, including the semicolons inside it.

Example: Customer Level Function

Let’s create a function that takes a customer’s salary as input and returns their level (‘Platinum’, ‘Gold’, ‘Silver’). We will use this CUSTOMERS table:

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    SALARY DECIMAL(10, 2)
);
INSERT INTO CUSTOMERS (NAME, SALARY) VALUES ('Ramesh', 2000.00), ('Khilan', 8500.00), ('Muffy', 12000.00);

Now, let’s define the function.

DELIMITER //

CREATE FUNCTION GetCustomerLevel(salary DECIMAL(10,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE customerLevel VARCHAR(10);
    IF salary > 10000.00 THEN
        SET customerLevel = 'Platinum';
    ELSEIF salary > 5000.00 AND salary <= 10000.00 THEN
        SET customerLevel = 'Gold';
    ELSE
        SET customerLevel = 'Silver';
    END IF;
    RETURN customerLevel;
END //

DELIMITER ;

Function Characteristics

You should specify at least one characteristic to tell MySQL about the function’s behavior, which helps the optimizer. The most common is:

• DETERMINISTIC: Specifies that the function will always return the same result for the same input parameters. Our GetCustomerLevel function is deterministic.
• NOT DETERMINISTIC: (Default) The result can change even with the same inputs (e.g., if it calls NOW() or reads from a table).
• READS SQL DATA: Indicates the function contains SELECT statements but does not modify data.
• CONTAINS SQL: Indicates the function contains SQL but does not read or write data (e.g., SET @x = 1).
• MODIFIES SQL DATA: Indicates the function contains statements that modify data (e.g., INSERT, UPDATE). Functions are rarely used for this.

Invoking a Stored Function

You can now use this function in a SELECT query.

SELECT
    NAME,
    SALARY,
    GetCustomerLevel(SALARY) AS Level
FROM CUSTOMERS;

Output

NAME	SALARY	Level
Ramesh	2000.00	Silver
Khilan	8500.00	Gold
Muffy	12000.00	Platinum

Viewing, Modifying, and Dropping Functions

• View Creation Code: SHOW CREATE FUNCTION GetCustomerLevel;
• Drop a Function: DROP FUNCTION IF EXISTS GetCustomerLevel;
• Modifying a Function: There is no ALTER FUNCTION statement to change the body or parameters directly. You must DROP the function and then CREATE it again with the desired changes.

Security Considerations (DEFINER, SQL SECURITY)

When creating a function, you can specify its security context:

• DEFINER: This clause specifies the MySQL user account whose permissions will be used when the function is executed. The default is the user who creates the function. This is powerful but can be a security risk if the definer has high privileges.
• SQL SECURITY: This can be set to DEFINER (default) or INVOKER. If set to INVOKER, the function runs with the permissions of the user who is calling it, not the user who created it. This is generally a safer approach.

CREATE FUNCTION GetCustomerLevel(salary DECIMAL(10,2))
RETURNS VARCHAR(10)
SQL SECURITY INVOKER
-- ... rest of the function

Calling a Function from a Stored Procedure

You can easily reuse function logic within a stored procedure. Here’s a procedure that generates a full customer report using our GetCustomerLevel function.

DELIMITER //

CREATE PROCEDURE GenerateCustomerReport()
BEGIN
    SELECT
        ID,
        NAME,
        SALARY,
        GetCustomerLevel(SALARY) AS Level
    FROM CUSTOMERS
    ORDER BY SALARY DESC;
END //

DELIMITER ;

To run the procedure, you simply use the CALL statement:

CALL GenerateCustomerReport();