mysql - 缺少END-Mysql存储过程

我非常习惯于使用TSQL进行编写，而MySQL会进行一些调整，但我想我已经掌握了大部分。我有一个存储过程，但是不会让步，我已经读取并重新读取了100次，读取了所有可以发现的错误，但仍然没有运气。

在第11行以及该语句的结尾：

CREATE TEMPORARY TABLE ReturnAvalRooms (roomID int);

即使出现在查询的开头，我也会收到错误“ Missing End”。
我检查并仔细检查了所有内容是否都已声明并设置了适当的内容；但无法弄清楚为什么我会收到此错误。

以下是存储过程的完整副本。

    CREATE PROCEDURE `Get_AvailRooms` (IN StartDate datetime, IN EndDate datetime, IN RoomType int)
BEGIN
    DECLARE PStartDate datetime;
    DECLARE PEndDate datetime;
    DECLARE PRoomType int;
    SET PStartDate = StartDate;
    SET PEndDate = EndDate;
    SET PRoomType = RoomType;

        CREATE TEMPORARY TABLE AvaliableNowRooms AS (SELECT idRooms FROM rooms WHERE RoomNextAvail < PStartDate AND RoomTypeID = PRoomType);
        CREATE TEMPORARY TABLE ReturnAvalRooms (roomID int);

    DECLARE AvailRooms CURSOR FOR SELECT * FROM AvaliableNowRooms;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE RoomID INT;

    OPEN AvailRooms;

    read_loop: LOOP

    FETCH AvailRooms INTO RoomID;
        IF done THEN
            LEAVE read_loop;
        END IF;

            DECLARE Bookings CURSOR FOR SELECT BookingStartDate, BookingEndDate, BookingRoomID FROM Booking WHERE BookingRoomID = RoomID AND BookingEndDate < PStartDate;
            DECLARE PBookingStartDate datetime;
            DECLARE PBookingEndDate datetime;
            DECLARE PBookingRoomID,

            OPEN Bookings

            Booking_Read: LOOP
            FETCH Bookings INTO PBookingStartDate, PBookingEndDate, PBookingRoomID;
            IF done THEN
                LEAVE Booking_Read;
            END IF;

            IF PBookingStartDate BETWEEN PStartDate AND PEndDate
                LEAVE Booking_Read;
            ELSE IF PBookingEndDate BETWEEN PStartDate AND PEndDate
                LEAVE Booking_Read;
            ELSE
                INSERT INTO ReturnAvalRooms (id) values (PBookingRoomid);
            END LOOP;
            CLOSE Bookings;
            END

    END LOOP;
    CLOSE AvailRooms;
    END

    SELECT * FROM ReturnAvalRooms

END

最佳答案

MySQL特别注重DECLARE语句的顺序。在一个区块内，这些必须首先出现。而且（我相信）处理程序必须是最后的声明。

我对您打算返回的程序感到困惑。似乎您想返回指定类型的所有房间，对于这些房间，没有与指定时间段重叠的预订。

但是，看一下过程中的逻辑，似乎如果预订给定房间的行没有排，该房间将不会被退回。这似乎很奇怪。令我感到困惑的是，这到底应该返回什么。大量不必要的混乱使规范变得模糊。

整个操作过程可以通过一条简单的SQL语句来完成，该语句更简单，更清晰并且启动效率更高。

DELIMITER $$

CREATE PROCEDURE `Get_AvailRooms` (IN PStartDate datetime, IN PEndDate datetime, IN PRoomType int)
BEGIN
-- rooms of the specified type which are "available" for the
-- specified period.  A room is considered not available if
-- the RoomNextAvail col has a date value later than the beginning
-- of the specified period, or there are one or more bookings that
-- overlap that period. If the specified StartDate is later than
-- the specified EndDate, then no rooms are available.
--
-- This query uses an "anti-join" pattern to return only rows
-- in rooms which don't have a overlapping booking

   SELECT r.idRooms
     FROM rooms r
     LEFT
     JOIN Booking b
       ON b.BookingRoomID = r.idRooms
      AND b.BookingEndDate    >= PStartDate
      AND b.BookingStartDate  <= PEndDate
    WHERE b.idRooms IS NULL
      AND r.RoomTypeID        = PRoomType
      AND r.RoomNextAvail     < PStartDate
      AND          PStartDate < PEndDate
    ORDER BY r.idRooms;

END$$

DELIMITER ;

那只是第一次。未经测试。我们可能希望将<=和>=分别更改为<和>，这取决于我们是否认为恰好在10AM结束的预订与从10AM开始的时间段重叠。

关于mysql - 缺少END-Mysql存储过程，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35715757/