what is the wrong in this PL/SQL block code?

Question

I have 3 tables in my db:

manufacturer: IDm int primary key, manufacturerName varchar.

car: IDc int primary key, manufacturer foreign key references manufacturer(IDm).

rent: IDr int primary key, car foreign key references car(IDc).

I want to create a procedure that prints out for each manufacturer : the number of cars that are made the manufacturer, and the number of the rented cars that are made by the manufacturer.

this is my code:

create or replace procedure Q8 as 
cursor c is select distinct manufacturername, IDm from manufacturer; 
cursor c2 is select * from car; 
rents int:=0; 
cars int; 
allrents int; 
p1 number(38,2); 
p2 number(38, 2); 
id int;  
begin 
   select count(IDr) into allrents from rent; 
   dbms_output.put_line('manufacturer cars rented cars % rents %'); 
   for k in c loop 
     select count(IDc) into cars from car c where c.manufacturer=k.IDm; 
     for k2 in c2 loop 
         select IDc into id from car where car.manufacturer=k.IDm;
         select count(car) into rents from rent r where r.car=id;
     end loop; 
     p1:=(rents/cars)*100; 
     p2:=(rents/allrents)*100; 
     dbms_output.put_line(k.manufacturerName||' '||cars||' '||p1||' '||p2); 
  end loop; 
end;

so, where is the wrong in my code?

Answer 1

You really don't need cursors to achieve this

select manufacturername, IDm, count(cars.IDC) as CarsMade,
                              count(rent.car) as Rental,
                              count(rent.car)/count(cars.IDC) as p1,
                              count(rent.car)/xx.Tot as p2
from manufacturer m
join cars on cars.manufacturer=m.IDm;
join rent on rent.car=car.id
join (select count(*) as Tot from Rent) xx

See if that query gives you what you are looking for, it will run a lot faster than nested cursors

Answer 2

Listen to OldProgrammer - put an if before the second loop.
If cars != 0 the do the second loop
else rents will also be 0 and just set it so.
You will also have to change your p1 statement as (0/0)*100 will throw a divide by zero error
p2 will also and (0/allrents)*100 will always be 0.