Yet another blog
What are cursors and what are the situations you will use them
Cursors provide a mechanism by which a database client iterates over the records in a database.
Cursor are for keeping a stable resultset which you can retrieve row-by-row. They are implicitly created when your query is run, and closed when it’s finished.
Of course keeping such a resultset requires some resources:
Keeping a cursor open is like keeping a fridge door open
You don’t do it for hours without necessity, but it does not mean you should never open your fridge.
That means that:
- You don’t get your results row-by-row and sum them: you call the
- You don’t execute whole query and get the first results from the cursor: you append a
rownum <= 10condition to your query
Cursors are useful when
1) you need to do something that you cannot do with a set operation, or
2) it doesn’t make sense to do the same work by making iterative calls from the application layer.
3) Or sometimes you have a procedure that must remain on the database layer, and you simply can’t break back out to the app layer midstream to iterate over some result set.
The bottom line is, avoid them if you possibly can, and if you can’t, use them minimally and wisely.