Расширенные хранимые процедуры в 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
Источник: