Hi,
I have any problem to create stored procedure in Oracle, Can you help me?
I have to create a stored procedure (or package) in order to reserve the free rooms to the students in the period comprised between the DATE_START and DATE_END.

Table of the present rooms in building BL1 (RM): SEX 1 = M - SEX 2 = F SEX = 0 (ROOM WITHOUT SEX)

BL_ID.....FL_ID.......RM_ID.........SEX......RM_STD.....RM_CAT
BL1.........1..........101...............1........S........ROOM
BL1.........1..........102...............0........D........ROOM
BL1.........1..........103...............2........T........ROOM
BL1.........2..........201...............2........S........ROOM
BL1.........2..........202...............1........D........ROOM
BL1.........2..........203...............1........T........ROOM
BL1.........3..........301...............2........S........APARTMENT
BL1.........3..........302...............2........D........APARTMENT
BL1.........3..........303...............1........T........APARTMENT
BL1.........3..........304...............1........D........APARTMENT
BL1.........3..........305...............0........D........APARTMENT

Table of the students (EM):

EM_ID...........BL_ID.......FL_ID........RM_ID........COD_STUD
SABRINA..........BL1..........1............102.........524505
TAKEM............BL1..........1............103.........569673
SERAFINO.........BL1..........1............103.........589920
STELLA...........BL1..........1............102.........574659
CHIARA...........BL1..........1............101.........587845
VIDAL............BL1..........1............102.........602877
ROSARIA..........BL1..........2............202.........517070
LUCA.............BL1..........2............201.........602743
DANIELA..........BL1..........2............203.........602865
ANNAMARIA........BL1..........3............305.........588721
LUIGI............BL1..........3............304.........546517

Type of rooms (RM_STD):

RM_STD.......STD_EM........DESCRIPTION
D.............4..............DOUBLE
T.............6..............TRIPLE
S.............2..............SINGLE

Tables of the reservations carried out from the students (RMPCT):

EM_ID......BL_ID........FL_ID......RM_ID......DATE_START.......DATE_END.......COD_STUD
CHIARA......BL1.........1..........101.......11/02/2004.......12/02/2004.......587845
CHIARA......BL1.........1..........101.......03/02/2005.......16/02/2005.......587845
SERAFINO....BL1.........1..........102.......12/02/2004.......19/02/2004.......589920
VIDAL.......BL1.........1..........102.......16/02/2004.......01/03/2004.......602877
SERAFINO....BL1.........1..........103.......01/02/2004.......15/02/2004.......589920
TAKEM.......BL1.........1..........103.......04/02/2005.......10/02/2005.......569673
LUCA........BL1.........2..........201.......03/02/2005.......23/02/2005.......602743
ROSARIA.....BL1.........2..........202.......03/02/2005.......16/02/2005.......517070
DANIELA.....BL1.........2..........203.......03/02/2005.......04/02/2005.......602865
LUIGI.......BL1.........3..........301.......03/02/2005.......23/02/2005.......546517
VALERIA.....BL1.........3..........302.......12/02/2004.......16/02/2004.......515348
CHIARA......BL1.........3..........302.......05/02/2004.......15/02/2004.......587845
CHIARA......BL1.........3..........304.......10/02/2004.......12/02/2004.......587845
CHIARA......BL1.........3..........305.......20/01/2004.......04/02/2004.......587845
ANNAMARIA...BL1.........3..........305.......03/02/2005.......16/02/2005.......588721


INPUT PARAMETERS:
CREATE OR REPLACE Procedure RESERVE_ROOMS (stud_name varchar2,
cod_stud varchar2,
bl_in varchar2,
fl_in varchar2,
rm_in in varchar2,
sex_in varchar2,
date_start_in varchar2,
date_end_in varchar2)

CONDITIONS:

verify if there are students in table EM:

select count (1)
into v_appo
from em
where em_id = stud_name
and cod_stud = cod_stud;

if v_appo = 0 then

insert new student:
insert into em (em_id,cod_sud,sex)
values (stud_name,cod_stud,sex_in);

Now I must verify the free rooms in the period comprised between the DATE_START_IN and DATE_END_IN.

I tried this query:

