How create stored procedure (or package).....

From: db_senior <db_senior_at_yahoo.it>
Date: Tue, 08 Feb 2005 09:12:14 -0500
Message-ID: <99a9f88f32c0649be599bb5c2fa7351c@localhost.talkaboutdatabases.com>

hi,
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):

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 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:verify if there are free double or triple rooms (D or T) between those occupied in that period.
if find it reserve room INTO rmpct

step4: update students in em

step5: update sex of room or apartment floor in rm table.

step6: if no room is available raise error

I tried this:

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! Received on Tue Feb 08 2005 - 08:12:14 CST