МДК.11.01 - 18 - Переменные и условия в хранимых процедурах

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

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 используется ключевое слово DECLARE, за которым следует имя переменной и ее тип данных. Например, для объявления целочисленной переменной с именем myVar можно использовать следующую команду:

DECLARE myVar INT;

Значение переменной можно присвоить с помощью оператора присваивания. Например, чтобы присвоить переменной myVar значение 42, можно использовать следующую команду:

SET myVar = 42;

Далее, значение переменной можно изменять и использовать в операциях.

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

DELIMITER $$
CREATE PROCEDURE MyProcedure()
BEGIN
    DECLARE myVar INT;
    SET myVar = 42;
    SELECT myVar;
END $$
DELIMITER ;

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

DELIMITER $$

CREATE PROCEDURE GetUserBalance(
  IN userID INT
)
BEGIN

  DECLARE userBalance DECIMAL(10,2);
  DECLARE userName VARCHAR(255);

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

  SET userName = (
    SELECT name FROM users
    WHERE id = userID
  );

  SELECT userBalance, userName;

END $$

DELIMITER ;

Результатом выполнения процедуры будет вывод общего баланса всех счетов пользователя с переданным при вызове идентификатором.

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

DELIMITER $$

CREATE PROCEDURE CalculateInterest(
  IN userID INT
)
BEGIN
  DECLARE interestRate DECIMAL(5,2) DEFAULT 0.05;
  DECLARE totalBalance DECIMAL(10,2) DEFAULT 0.00;
  DECLARE interestAmount DECIMAL(10,2) DEFAULT 0.00;

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

  SET interestAmount = totalBalance * interestRate;

  SELECT interestAmount;
END $$

DELIMITER ;

В этом примере мы создаем хранимую процедуру CalculateInterest() которая вычисляет сумму начисленных процентов, которая объявляет три локальные переменные: interestRate, totalBalance и interestAmount. Мы устанавливаем значение переменной interestRate равным 0.05 (пять процентов на сумму остатка), затем выполняем запрос SELECT, чтобы получить общую сумму балансов всех аккаунтов и сохранить ее в переменной totalBalance. Затем мы вычисляем сумму процентов, умножая totalBalance на interestRate и сохраняем результат в переменную interestAmount, значение которой мы выводим с помощью оператора SELECT.

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

Условия в хранимых процедурах

В хранимых процедурах MySQL 8 можно использовать условные операторы для выполнения различных действий в зависимости от определенных условий. Ниже приведен пример использования оператора IF в хранимой процедуре:

DELIMITER $$

CREATE PROCEDURE CheckBalance(
  IN accountID INT
)
BEGIN
    DECLARE balanceAmount DECIMAL(10,2);

    SELECT balance INTO balanceAmount
    FROM accounts WHERE id = accountID;

    IF balanceAmount > 0 THEN
        SELECT 'Баланс положительный';
    ELSEIF balanceAmount < 0 THEN
        SELECT 'Баланс отрицательный';
    ELSE
        SELECT 'Баланс нулевой';
    END IF;
END $$

DELIMITER ;

В этом примере мы создаем хранимую процедуру CheckBalance(), которая принимает один параметр accountID. Мы объявляем локальную переменную balanceAmount типа DECIMAL(10,2) и выполняем запрос SELECT, чтобы получить баланс аккаунта и сохранить его в переменной balanceAmount, затем мы используем оператор IF для проверки значения переменной balanceAmount. Если баланс больше нуля, то выводится сообщение 'Баланс положительный'. Если баланс меньше нуля, то выводится сообщение 'Баланс отрицательный'. Если баланс равен нулю, то выводится сообщение 'Баланс нулевой'.

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

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

1. Модифицируйте хранимую процедуру CalculateInterest таким образом чтобы проценты начислялись только при положительном балансе, во всех других случаях значение начисленного процента должно быть 0.00.
2. Напишите хранимую процедуру перевода денежных средств с одного счета на другой с учётом того хватит ли денег на счету с которого пытаются перевести деньги. И если хватит — перевести, а если нет — вывести текст Недостаточно средств.