Yet another blog

Tag Archives: cursors in SQL

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: locks, latches, memory, even disk space.

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 SQL‘s SUM instead.
  • You don’t execute whole query and get the first results from the cursor: you append a rownum <= 10 condition 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.