MySQL Stored Functions: Comprehensive Guide

Stored functions are an important type of stored object in MySQL that allow you to encapsulate logic and return a single value. Unlike stored procedures, functions must return a value and can be used directly in SQL statements.

Stored Function Basics

Definition and Syntax

DELIMITER //
CREATE FUNCTION function_name(parameter1 datatype, parameter2 datatype, ...)
RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
[SQL DATA ACCESS OPTION]
BEGIN
    -- Function body
    RETURN value;
END //
DELIMITER ;

Key elements:

• RETURNS clause: Mandatory, specifies the data type of the return value
• DETERMINISTIC/NOT DETERMINISTIC: Indicates whether the function always returns the same result for the same inputs
• SQL DATA ACCESS OPTIONS:
  • CONTAINS SQL (default)
  • NO SQL
  • READS SQL DATA
  • MODIFIES SQL DATA

Simple Example

DELIMITER //
CREATE FUNCTION calculate_bonus(salary DECIMAL(10,2), years_of_service INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE bonus_amount DECIMAL(10,2);

    IF years_of_service < 5 THEN
        SET bonus_amount = salary * 0.05;
    ELSEIF years_of_service < 10 THEN
        SET bonus_amount = salary * 0.10;
    ELSE
        SET bonus_amount = salary * 0.15;
    END IF;

    RETURN bonus_amount;
END //
DELIMITER ;

Calling a Stored Function

Unlike stored procedures, you call functions directly in SQL expressions:

-- In a SELECT statement
SELECT employee_name, salary, calculate_bonus(salary, years_of_service) AS bonus
FROM employees;

-- In a variable assignment
SET @bonus = calculate_bonus(50000, 7);

-- In WHERE clauses
SELECT * FROM employees WHERE calculate_bonus(salary, years_of_service) > 5000;

Deterministic vs. Non-Deterministic Functions

Deterministic Functions

A deterministic function always returns the same result for the same input parameters.

DELIMITER //
CREATE FUNCTION calculate_circle_area(radius DOUBLE)
RETURNS DOUBLE
DETERMINISTIC
BEGIN
    RETURN PI() * radius * radius;
END//
DELIMITER ;

Non-Deterministic Functions

A non-deterministic function may return different results even with the same input parameters.