thank you .....
"andrewst" <member14183_at_dbforums.com> wrote in message
news:2729358.1049465317_at_dbforums.com...
>
> 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
Received on Sat Apr 05 2003 - 10:09:38 CST
