Расширенные хранимые процедуры в MySQL

Расширенные хранимые процедуры в MySQL

На этот раз я расскажу о чтении информации из таблиц и манипулировании ею. Ранее я рассмотрел базовое использование и использование циклов. В этой статье я рассмотрю СУЩЕСТВУЕТ, ВЫБРАТЬ В и КУРСОРЫ.

Подзапросы EXISTS просто проверяют, возвращает ли запрос какие-либо строки, и возвращает значение true, если это так. Используйте его, чтобы проверить, присутствуют ли данные в других таблицах.

IF NOT EXISTS(SELECT * FROM People) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'OMG PANIC';
END IF;

ВЫБРАТЬ В позволяет выбирать столбцы из таблицы в переменные. При выборе необходимо будет вернуть одну строку, а переменные, которые выбираются «в», должны быть уже объявлены.

SELECT id, name FROM Profile WHERE id = 10 INTO v_id, v_name;

Если ничего не найдено, вы можете объявить обработчик продолжения для SELECT INTO. Хотя я предпочитаю использовать EXISTS для проверки наличия строки и SELECT INTO, когда я знаю, что что-то уже существует, и терпеть неудачу, если этого не существует.

Для перебора результата можно использовать КУРСОР. Курсоры немного неудобны и требуют осторожности и терпения. Они следуют формату, аналогичному SELECT INTO, но должны быть объявлены заранее, открыты, извлечены и закрыты.

Им также нужны обработчики для обработки конца курсора. Они доступны только для чтения и могут перемещаться в одном направлении. Базовый КУРСОР будет выглядеть примерно так:

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE p_id INT;
  DECLARE p_name CHAR(24);
  DECLARE cursorForProfile CURSOR FOR SELECT id, name FROM Profile;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cursorForProfile;
  read_loop: LOOP
    FETCH cursorForProfile INTO p_id, p_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    #do something with p_id and p_name
  END LOOP;
  CLOSE cursorForProfile;
END

В этом коде много чего происходит. Во-первых, порядок объявления всего. ПЕРЕМЕННЫЕ, КУРСОРЫ, затем ОБРАБОТКИ. Такой порядок установлен. ПРОДОЛЖИТЬ ОБРАБОТЧИК запускается, когда курсор подходит к концу набора результатов. Он устанавливает для переменной done значение true, а затем переменная done используется для выхода из цикла.

См. также:  Праздничная ловушка

Курсор становится активным с помощью OPEN, завершается с помощью CLOSE и считывается с помощью FETCH INTO x, y.

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

Схема (доступна по сути) выглядит примерно так:

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

Базовый sproc выглядит примерно так:

CREATE PROCEDURE `addAvailability`(
  IN p_idHotel INT,
  IN p_date DATE
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;  
  
  IF NOT EXISTS(SELECT * FROM Room WHERE idHotel = p_idHotel) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Rooms';
  END IF;  
END

Затем добавляем курсор, и цикл выглядит так:

CREATE PROCEDURE `addAvailability`(
  IN p_idHotel INT,
  IN p_date DATE
)
BEGIN
  #variables
  DECLARE v_done INT DEFAULT FALSE;
  DECLARE v_idRoom INT;
  DECLARE v_maxAvailable INT;
  DECLARE v_price DECIMAL(10, 2);
  
  #cursors
  DECLARE cursorForRoom CURSOR FOR 
  SELECT
    idRoom,
    maxAvailable,
    price
  FROM 
    Room
  WHERE
    idHotel = p_idHotel;
  
  #handlers
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;  
  
  IF NOT EXISTS(SELECT * FROM Room WHERE idHotel = p_idHotel) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Rooms';
  END IF;
  OPEN cursorForRoom;  
  START TRANSACTION;
  read_loop: LOOP
    FETCH cursorForRoom INTO v_idRoom, v_maxAvailable, v_price;
    IF v_done THEN
      LEAVE read_loop;
    END IF;
    #INSERT GOES HERE
  END LOOP;
  COMMIT;
  
  CLOSE cursorForRoom;
END

Наконец, добавление вставки в sproc выглядит так:

CREATE PROCEDURE `addAvailability`(
  IN p_idHotel INT,
  IN p_date DATE
)
BEGIN
  #variables
  DECLARE v_done INT DEFAULT FALSE;
  DECLARE v_idRoom INT;
  DECLARE v_maxAvailable INT;
  DECLARE v_price DECIMAL(10, 2);
  
  #cursors
  DECLARE cursorForRoom CURSOR FOR 
  SELECT 
    idRoom,
    maxAvailable,
    price
  FROM 
    Room
  WHERE
    idHotel = p_idHotel;
  
  #handlers
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;  
  
  IF NOT EXISTS(SELECT * FROM Room WHERE idHotel = p_idHotel) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Rooms';
  END IF;
  
  OPEN cursorForRoom;  
  START TRANSACTION;
  
  read_loop: LOOP
    FETCH cursorForRoom INTO v_idRoom, v_maxAvailable, v_price;
    IF v_done THEN
      LEAVE read_loop;
    END IF;
    
    INSERT INTO 
      Availability
    SET
      idAvailability = NULL,
      idRoom = v_idRoom,
      numberAvailable = v_maxAvailable,
      price = v_price,
      date = p_date;
  END LOOP;
  
  COMMIT; 
  CLOSE cursorForRoom;
END

Практика выглядит так:

mysql> CALL addAvailability(1, '2017-12-10');
ERROR 1644 (45000): No Rooms
mysql> INSERT INTO Hotel SET idHotel = 1, name = 'Peter\'s Pad';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Room SET idRoom = NULL, idHotel = 1, beds = 1, capacity = 2, price = 10.00, maxAvailable = 10;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Room SET idRoom = NULL, idHotel = 1, beds = 1, capacity = 1, price = 5.00, maxAvailable = 17;
Query OK, 1 row affected (0.00 sec)
mysql> CALL addAvailability(1, '2017-12-10');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM Availability;
+----------------+--------+-----------------+-------+------------+
| idAvailability | idRoom | numberAvailable | price | date       |
+----------------+--------+-----------------+-------+------------+
|              1 |      1 |              10 | 10.00 | 2017-12-10 |
|              2 |      2 |              17 |  5.00 | 2017-12-10 |
+----------------+--------+-----------------+-------+------------+
2 rows in set (0.00 sec)
mysql> CALL addAvailability(1, '2017-12-10');
ERROR 1062 (23000): Duplicate entry '1-2017-12-10' for key 'UNIQUE'

Это покрывает продвинутые sprocs в MySQL.

См. также:  Культ мастерства и как слова «я не знаю» могут вас спасти

 

Промежуточные хранимые процедуры MySQL
В предыдущей статье я рассмотрел некоторые основы sprocs, в этой статье я расскажу о некоторых циклах и проверке … medium.com

 

 

Хранимые процедуры MySQL 101
Зачем решать одну проблему, если можно решить две? Я много слышал о том, как хранимые процедуры (sprocs) в MySQL — это штаны… medium.com

 

 

Планирование емкости и разработка программного обеспечения
Работа на максимальной мощности — это плохо и может привести к более медленным циклам выпуска, выгоранию и большему стрессу. Без… medium.com

 

Понравилась статья? Поделиться с друзьями:
IT Шеф
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: