МДК.11.01 - 20 - Пользовательские функции

Примеры данной темы используют учебную БД:

SET foreign_key_checks = 0;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS accounts;

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
);

LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES (1,'Иванов Иван Иванович'),(2,'Петров Петр Петрович'),(3,'Сидоров Сидор Сидорович');
UNLOCK TABLES;

CREATE TABLE `accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) DEFAULT NULL,
  `user_id` INT NOT NULL,
  `balance` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

LOCK TABLES `accounts` WRITE;
INSERT INTO `accounts` VALUES (1,'Основной',1,10500.00),(2,'Резервный',1,3412.57),(3,'Основной',2,20750.00),(4,'Основной',3,25000.00),(5,'Накопительный',2,5401.75);
UNLOCK TABLES;

SET foreign_key_checks = 1;

MySQL 8 предлагает широкий набор функций, которые позволяют выполнять различные операции над данными в базе данных. Функции могут быть использованы для выполнения вычислений, преобразования данных, агрегации и других задач.

Вот пример функции для MySQL 8, которая рассчитывает налог НДС на основе переданной суммы:

DELIMITER $$
CREATE FUNCTION calculate_vat(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE vat DECIMAL(10,2);
    SET vat = amount * 0.2; -- 20% ставка НДС
    RETURN vat;
END $$
DELIMITER ;

В этом примере мы создаем функцию с именем calculate_vat, которая принимает параметр amount типа DECIMAL(10,2) (сумма) и возвращает значение типа DECIMAL(10,2) (налог НДС). Внутри функции мы объявляем переменную vat типа DECIMAL(10,2), затем рассчитываем налог НДС, умножая сумму на 0.2 (что соответствует 20% ставке НДС). Наконец, мы возвращаем значение переменной vat с помощью оператора RETURN.

Теперь мы можем использовать эту функцию для расчета налога НДС на основе переданной суммы. Например:

SELECT calculate_vat(100);

Этот запрос вернет налог НДС для суммы 100. В данном случае, функция вернет значение 20, так как 20% от 100 равно 20.

Пометка типа функции в MySQL 8

Ключевые слова DETERMINISTIC, NO SQL и READS SQL DATA используются для пометки функций в MySQL и указания их свойств и поведения. Они были введены в MySQL 5.0 для обеспечения безопасности и целостности данных.

1. DETERMINISTIC:

   • Ключевое слово DETERMINISTIC указывает, что функция всегда возвращает одинаковый результат для одних и тех же входных данных.
   • Это означает, что функция не зависит от внешних факторов, таких как текущее время или состояние базы данных.
   • Если функция помечена как DETERMINISTIC, MySQL может кэшировать результаты вызова функции и использовать их для оптимизации запросов.
   • Это ключевое слово особенно полезно для функций, которые используются в выражениях SELECT, чтобы гарантировать, что результаты будут предсказуемыми и однозначными.

2. NO SQL:

   • Ключевое слово NO SQL указывает, что функция не выполняет операции с базой данных.
   • Это означает, что функция не изменяет данные в базе данных и не делает запросы к другим таблицам или представлениям.
   • Если функция помечена как NO SQL, MySQL может оптимизировать ее выполнение, так как нет необходимости проверять изменения данных или блокировать таблицы.

3. READS SQL DATA:

   • Ключевое слово READS SQL DATA указывает, что функция может выполнять операции чтения данных из базы данных.
   • Это означает, что функция может использовать операторы SELECT для получения данных из таблиц или представлений.
   • Если функция помечена как READS SQL DATA, MySQL может оптимизировать ее выполнение, учитывая кэширование данных и другие оптимизации чтения.

Важно отметить, что эти ключевые слова не обязательны для всех функций в MySQL. Они рекомендуются для использования только в тех случаях, когда они действительно применимы. Неправильное использование этих ключевых слов может привести к непредсказуемому поведению функций или проблемам с производительностью.

Например, если у вас есть функция, которая всегда возвращает текущее время, вы можете пометить ее как DETERMINISTIC, так как результат будет одинаковым для каждого вызова с одними и теми же входными данными. Если у вас есть функция, которая только читает данные из таблицы, вы можете пометить ее как READS SQL DATA, чтобы MySQL мог оптимизировать ее выполнение.

В целом, ключевые слова DETERMINISTIC, NO SQL и READS SQL DATA помогают оптимизировать выполнение функций и обеспечивают безопасность и целостность данных в MySQL.

В случае нашей функции, можно будет пометить её как DETERMINISTIC(т.к. при одних и тех же входных данных она будет давать одинаковый результат):

DELIMITER $$
CREATE FUNCTION calculate_vat(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE vat DECIMAL(10,2);
    SET vat = amount * 0.2; -- 20% ставка НДС
    RETURN vat;
END $$
DELIMITER ;

Теперь создать такую функцию можно без изменения значения глобальной переменной log_bin_trust_function_creators.

Различия функций и хранимых процедур

MySQL 8 позволяет создавать и использовать как хранимые процедуры так и пользовательские функции для выполнения различных операций. При этом, функции и хранимые процедуры являются двумя различными типами объектов базы данных, каждый из которых имеет свои особенности и применение.

Хранимые процедуры:

• Хранимые процедуры в MySQL 8 представляют собой набор инструкций, которые выполняются последовательно при вызове процедуры.
• Хранимые процедуры могут содержать операторы SQL, условные выражения, циклы и другие конструкции языка программирования.
• Хранимые процедуры могут использоваться для выполнения сложных операций, включая манипуляции с данными, обработку ошибок и транзакции.
• Хранимые процедуры могут вызываться из других хранимых процедур, функций, триггеров и приложений.
• Пример использования хранимой процедуры: CALL calculate_discount(price, discount, @discounted_price); SELECT @discounted_price;

Пользовательские функции:

• Функции в MySQL 8 предназначены для возврата значения на основе заданных входных параметров.
• Функции могут использоваться в выражениях SELECT, WHERE, HAVING и других частях запроса для вычисления значений.
• Функции могут быть вызваны из других функций, хранимых процедур и триггеров.
• Функции могут содержать только операторы SQL и не могут изменять данные в базе данных.
• Пример использования функции: SELECT calculate_total(price, quantity) FROM products;

Теперь рассмотрим, для чего лучше использовать функции и хранимые процедуры:

• Используйте хранимые процедуры, когда вам нужно выполнить сложные операции, включая манипуляции с данными, обработку ошибок и транзакции, в том числе для группирования логически связанных инструкций в одном месте и обеспечивают повторное использование кода.
• Используйте функции, когда вам нужно вычислить и вернуть значение на основе входных параметров,в различных частях БД, в том числе в выражениях SELECT, WHERE, HAVING и других частях запроса для вычисления значений.

В целом, функции и хранимые процедуры в MySQL 8 имеют разные цели и применение. Функции используются для вычисления и возврата значений, а хранимые процедуры — для выполнения сложных операций. Выбор между ними зависит от конкретных требований вашего проекта.

Задание для самопроверки

1. Модифицируйте БД таким образом чтобы в ней можно было хранить совершенные транзакции (например, в таблице transactions), с указанием типа транзакции для пополнения (deposit), снятия (withdrawal) и перевода денежных средств между счетами (transfer). При этом необходимо учитывать дату и время, сумму транзакции, тип транзакции а также id пользователя и счет с которого он её совершил, а для операции перевода между счетами не забудьте указать счет на который совершается перевод.

2. Реализуйте хранимую процедуру moveMoney, которая позволит выполнить любой из типов транзакции (deposit,withdrawal,transfer), не забудьте проверить каждый тип транзакции на возможность выполнения (положить можно любую сумму, а вот снять и перевести можно не больше чем на счете есть денег).

3. Добавьте в таблицу transactions сумму комиссии за совершение операции. Для каждого из типов операций будет своя комиссия, а именно:

   • за пополнение счета комиссия не предусмотрена
   • за снятие предусмотрена комиссия в размере 3% от суммы операции
   • за перевод между счетами предусмотрена комиссия в размере 0.5% от суммы операции (уплачивает тот кто переводит)

   Учтите, что хранить в транзакциях необходимо отдельно сумму перевода, сумму комиссии а также полную сумму транзакции. Расчет комиссии реализуйте при помощи пользовательской функции calculateFee, которая принимает два аргумента: тип транзакции и сумму для расчета, а возвращает сумму комиссии (за пополнение счета должна вернуть 0)