|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > PL/SQL procedure with parameters |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|