Хранимые функции в SQL

Хранимые функции — это мощный инструмент SQL, который позволяет создавать переиспользуемые блоки кода для выполнения вычислений и преобразования данных. В отличие от встроенных функций, хранимые функции создаются разработчиками для решения специфических задач.

Хранимая функция — это именованный блок SQL-кода, который принимает параметры, выполняет вычисления и всегда возвращает одно значение определённого типа.

Общая структура хранимой функции

MySQL 8.1
CREATE FUNCTION имя_функции(параметр1 ТИП, параметр2 ТИП, ...)
RETURNS тип_возвращаемого_значения
BEGIN
    -- логика функции
    RETURN результат_вычислений;
END;

MySQL 8.1
CREATE OR REPLACE FUNCTION имя_функции(параметр1 ТИП, параметр2 ТИП, ...)
RETURNS тип_возвращаемого_значения
LANGUAGE plpgsql
AS $$
BEGIN
    -- логика функции
    RETURN результат_вычислений;
END;
$$;

Простой пример функции

Создадим функцию для определения, является ли человек совершеннолетним по дате рождения:

MySQL 8.1
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

MySQL 8.1
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

Теперь эту функцию можно использовать в любом запросе:

MySQL 8.1
-- Создаём функцию
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

-- Используем функцию
SELECT
    is_adult('2010-05-15') AS child_status,
    is_adult('2000-03-20') AS adult_status;

MySQL 8.1
-- Создаём функцию
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

-- Используем функцию
SELECT
    is_adult('2010-05-15') AS child_status,
    is_adult('2000-03-20') AS adult_status;

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

Хранимые функции особенно полезны при работе с реальными данными. Например, мы можем использовать нашу функцию для фильтрации студентов по возрасту:

MySQL 8.1
-- Создаём функцию
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

-- Используем функцию в запросе к таблице
SELECT
    first_name,
    last_name,
    birthday,
    is_adult(birthday) AS is_adult
FROM Student
WHERE is_adult(birthday) = TRUE
LIMIT 5;

MySQL 8.1
-- Создаём функцию
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

-- Используем функцию в запросе к таблице
SELECT
    first_name,
    last_name,
    birthday,
    is_adult(birthday) AS is_adult
FROM Student
WHERE is_adult(birthday) = TRUE
LIMIT 5;

Функции с запросами к базе данных

Хранимые функции могут выполнять SQL-запросы внутри себя для получения необходимых данных:

MySQL 8.1
CREATE FUNCTION get_student_lessons_count(student_id INT, target_date DATE)
RETURNS INT
BEGIN
    DECLARE lessons_count INT;

    SELECT COUNT(*) INTO lessons_count
    FROM Schedule s
    INNER JOIN Student_in_class sic ON s.class = sic.class
    WHERE sic.student = student_id
      AND s.date = target_date;

    RETURN lessons_count;
END;

MySQL 8.1
CREATE OR REPLACE FUNCTION get_student_lessons_count(student_id INT, target_date DATE)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    lessons_count INT;
BEGIN
    SELECT COUNT(*) INTO lessons_count
    FROM Schedule s
    INNER JOIN Student_in_class sic ON s.class = sic.class
    WHERE sic.student = student_id
      AND s.date = target_date;

    RETURN lessons_count;
END;
$$;

Эта функция подсчитывает количество уроков у конкретного студента в определённый день:

MySQL 8.1
SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;

MySQL 8.1
SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;

Разбор примера с переменными

До этого мы еще не сталкивались с переменными в SQL, но это важная концепция при создании хранимых функций и процедур. Поэтому давайте разберём предыдущий пример пошагово:

MySQL 8.1
DECLARE lessons_count INT;

Эта строка объявляет переменную lessons_count типа INT. Переменная будет хранить результат нашего запроса.

MySQL 8.1
SELECT COUNT(*) INTO lessons_count
FROM Schedule s
INNER JOIN Student_in_class sic ON s.class = sic.class
WHERE sic.student = student_id
  AND s.date = target_date;

Здесь происходит сохранение результата запроса в переменную:

• SELECT COUNT(*) — подсчитывает количество записей
• INTO lessons_count — сохраняет результат в переменную lessons_count
• Остальная часть — обычный SQL-запрос с JOIN и условиями

MySQL 8.1
RETURN lessons_count;

Возвращаем значение переменной как результат функции.

Важно: Конструкция INTO позволяет сохранить результат SELECT-запроса в переменную. Это основа работы с данными внутри хранимых функций.

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

• Просмотр существующих функций

  MySQL 8.1
  SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
  

  MySQL 8.1
  SELECT routine_name, routine_type
  FROM information_schema.routines
  WHERE routine_type = 'FUNCTION' AND routine_schema = 'public';
  

• Удаление функции

  MySQL 8.1
  DROP FUNCTION IF EXISTS is_adult;
  

  MySQL 8.1
  DROP FUNCTION IF EXISTS is_adult(DATE);
  

• Изменение функции

  Для изменения функции в MySQL нужно сначала удалить старую версию, а затем создать новую:

  MySQL 8.1
  DROP FUNCTION IF EXISTS is_adult;
  -- Создать новую версию функции
  CREATE FUNCTION is_adult(birth_date DATE) ...
  

  В PostgreSQL можно использовать CREATE OR REPLACE FUNCTION:

  MySQL 8.1
  CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
  RETURNS BOOLEAN
  -- новая реализация
  

Хранимые функции — это мощный инструмент для создания переиспользуемой бизнес-логики прямо в базе данных. Они помогают централизовать вычисления и обеспечить консистентность данных во всём приложении! 🚀