Re: Problem with a PL/SQL script. FOR....LOOP

Adding to Mick reply, I would also suggest using VARCHAR2, not CHAR. There are some quirks about comparing CHARs.

The other question...why are you looping over the rows? As you have it, you are only returning the results from the LAST row retrieved. With no order-by, there is no guarantee of getting the same result next time. If you mean to get the total (several rows, you need to change

     MachinePrice := (Spare_rec.ChildTypeNr * Spare_rec.Quantity);

to

     MachinePrice := MachinePrice + (Spare_rec.ChildTypeNr * Spare_rec.Quantity);

even then, it would be faster to do the select like this

declare
CURSOR SparePart_Cur(Curvar CHAR) IS
 SELECT sum(ChildTypeNr* Quantity) machinePrice  FROM SparePartComb
 WHERE ParentTypeNr = Curvar;

 machineprice number(7,2)

begin

   open sparepart_curs (compnr);
   fetch sparepart_curs into machineprice;    close sparepart_curs;

  return machineprice;
end;

HTH
Dan Hekimian-Williams Received on Thu Apr 20 2000 - 03:58:42 CEST