Re: PL/SQL question

Originally posted by Andrew Hardy
> Clint,
>
> > BEGIN
> > OPEN c_MILEAGE_CAL;
> > LOOP
> > EXIT WHEN c_MILEAGE_CAL%NOTFOUND;
> > FETCH c_MILEAGE_CAL INTO
> > V_EMPLOID, V_NUM_MILES, V_CENTSPERMILE, V_DATE, V_DESCR;
> > V_REFUND := V_NUM_MILES * V_CENTSPERMILE;
> > INSERT INTO EXPENSE_LOG
> > VALUES(EXPLOG_COUNT_SEQ.NEXTVAL,V_EMPLOID,11004,V_REFUND,V_DA-
> TE,
> V_DESCR,
> > V_NUM_MILES );
> > END LOOP;
> > CLOSE c_MILEAGE_CAL;
> > COMMIT;
> > END;
>
> Try putting the FETCH before the EXIT WHEN. The way you have it
> the FETCH
> fails, a row is inserted and then you exit the loop.
>
> Andy

Or better still, use the excellent PL/SQL FOR loop, which saves a lot of code AND prevents you from making silly mistakes like that:

DECLARE
  CURSOR c_MILEAGE_CAL IS
    SELECT EMPLO_ID, NUM_MILES, MIL_ALLOWANCE, ENTRY_DATE, DESCR,            V_NUM_MILES * V_CENTSPERMILE REFUND     FROM MILE_LOG;
BEGIN
  FOR r IN c_MILEAGE_CAL LOOP
    INSERT INTO EXPENSE_LOG -- Please list the EXPENSE_LOG columns here !!!!     VALUES(EXPLOG_COUNT_SEQ.NEXTVAL,R.EMPLOID,11004,R.REFUND,R.ENTRY_DATE, R.DESCR,     R.NUM_MILES );
  END LOOP;
  COMMIT;
END; Note the complete absence of variable declarations!

Or, even better (simpler AND faster):

BEGIN
  INSERT INTO EXPENSE_LOG -- Please list the EXPENSE_LOG columns here!!!!   SELECT EMPLO_ID, NUM_MILES, MIL_ALLOWANCE, ENTRY_DATE, DESCR,          V_NUM_MILES * V_CENTSPERMILE REFUND   FROM MILE_LOG;
  COMMIT;
END; (Feeling of deja vu here... haven't I made these same suggestions before?)

--
Posted via http://dbforums.com