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@xxxxxxxxxxxxxxxxx
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 //
