Lars Athle Larsen wrote:
> "DA Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3E78E263.18784369_at_exxesolutions.com...
>
>>If you expect a row to have a column value change after your cursor is
>
> opened
>
>>your design isn't valid. A fetch, by definition, is current to the
>
> point-in-time
>
>>(SCN) when the cursor was opened.
>>
>>I would suggest you look at using SELECT FOR UPDATE.
>
>
> My problem isn't that I need to lock the table, the changes that will be
> made is made by my PL/SQL-procedure. Some of the code (there are some more
> if-clauses that is not important for the question). The code works fine,
> except when GjenstMinHon have been moved to the next row, and the sentence
> marked "mail clause" still believes that GjenstMinHon = 0. If Beholdning = 0
> for the updated row, and a row with FDelkontrakt = FDelkontrakt + 1 exists,
> then GjenstMinHon should be moved to the next row. This does not happen.
>
> Any suggestions?
>
> ---
> CREATE OR REPLACE PROCEDURE rtyprc_OverforMinHon AS
> CURSOR c IS
> SELECT * FROM rtytbl_Verkihonorar;
>
> v_c c%ROWTYPE;
> v_opptjentHon rtytbl_verkihonorar.Opptjenthon%TYPE;
>
> BEGIN
> OPEN c;
> LOOP
> FETCH c INTO v_c;
> EXIT WHEN c%NOTFOUND;
>
> -- Main clause
> IF (v_c.beholdning = 0) AND (v_c.GjenstMinHon > 0) THEN
>
> -- I'm finding out if there is a row with FDelkontrakt = the current +
> 1
> BEGIN
> SELECT OpptjentHon INTO v_opptjentHon FROM rtytbl_verkihonorar
> WHERE FHonorar = v_c.FHonorar AND
> FKontrakt = v_c.FKontrakt AND
> FDelkontrakt = v_c.FDelKontrakt + 1;
>
> -- If row exists, update the next row with new value of GjenstMinHon
> IF (v_opptjenthon > v_c.GjenstMinHon) THEN
> UPDATE rtytbl_verkihonorar SET MinHon = v_c.GjenstMinHon,
> ForfatterFavor = v_opptjenthon - v_c.GjenstMinHon
> WHERE FHonorar = v_c.FHonorar AND
> FKontrakt = v_c.FKontrakt AND
> FDelkontrakt = v_c.FDelKontrakt + 1;
> END IF;
>
> --Update the existing row setting GjenstMinHon = 0
> UPDATE rtytbl_Verkihonorar SET GjenstMinHon = 0
> WHERE FHonorar = v_c.FHonorar AND
> FKontrakt = v_c.FKontrakt AND
> FDelkontrakt = v_c.FDelkontrakt;
>
> -- If selectsentence fails, just continue loop
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> NULL;
> END;
>
> END IF;
>
> END LOOP;
> CLOSE c;
>
> COMMIT;
> END;
> /
>
>
It appears that you have an ordering in your data. Make use of it in you
cursor and adjust your logic accordingly. Here, let me see if I can follow
your current logic in words.
fetch record with FHonorar = A, FKontrakt = B and FDelkontrakt = X
if this record has GjenstMinHon > 0 and beholdning = 0
then
if there is a following record (FKontrakt and FHonorar match this record
and FDelkontrakt = X+1)
then
move the GjenstMinHon value to the following record
and set GjenstMinHon value to 0(zero) on this record
Now having done that I realize that what you appear to be trying to do is move
the GjenstMinHon value to the last record in the set. Is that correct? That
appears to be what you are doing (whether intended of not).
If the answer is yes, then change to logic to reflect that. IOW
- select with an ordering that groups the records of the same FKontrakt and
FHonorar and retrieves those in order of FDelkontrakt.
- in each set of FKontrakt and FHonorar, save the value of the first nonzero
GjenstMinHon and the current rowid (call it currentRowid).
- for each record after, save the rowid (call it savedRowid).
- when you encounter the end of the set then update the savedRowid with the
value and the currentRowid with zero.
Not only will such logic do the move, it will be faster. Your current logic
moves the value from record X to X+1 to X+2.... to X+n. And it doesn't even do
that very well, since there is no ordering in your cursor, you might update
things in the wrong order. Boy, now I see why the replies drifted off. I hate
to say this but your code is ugly.
Have I guessed the purpose correctly?
--
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family: http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost
Received on Wed Mar 26 2003 - 15:45:56 CST
