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

db_senior wrote:
> 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!

Home assignment??

Regards
/Rauf Received on Tue Feb 08 2005 - 09:28:17 CST