Chad wrote:
> Thanks Martijn/Alban,
>
> This look interesting. I'll make some time to try this problem out
> using your approach.
> I have a few questions like:
> -Could I skip the FETCH FORWARD and go straight to the FETCH BACKWARD
> i.e. declare cursor to be at "Mal" and go backwards from there or is
> the cursor limited to going backward only as far as "Mal"?
> -Does the DB avoid transferring the data until the FETCH command?
Indeed it does.
> -When rows change in between opening the cursor and fetching the
> changed rows, will the FETCH retrieve the new data or is a snapshot
> taken when the cursor is declared ?
> -What about concurrency? If a cursor is kept open while other
> transactions change the same table or does it cause those writer
> transactions to block? Perhaps this is configurable.
Transactions are isolated. If data is changed in other transactions,
they don't influence the data in the transaction you're currently
looking at. I assume the same goes for cursors, it's still a query
after all.
I don't know what happens if you change the data you're looping over in
a way that the result set changes, but a test case can't be too hard to
think up. I'm talking about something like this:
OPEN cur FOR SELECT val FROM values WHERE val BETWEEN 1 AND 10 ORDER BY val;
LOOP
FETCH cur INTO record;
-- This reverses the order in which the record would be fetched
UPDATE values SET val = 11 - val WHERE val = record.val;
END LOOP;
Makes me kind of curious what happens... Would it only get to halfway
the values 1..10 and then go backwards again?
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
