Help....on stored procedure (or package)

[raf]

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!

[Lowaiy]

Dear,
In order to help you, you should ask your question in brief. So try to throw your question in better format, and without all these codes.

[raf]

ok, my problem is this:
I have to create a stored procedure in order to reserve the free rooms to the students in the period comprised between the DATE_START and DATE_END.

step1: verify if the student is in table em:
if the student is not in table em, then insert new student

step2: find available room
if find it reserve room INTO rmpct

step3: update students in em

step4: update sex of room or apartment floor

step5: if no room is available raise error

I tried this procedure:

CREATE OR REPLACE PROCEDURE reserve_rooms
-- input parameters:
(p_em_id IN em.em_id%TYPE,
p_cod_stud IN em.cod_stud%TYPE,
p_bl_id IN rm.bl_id%TYPE,
p_fl_id IN rm.fl_id%TYPE,
p_rm_id IN rm.rm_id%TYPE,
p_sex IN rm.sex%TYPE,
p_date_start IN VARCHAR2,
p_date_end IN VARCHAR2)
AS
-- local variables:
v_appo INTEGER;
v_bl_id rm.bl_id%TYPE;
v_fl_id rm.fl_id%TYPE;
v_rm_id rm.rm_id%TYPE;
v_rm_cat rm.rm_cat%TYPE;
v_sex rm.sex%TYPE;
BEGIN
-- verify if the student is in table em:
SELECT COUNT (*)
INTO v_appo
FROM em
WHERE em_id = p_em_id
AND cod_stud = p_cod_stud;
-- if the student is not in table em, then insert new student:
IF v_appo = 0 THEN
INSERT INTO em (em_id, cod_stud)
VALUES (p_em_id, p_cod_stud);
END IF;
BEGIN
-- find available room:
SELECT bl_id, fl_id, rm_id, sex, rm_cat
INTO v_bl_id, v_fl_id, v_rm_id, v_sex, v_rm_cat
FROM (SELECT rm.bl_id, rm.fl_id, rm.rm_id, rm.sex, rm.rm_cat
FROM rmpct, rm
WHERE rm.bl_id = rmpct.bl_id (+)
AND rm.fl_id = rmpct.fl_id (+)
AND rm.rm_id = rmpct.rm_id (+)
AND rm.bl_id = NVL (p_bl_id, rm.bl_id)
AND rm.fl_id = NVL (p_fl_id, rm.fl_id)
AND rm.rm_id = NVL (p_rm_id, rm.rm_id)
AND (rm.sex = p_sex OR rm.sex = 0)
AND rmpct.date_start (+) <= TO_DATE (p_date_end, 'DD-MM-YYYY')
AND rmpct.date_end (+) >= TO_DATE (p_date_start, 'DD-MM-YYYY')
GROUP BY rm.bl_id, rm.fl_id, rm.rm_id, rm.sex, rm.rm_cat, rm.rm_std
HAVING SUM (decode(rmpct.rm_id (+),null,0,1))
< DECODE (rm_std, 'S', 1, 'D', 2, 'T', 3)
ORDER BY DBMS_RANDOM.RANDOM)
WHERE ROWNUM = 1;
-- reserve room:
INSERT INTO rmpct (bl_id, fl_id, rm_id, em_id, cod_stud, date_start, date_end)
VALUES (v_bl_id, v_fl_id, v_rm_id, p_em_id, p_cod_stud,
TO_DATE (p_date_start, 'DD-MM-YYYY'),
TO_DATE (p_date_end, 'DD-MM-YYYY'));
-- update students:
UPDATE em
SET bl_id = v_bl_id, fl_id = v_fl_id, rm_id = v_rm_id
WHERE em_id = p_em_id
AND cod_stud = p_cod_stud;
-- update sex of room or apartment floor:
IF v_sex = 0 THEN
IF v_rm_cat = 'ROOM' THEN
UPDATE rm
SET sex = p_sex
WHERE bl_id = v_bl_id
AND fl_id = v_fl_id
AND rm_id = v_rm_id;
ELSIF v_rm_cat = 'APARTMENT' THEN
UPDATE rm
SET sex = p_sex
WHERE bl_id = v_bl_id
AND fl_id = v_fl_id;
END IF;
END IF;
EXCEPTION
-- if no room is available:
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20001, 'Sorry, there is no such room available.');
END;
END reserve_rooms;

Have you any idea?

Thanks in advance!

[marist89]

and what is your problem?

[DaPi]

Originally posted by raf
Have you any idea?

Yes.

Mr.H do you have the same one?

[marist89]

Originally posted by DaPi
Yes.

Mr.H do you have the same one?

I thought of that too, but I'm only down to 3 bullets and will probably need them for my own guys today...

[gandolf989]

Originally posted by marist89
I thought of that too, but I'm only down to 3 bullets and will probably need them for my own guys today...

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

I thought you guys were going to say "who wants to live in a room with a sex code of zero?"

Raf, you aren't going to get someone to completely do you job for you. Besides I see 4 procedures and a function in a single package not one procedure. Steps 1, 3 and 4 can be separates procedures in a package. Step two is a function that returns an available room. Step 5 should be called when you want to assign someone to a room. It in turn will call the other procedures and the function. You need to break down the problem more and test each part seperately, then write the main procedure. You might also want to look at error handling and error logging.

step1: verify if the student is in table em:
if the student is not in table em, then insert new student

step2: find available room
if find it reserve room INTO rmpct

step3: update students in em

step4: update sex of room or apartment floor

step5: if no room is available raise error