CREATE OR REPLACE PROCEDURE
CheckInGuest(guestId INTEGER, hotelId INTEGER, numNights INTEGER, roomNum VARCHAR2) AS
url VARCHAR(100) := '"http://dboracle.eng.fau.edu:7777/~tmeares2012/SimpleMenu.HTML"';
guestName VARCHAR2(100);
hotelName VARCHAR2(100);
errMessage VARCHAR2(200);
stayId INTEGER;
BEGIN
SELECT first_name||' '||last_name INTO guestName FROM guest WHERE guest_id=guestId;
SELECT hotel_name INTO hotelName FROM hotel WHERE hotel_id=hotelId;
SELECT stay_id+1 INTO stayId FROM stay WHERE stay_id = (SELECT MAX(stay_id) FROM stay);
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
errMessage := 'Invalid input parameter';
ReturnMenu(errMessage, url);
END;
WHEN OTHERS THEN
BEGIN
errMessage := 'Something bad happened - '||SQLCODE;
ReturnMenu(errMessage, url);
END;
INSERT INTO stay(stay_id, guest_id, hotel_id, start_date, number_days, room)
VALUES (stayId, guestId, hotelId, SYSDATE, numNights, roomNum);
COMMIT;
htp.print('<html>');
htp.print(' <head>');
htp.print(' <title> Guest Table </title>');
htp.print(' </head>');
htp.print(' <body>');
htp.print(' <h1> Insert Successful </h1>');
htp.print(' <p>');
htp.print(' Guest: '||guestName||' Checked In To Hotel: '||hotelName||' for '||numNights||' Nights In Room '||roomNum);
htp.print(' </p>');
-- footer --
htp.print(' <p>');
htp.print(' <a href='||url||'>');
htp.print(' click here to return to main menu');
htp.print(' </a>');
htp.print(' </p>');
htp.print(' </body>');
htp.print('</html>');
END;