MySQL - Хранимые функции

Здравствуйте, ambitные энтузиасты баз данных! Сегодня мы окунемся в fascинирующий мир хранимых функций MySQL. Как ваш доброжелательный сосед-преподаватель информатики, я с радостью провожу вас через это путешествие, даже если вы никогда не писали ни строчки кода. Так что возьмите杯 свой любимый напиток и давайте начнем!

MySQL - Stored Functions

Что такое хранимые функции MySQL?

Представьте себе魔法льную кулинарную книгу, которая может Instantly приготовить ваше любимое блюдо, когда вам это нужно. Это sorta как хранимые функции MySQL для баз данных! Это как заранее написанные рецепты (или функции), которые вы можете вызвать, когда вам нужно выполнить определенную задачу в вашей базе данных.

Why Использовать хранимые функции?

Переносимость: Напишите один раз, используйте много раз!
Консистентность: Обеспечьте единое приложение логики везде.
Безопасность: Контролируйте, кто может получить доступ и изменить ваши функции.
Производительность: Они могут быть быстрее, чем повторение одного и того же кода.

Теперь, натянем рукава и создадим нашу первую хранимую функцию!

Создание вашей первой хранимой функции

Вот простой пример, чтобы начать:

DELIMITER //

CREATE FUNCTION HelloWorld() 
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    RETURN 'Привет, мир!';
END //

DELIMITER ;

Давайте разберем это:

DELIMITER //: Это говорит MySQL использовать // в качестве разделителя陈述 вместо стандартного точка с запятой (;). Мы делаем это, потому что наша функция содержит точку с запятой, и мы не хотим, чтобы MySQL запутался.
CREATE FUNCTION HelloWorld(): Эта строка объявляет, что мы создаем функцию с именем "HelloWorld".
RETURNS VARCHAR(50): Это указывает, что наша функция будет возвращать строку (VARCHAR) до 50 символов.
DETERMINISTIC: Этот ключ λέ λέ, что для одинаковых входных данных функция всегда будет возвращать одинаковый результат.
BEGIN и END: Эти ключи охватывают тело нашей функции.
RETURN 'Привет, мир!';: Это сам код нашей функции. Он просто возвращает строку "Привет, мир!".
DELIMITER ;: Это устанавливает разделитель обратно на стандартный точка с запятой.

Теперь давайте вызовем нашу функцию:

SELECT HelloWorld();

И voila! Вы должны увидеть "Привет, мир!" в качестве результата. Поздравления, вы только что создали и вызвали свою первую хранимую функцию!

Параметры и переменные в хранимых функциях

Давайте сделаем вещи немного интереснее, добавив параметры и переменные в наши функции.

DELIMITER //

CREATE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE greeting VARCHAR(100);
    SET greeting = CONCAT('Привет, ', name, '! Добро пожаловать в функции MySQL!');
    RETURN greeting;
END //

DELIMITER ;

В этом примере:

Мы добавили параметр name VARCHAR(50) к нашей функции.
Мы объявили переменную greeting с использованием ключа DECLARE.
Мы используем ключ SET, чтобы присвоить значение нашей переменной.
Мы использовали функцию CONCAT, чтобы объединить строки.

Давайте вызовем нашу новую функцию:

SELECT Greet('Алиса');

Это должно вернуть: "Привет, Алиса! Добро пожаловать в функции MySQL!"

Использование хранимых функций с данными базы данных

Теперь давайте создадим функцию, которая действительно взаимодействует с нашей базой данных. Представьте, что у нас есть таблица employees с колонками id, first_name, last_name, и salary.

DELIMITER //

CREATE FUNCTION CalculateBonus(employee_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE employee_salary DECIMAL(10,2);
    DECLARE bonus DECIMAL(10,2);

    SELECT salary INTO employee_salary
    FROM employees
    WHERE id = employee_id;

    SET bonus = employee_salary * 0.1;

    RETURN bonus;
END //

DELIMITER ;

Эта функция:

Принимает employee_id в качестве входных данных.
Объявляет переменные для employee_salary и bonus.
Получает зарплату сотрудника из таблицы employees.
Рассчитывает bonus в размере 10% от зарплаты.
Возвращает рассчитанный bonus.

Чтобы использовать эту функцию:

SELECT first_name, last_name, CalculateBonus(id) AS bonus
FROM employees
WHERE id = 1;

Это вернет имя, фамилию и рассчитанный bonus для сотрудника с id 1.

Управление потоком в хранимых функциях

Давайте улучшим наш расчет bonus с помощью управления потоком:

DELIMITER //

CREATE FUNCTION CalculateBonusTier(employee_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
    DECLARE employee_salary DECIMAL(10,2);
    DECLARE bonus_tier VARCHAR(20);

    SELECT salary INTO employee_salary
    FROM employees
    WHERE id = employee_id;

    IF employee_salary < 30000 THEN
        SET bonus_tier = 'Бронзовый';
    ELSEIF employee_salary < 50000 THEN
        SET bonus_tier = 'Серебряный';
    ELSE
        SET bonus_tier = 'Золотой';
    END IF;

    RETURN bonus_tier;
END //

DELIMITER ;

Эта функция использует IF, ELSEIF, и ELSE, чтобы определить уровень bonus на основе зарплаты сотрудника.

Вызов хранимой функции из хранимой процедуры

Наконец, давайте посмотрим, как мы можем использовать наши хранимые функции внутри хранимой процедуры:

DELIMITER //

CREATE PROCEDURE EmployeeBonusReport(IN emp_id INT)
BEGIN
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_bonus DECIMAL(10,2);
    DECLARE emp_bonus_tier VARCHAR(20);

    SELECT CONCAT(first_name, ' ', last_name) INTO emp_name
    FROM employees
    WHERE id = emp_id;

    SET emp_bonus = CalculateBonus(emp_id);
    SET emp_bonus_tier = CalculateBonusTier(emp_id);

    SELECT emp_name AS 'Имя сотрудника',
           emp_bonus AS 'Сумма bonus',
           emp_bonus_tier AS 'Уровень bonus';
END //

DELIMITER ;

Эта хранимая процедура:

Принимает идентификатор сотрудника в качестве входных данных.
Получает полное имя сотрудника.
Вызывает нашу функцию CalculateBonus, чтобы получить сумму bonus.
Вызывает нашу функцию CalculateBonusTier, чтобы получить уровень bonus.
Возвращает отчет с именем сотрудника, суммой bonus и уровнем bonus.

Чтобы вызвать эту процедуру:

CALL EmployeeBonusReport(1);

И вот и все! Мы рассмотрели основы хранимых функций MySQL, от создания до использования в хранимых процедурах. Помните, что практика делает мастера, так что не бойтесь экспериментировать со своими функциями. Счастливого кодирования!

Credits: Image by storyset