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