SQL Programmer dabbling in PL/SQL: How do I get a persistent cursor?

I'm trying to optimize a particular piece of an application, the rest of the application is pure SQL and I'm pretty handy with straight SQL but I think to optimize this piece I'll need some PL/SQL magic which I have no idea how to implement.

Basically I want to walk through a whole table and run a piece of application code for each record. But I also want to update each record as I handle it. And Ideally I would like multiple clients to be able to walk through this table and not step on each others toes.

So far I've been doing it all in the client:  select * from tbl
 while (fetch) {
   update tbl ... where id=...
   do work
 }

This is really slow (yes the id has an index) and can't be distributed. I would rather do some sort of PL/SQL function which I could call from my application which would a) lock the next record, verify that it was unprocessed, update it to have been processed (with CURRENT OF syntax), unlock it and return the information to the client.

That would eliminate the extra round trip and index lookup from the client, it would also let me run the client from multiple machines, each one would handle records from the cursor without handling the same record twice.

It seems like this should be doable, perhaps with cursor variables or something, but I don't see how to do it. But this is my first foray into the world of PL/SQL. I've been using straight SQL so far and it has served well until now.

greg Received on Mon Jul 05 1999 - 01:26:45 CDT