Как создать процедуру в mysql

Question

Первый раз пишу процедуру. У меня есть таблица бронирований, которая создается так:

CREATE TABLE bookings
(
    id          INT  NOT NULL AUTO_INCREMENT,
    room_id     INT  NOT NULL,
    user_id     INT  NOT NULL,
    start_date  DATE NOT NULL,
    end_date    DATE NOT NULL,
    delete_time DATE,
    PRIMARY KEY (ID),
    FOREIGN KEY (room_id) REFERENCES rooms (id),
    FOREIGN KEY (user_id) REFERENCES users (id)
);

Мне необходимо написать процедуру, в которую будет передано 3 значения: дата заезда, дата отъезда и номер комнаты. В самой процедуре мне надо получить все бронирования на данный номер если они есть и проверить, не пересекаются ли даты переданные в процедуру с датами уже существующих бронирований. Если такое не происходит мне надо вернуть false, Если пересечение есть, то вернуть true. Учитывая что это мой первый опыт, я даже не знаю с чего начать. Читал про процедуры, и насколько понимаю мне нужен курсор? Пытался сделать так:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_booking_date`(IN room_id INT, IN start_booking_date DATE, IN end_booking_date DATE)
BEGIN
    declare validate TINYINT;
    with b as (
        select * from bookings bok where bok.room_id = room_id
    );
    if start_booking_date > b.start_date then 
        SET validate = 1;        
        select validate;

    else 
        set validate = 0;        
        select validate;
    end if;
END

Но ничего не выходит. У меня есть данная проверка в коде, и вот как она выглядит на java:

booking.getStartDate().after(b.getStartDate()) && booking.getStartDate().before(b.getEndDate())
                    || (booking.getEndDate().after(b.getStartDate()) && booking.getEndDate().before(b.getEndDate()))
                    || (b.getStartDate().after(booking.getStartDate()) && b.getStartDate().before(booking.getEndDate())))
                    || (b.getEndDate().after(booking.getStartDate()) && b.getEndDate().before(booking.getEndDate()))
                    || (b.getStartDate().getTime() == booking.getStartDate().getTime() || b.getStartDate().getTime() == booking.getEndDate().getTime())
                    || (b.getEndDate().getTime() == booking.getStartDate().getTime() || b.getEndDate().getTime() == booking.getEndDate().getTime())

в данном коде b - уже существующее бронирование а booking - новое, которое и надо проверить.

Answer 1

Если не получается создать

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

то дело в WITH

with b as (
        select * from bookings bok where bok.room_id = room_id
    );

вместо него можно использовать SET

SET @b = (select start_date from (select * from bookings bok where bok.room_id = room_id) as t);

Вариант 1:

Для переменной нужно использовать @ и весь запрос должен быть в скобках, а внутренний иметь алиас (тут не важно какой например: t).

В итоге должно быть так:

DELIMITER // 
    CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_booking_date`(IN room_id INT, IN start_booking_date DATE, IN end_booking_date DATE)
BEGIN
    declare validate TINYINT; 
    SET @b = (select start_date from (select * from bookings bok where bok.room_id = room_id) as t);
    if start_booking_date > @b then
        SET validate = 1;
        select validate; 
    else 
        set validate = 0;
        select validate; end if;
END //

если start_booking_date > start_date для комнаты вернет 0 если нет вернет 1

Вариант 2

Можно использовать конструкцию SELECT INTO

DELIMITER // 
    CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_booking_date`(IN room_id INT, IN start_booking_date DATE, IN end_booking_date DATE)
BEGIN
    declare validate TINYINT; 
    DECLARE b DATE;
    SELECT start_date INTO b from bookings bok where bok.room_id = room_id;    
    if start_booking_date > b then
        SET validate = 1;
        select validate; 
    else 
        set validate = 0;
        select validate; end if;
END //

Важно: запрос для b должен возвращать не более одного значения, в случае если там будет список произойдет ошибка.

#1172 - Result consisted of more than one row

Думаю там должно быть что-то типа:

SELECT
    start_date 
From
    bookings bok 
WHERE 
    bok.room_id=room_id 
    AND (
        (start_booking_date BETWEEN bok.start_date AND bok.end_date) 
        OR 
        (end_booking_date BETWEEN bok.start_date AND bok.end_date)
    )

БЛОГ НА HUSL

Как создать процедуру в mysql

Ответы (1 шт):