MySQL存储过程语法错误求助：酒店空房查询功能实现

Alright, let's work through fixing your stored procedure – there are a mix of syntax typos, logical errors, and schema assumptions that are keeping it from working correctly. Let's break down what's wrong first, then build the correct version.

Key Issues in Your Original Code #

• Spelling mistake: You wrote Rezerwcje instead of Rezerwacje in the JOIN condition – that's an easy typo but will cause an immediate error.
• Incorrect JOIN relationship: Your code joins Pokoj.BookID = Rezerwacje.BookID, which doesn't make sense for a hotel booking schema. Rooms don't have a single BookID (they can be booked multiple times). Instead, your Rezerwacje (bookings) table should have a RoomID foreign key linking to Pokoj.RoomID.
• Broken date conflict logic: Your current WHERE clause for checking overlapping bookings is reversed. To find available rooms, we need to exclude rooms where the target dates overlap with an existing reservation – your current condition doesn't correctly identify those overlaps.
• Missing parentheses: Your WHERE clause has an unclosed parenthesis, which will trigger a syntax error.
• Wrong JOIN type: Using INNER JOIN will exclude any rooms that have never been booked at all. We need a LEFT JOIN to include all rooms, then filter out the ones with conflicting bookings.

Corrected Stored Procedure #

Here's the fixed version with explanations built in:

DELIMITER //
CREATE PROCEDURE wolnePokoje(IN ilosc int, IN poczatek date, IN koniec date)
BEGIN
    -- Fetch room IDs and their capacity for available rooms
    SELECT p.RoomID, p.IloscMiejsc
    FROM Pokoj p
    -- Left join to include all rooms, even those with no bookings
    LEFT JOIN Rezerwacje r ON p.RoomID = r.RoomID
    WHERE 
        -- Either the room has no bookings, OR its bookings don't overlap with target dates
        (r.RoomID IS NULL OR NOT (
            poczatek < r.DataOdjazdu 
            AND koniec > r.DataPrzyjazdu
        ))
        -- Ensure the room can hold the requested number of guests
        AND p.IloscMiejsc >= ilosc
    -- Group to avoid duplicate rows for rooms with multiple non-conflicting bookings
    GROUP BY p.RoomID, p.IloscMiejsc;
END //
DELIMITER ;

How This Works #

1. LEFT JOIN: This includes every room from Pokoj, even if it has never been booked (those rooms will show NULL for all Rezerwacje columns).
2. Date Overlap Check: The condition poczatek < r.DataOdjazdu AND koniec > r.DataPrzyjazdu correctly identifies when your target booking period overlaps with an existing reservation. Using NOT excludes those conflicting rooms.
3. Capacity Filter: p.IloscMiejsc >= ilosc guarantees the room can accommodate at least the number of guests you're querying for.
4. GROUP BY: This prevents duplicate rows for rooms that have multiple past or future bookings that don't conflict with your target dates.

Quick Notes #

• Double-check that your Rezerwacje table has a RoomID column that references Pokoj.RoomID – if your schema uses a different foreign key name, adjust the JOIN condition to match.
• If your date columns include time values (e.g., DATETIME), wrap them in DATE() functions (like DATE(r.DataOdjazdu)) if you only care about date-based availability.

内容的提问来源于stack exchange，提问作者AdrianIT