#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    4
    Rep Power
    0

    PL/SQL procedure with parameters


    hey can anyone help me with PL/SQL statements i am having trouble with. Our assignment is a video shop database.
    this is what i'm trying to do....

    Write a procedure called DUE_FILMS that accepts a date (in DD/MM/YYYY format) that will determine the films that are either overdue or due back on that day. The procedure must display the film title, format, copy number, customerís name and contact number, the date it was rented and due, the number of days it is overdue and the fine due. The output should be in date due order (most overdue film first). The data should also be written in UPPERCASE to a table called FILMS_DUE_AND_OVERDUE.

    this is what i have for it....

    create or replace procedure due_films(overdue_date IN DATE)
    IS
    cursor c1 is
    select fr.date_rented, fr.date_due, c.cust_firstname, c.cust_lastname, c.cust_contact, f.film_title, f.classification, fc.format, fc.copy_nbr
    from film_rental fr, customer c, film f, film_copy fc
    where fr.customer_nbr = c.customer_nbr
    and fr.film_id = f.film_id
    and fr.film_id = fc.film_id
    and date_due = overdue_date;
    rented_date DATE;
    due_date DATE;
    cus_lastname VARCHAR(15);
    cus_firstname VARCHAR(15);
    contact VARCHAR(9);
    title VARCHAR(20);
    class VARCHAR(2);
    f_mat VARCHAR(3);
    copy_num NUMBER(4);
    begin
    open c1;
    for i in 1..100 LOOP
    fetch c1 INTO rented_date, due_date, cus_lastname, cus_firstname,contact, title, class, f_mat, copy_num;
    exit when c1%NOTFOUND;
    dbms_output.put_line(rented_date||' '||due_date||' '||cus_lastname||' '||cus_firstname||' '||contact||' '||title||' '||class||' '||f_mat||' '||copy_num);
    end loop;
    close c1;
    end;



    but the data i get is like when you dont have a join in and it creates a cartesian product...

    17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G DVD 1
    17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 1
    17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 2
    17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 3
    17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 4
    17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 5


    i'm not sure what i'm doing wrong...........can anybody help........please
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    It seems that your joins are correct but might be you have missed something. I believe that you have more than one copy of some of these films that's why it returns more than one records from the film_copy table because the film_id is same. So whenever you try to join the tables it replicates all copy of those movies with same customer name and movie name. You should add one new column in film_rental table i,e copy_nbr and then join film_rental's copy_nbr with film_copy's copy_nbr column like that
    and fr.copy_nbr = fc.copy_nbr

    You will get the result that you wanted.

    Note : Next time must send template of your table structure.

    Message: Make your life easy, Make others life easier.

    Regards

IMN logo majestic logo threadwatch logo seochat tools logo