МДК.11.01 - 17 - Введение в хранимые процедуры

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

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 представляет собой подпрограмму, хранящуюся в базе данных. Она содержит имя, список параметров и операторы SQL. Все популярные системы управления базами данных поддерживают хранимые процедуры. Они были введены в MySQL 5.

Существует два вида подпрограмм: хранимые процедуры и функции, возвращающие значения, которые используются в других операторах SQL.

Основное отличие заключается в том, что функции могут использоваться, как любое другое выражение в операторах SQL, а хранимые процедуры должны вызываться с помощью оператора CALL.

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

Исходный код хранимых процедур всегда доступен в базе данных. Это эффективная практика связать данные с процессами, которые их обрабатывают.

Пример создания хранимой процедуры без параметров:

DELIMITER $$
CREATE PROCEDURE ShowAllUsers()
BEGIN
    SELECT * FROM users;
END $$
DELIMITER ;

В данном примере создается хранимая процедура ShowAllUsers, которая выполняет запрос на выборку всех данных из таблицы users.

Вызвать такую процедуру можно командой:

CALL ShowAllUsers();

Пример хранимой процедуры с параметром на ввод:

DELIMITER $$
CREATE PROCEDURE GetUser(IN userID INT)
BEGIN
    SELECT * FROM users WHERE id = userID;
END $$
DELIMITER ;

В данном примере создается хранимая процедура GetUser, которая принимает входной параметр userID и выполняет запрос на выборку данных из таблицы users по указанному идентификатору пользователя.

Вызвать такую процедуру можно командой:

CALL GetUser(1);

Здесь, мы получим данные пользователя с ID = 1;

Пример хранимой процедуры с параметром на вывод:

DELIMITER $$
CREATE PROCEDURE CountUsers(OUT usersCount INT)
BEGIN
    SELECT COUNT(*) AS countInt INTO usersCount FROM users;
END $$
DELIMITER ;

Данная хранимая процедура считает количество пользователей и возвращает их в виде выходного параметра usersCount. Выходные параметры, как правило, служат для их дальнейшего присвоения переменным.

Вызвать такую процедуру можно командой:

SET @usersCount = 0;
CALL CountUsers(@usersCount);
SELECT @usersCount;

Обратите внимание, что для начала мы создали переменную @usersCount и задали ей некоторое начальное значение 0, после чего вызвали процедуру, передав ей переменную в качестве некоторого "контейнера" в которую будет положен выходной параметр. После вызова процедуры, это значение присвоилось переменной, что мы впоследствии и увидели при помощи SELECT.

Удалить неиспользуемую процедуру можно при помощи команды:

DROP PROCEDURE CountUsers;

При этом для удаления процедуры не нужно передавать каких-либо аргументов или использоваться скобки.

Пример хранимой процедуры с параметром INOUT:

DELIMITER $$

CREATE PROCEDURE CalculateFixedTax (
  INOUT amount DECIMAL(10,2)
)
BEGIN
  SELECT amount * 0.2 INTO amount;
END $$

DELIMITER ;

В данном примере мы написали хранимую процедуру которая рассчитывает фиксированную ставку НДС, составляющую 20% от переданной суммы. Результат возвращается в ту же самую переменную в которой хранилось исходной значение, что упрощает использование в рамках более сложных конструкций (например если исходное значение получено другой хранимой процедурой).

Такую хранимую процедуру мы можем вызвать следующим образом:

SET @amount = 10500;
CALL CalculateFixedTax(@amount);
SELECT @amount;

Здесь мы передали некоторую сумму для расчета налога в переменной @amount, и после вызова хранимой процедуры и расчета налога, результат будет записал в ту же переменную.

Пример хранимой процедуры с несколькими параметрами IN и OUT:

DELIMITER $$

CREATE PROCEDURE GetUserBalance(
  IN userID INT,
  OUT userBalance DECIMAL(10,2),
  OUT userName VARCHAR(255)
)
BEGIN

  SELECT SUM(balance) INTO userBalance
  FROM accounts
  WHERE user_id = userID;

  SELECT name INTO userName
  FROM users
  WHERE id = userID;

END $$

DELIMITER ;

В данной хранимой процедуре GetUserBalance объявляются три параметра: userID типа INT, userBalance типа DECIMAL(10,2) и userName типа VARCHAR(255).

Процедура сначала получает сумму баланса пользователя, при помощи функции SUM(balance) для переданного userID. Значение суммы баланса присваивается переменной userBalance с помощью оператора SELECT INTO.

Затем процедура получает имя пользователя для переданного userID. Имя пользователя присваивается переменной userName с помощью оператора SELECT INTO.

Таким образом, после выполнения процедуры, значения переменных userBalance и userName будут содержать соответствующие значения баланса пользователя и его имени.

Пример вызова процедуры, например для пользователя с ID = 2:

SET @userBalance = 0;
SET @userName = '';
CALL GetUserBalance(2, @userBalance, @userName);
SELECT @userBalance, @userName;