Help....on stored procedure (or package)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

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

  1. #1
    Join Date
    Jul 2002
    Posts
    228

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

    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!

  2. #2
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41

    Exclamation It's too long...

    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.

  3. #3
    Join Date
    Jul 2002
    Posts
    228
    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!

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    and what is your problem?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by raf
    Have you any idea?
    Yes.

    Mr.H do you have the same one?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    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
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width