select bl_id,fl_id,rm_id,RM_STD
from rm
where (bl_id,fl_id,rm_id,RM_STD) not in (select a.bl_id,a.fl_id,a.rm_id,A.RM_STD
from rm a, rmpct b
where a.bl_id=b.bl_id
and a.fl_id=b.fl_id
and a.rm_id=b.rm_id
AND((b.date_start <= TO_DATE(date_start_in, 'dd-mm-YYYY')
AND b.date_end >= TO_DATE(date_end_in, 'dd-mm-YYYY'))
OR ( b.date_end <= TO_DATE(date_end_in, 'dd-mm-YYYY'))
AND b.date_end >= TO_DATE(date_start_in,

'dd-mm-YYYY')
OR ( b.date_start >= TO_DATE(date_start_in, 'dd-mm-YYYY')
and b.date_start <= TO_DATE(date_end_in,

'dd-mm-YYYY')
AND b.date_end >= TO_DATE(date_end_in, 'dd-mm-YYYY'))))


with this query I get all free rooms in period date_start_in - date_end_in, but I must,also,verify if there are double or triple rooms (reserved) with minus of 2 (double) or minus of 3 (triple) students. If there are I can reserved these rooms.


I tried to verify with these steps:

CREATE OR REPLACE VIEW COUNT_EM ( BL_ID,
FL_ID, RM_ID, NUMBER_EM ) AS
(SELECT rm.bl_id, rm.fl_id, rm.rm_id, COUNT(*) as numero_em
FROM em, rm
WHERE em.bl_id(+) = rm.bl_id
AND em.fl_id(+) = rm.fl_id
AND em.rm_id(+) = rm.rm_id
and rm.rm_std in ('S', 'D', 'T')
group by rm.bl_id, rm.fl_id, rm.rm_id)

CREATE OR REPLACE VIEW COUNT_RMPCT ( BL_ID,
FL_ID, RM_ID, STD_EM, NUMBER_RMPCT
) AS
SELECT rm.bl_id, rm.fl_id, rm.rm_id, rmstd.std_em, COUNT(*) as numero_rmpct
FROM rm, rmpct,rmstd
WHERE rmpct.bl_id(+) = rm.bl_id
AND rmpct.fl_id(+) = rm.fl_id
AND rmpct.rm_id(+) = rm.rm_id
and rm.rm_std=rmstd.rm_std
and rm.rm_std in ('S', 'D', 'T')
AND((rmpct.date_start <= TO_DATE(date_start_in', 'dd-mm-YYYY')
AND rmpct.date_end >= TO_DATE(date_end_in, 'dd-mm-YYYY'))
OR ( rmpct.date_end <= TO_DATE(date_end_in, 'dd-mm-YYYY'))
AND rmpct.date_end >= TO_DATE(date_start_in, 'dd-mm-YYYY')
OR ( rmpct.date_start >= TO_DATE(date_start_in, 'dd-mm-YYYY')
and rmpct.date_start <= TO_DATE(date_end_in, 'dd-mm-YYYY')
AND rmpct.date_end >= TO_DATE(date_end_in, 'dd-mm-YYYY')))
group by rm.bl_id, rm.fl_id, rm.rm_id, rmstd.std_em

AND FINALLY:
select a.bl_id, a.fl_id, a.rm_id, a.NUMBER_RMPCT, B.NUMBER_EM, a.std_em, (a.std_em -

(a.NUMBER_RMPCT+B.NUMBER_EM)) RM_FREE
from COUNT_RMPCT a, COUNT_EM b
where a.bl_id=b.bl_id
and a.fl_id=b.fl_id
and a.rm_id=b.rm_id

if RM_FREE > 0 THEN there are free rooms (D or T) between those occupied in that period.

Now If the room (bl_in,fl_in,rm_in) is free I can reserve inserting it in the table RMPCT:

INSERT INTO rmpct (bl_id, fl_id, rm_id,em_id,cod_stud,date_start, date_end)values(bl_in,fl_in,rm_in,stud_name,cod_stud,date_start_in,date_end_in);

If I haven't rm_in (can be null) I must reserve the first free room (random).

after these controls: I update table of the students:


UPDATE em
Set bl_id = BL_IN,fl_id= FL_IN,rm_id=rm_in
where em_id=stud_name
and cod_stud=cod_stud;

Finally I must make a control on the sex of the room, because there are rooms that have sex=0

if RM.SEX <> 0 then
null
else
if rm_cat = 'ROOM' then
UPDATE rm
set sex = sex_in
where bl_id = in
and fl_id = in
and rm_id = in;

if rm_cat = 'APARTMENT' then (update on all rooms)

UPDATE rm
set sex = sex_in
where bl_id = in
and fl_id = in;


IF v_appo > 0 then
Same controls except: insert into em (em_id,cod_sud,sex)
values (stud_name,cod_stud,sex_in);

How I can insert in one stored procedure (or package) all these instructions and controls?

Thanks in advance!