PL/SQL help To check date already in use

[devilangel]

Hi all

I've managed to create the procedure that checks for all the unoccupied rooms in that date and if no rooms available message is displayed.and works fine.
but now I'm faced with another problem. I wan't the user to enter the date in a dialogue screen then the PL/SQL script returns all the rooms availables.

CREATE TABLE ROOM 
   (	ROOM_NUMBER VARCHAR2(2) NOT NULL, 
	DAILY_RATE DECIMAL(7,2),  
	CONSTRAINT ROOM_ROOMID_PK PRIMARY KEY (ROOM_NUMBER));

 CREATE TABLE GUEST 
   (	ROOM_NUMBER VARCHAR2(2), 
	DATE_ARRIVAL DATE NOT NULL, 
	DATE_DEPARTURE DATE NOT NULL, 
      	CONSTRAINT GUEST_ARRIVALDATE_CHK CHECK (DATE_ARRIVAL <= DATE_DEPARTURE),  
	CONSTRAINT GUEST_ROOM_FK FOREIGN KEY (ROOM_NUMBER)
	REFERENCES ROOM (ROOM_NUMBER));

INSERT INTO ROOM VALUES ('R1',55);
INSERT INTO ROOM VALUES ('R2',85);
INSERT INTO ROOM VALUES ('R3',65);
INSERT INTO ROOM VALUES ('R4',95);
INSERT INTO ROOM VALUES ('R5',125);

INSERT INTO Guest VALUES('R1','26-FEB-06','28-FEB-06';
INSERT INTO Guest VALUES('R3','25-FEB-06','28-FEB-06');



accept date prompt "enter date: "

/*create or replace PROCEDURE check_date (date_to_check IN DATE) IS*/

declare date_do_check :='&date';

room_to_check VARCHAR2(2);
v_check       NUMBER;

CURSOR c_rooms IS
SELECT room_number FROM room;
BEGIN

 OPEN c_rooms;
     LOOP
         FETCH c_rooms INTO room_to_check;
         EXIT WHEN c_rooms%NOTFOUND;

         SELECT Count(*)
            INTO v_check
 FROM guest
         WHERE ROOM_NUMBER = room_to_check
            AND date_to_check BETWEEN date_arrival AND date_departure;

           IF v_check = 0 THEN
               Dbms_Output.put_line('Room ' || room_to_check || ' is availabe');
           ELSE
               Dbms_Output.put_line('Room ' || room_to_check || ' is occupied');
           END IF;
       END LOOP;

       CLOSE c_rooms;
   END;

Thanx

[tabreaz]

Hope the following code will help you.

create or replace type rooms_available is table of varchar2(2);
/

create or replace function check_date (date_to_check IN DATE) 
return rooms_available
IS

rooms rooms_available;
BEGIN
 SELECT room_number bulk collect into rooms
 FROM room m
 WHERE NOT EXISTS
 (SELECT 1 
	FROM guest g
	WHERE m.ROOM_NUMBER = g.ROOM_NUMBER
 	AND date_to_check BETWEEN date_arrival AND date_departure);
 return rooms;
END;

SELECT * FROM TABLE(check_date(sysdate));