What are the benefits of using database cursor?

Question

It is based on the interview question that I faced.

Very short definition can be

It can be used to manipulate the rows returned by a query.

Besides the use of the cursor (Points are listed here on MSDN), I have a question in my mind that if we can perform all the operations using query or stored procedure (if I'm not wrong, Like we can use Transact-SQL for ms-sql), is there any concrete point that we should use cursor?

Answer 1

With cursor you access one row at a time. So it is good to use it when you want manipulate with a lot of rows but with only one at a given time.

I was told at my classes, the reason to use cursor is you want to access more rows than you can fit your memory - so you can't just get all rows into a collection and then loop through it.

Answer 2

Using a cursor it is possible to read sequentially through a set of data, programmatically, so it behaves in a similar manner to conventional file access, rather than the set-based behaviour characteristic of SQL.

There are a couple of situations where this may be of use:

1. Where it is necessary to simulate file-based record access behaviour - for example, where a relational database is being used as the data storage mechanism for a piece of code that was previously written to use indexed files for data storage.

2. Where it is necessary to process data sequentially - a simple example might be to calculate a running total balance for a specific customer. (A number of relational databases, such as Oracle and SQLServer, now have analytical extensions to SQL that should greatly reduce the need for this.)

Inevitably, wikipedia has more: http://en.wikipedia.org/wiki/Database_cursor

Answer 3

One big advantage I see is that you can create a function that returns a cursor and leave all the SQL code inside the database.

Any interaction (insert/update/delete/select/...) with the database goes through a stored procedure or function, it's easy for a DB expert to analyze issues as all the SQL code your app executes is accessible in the database. Your app is just calling sps with specific parameters, it's like an API between your app and the db.

I'm using Oracle though and I'm not sure this technique can apply to any DBMS.

Answer 4

A cursor is a tool that allows you to iterate the records in a set. It has concepts of order and current record.

Generally, SQL operates with multisets: these are sets of possibly repeating records in no given order, taken as a whole.

Say, this query:

SELECT  *
FROM    a
JOIN    b
ON      b.a = a.id

, operates on multisets a and b.

Nothing in this query makes any assumptions about the order of the records, how they are stored, in which order they should be accessed, etc.

This allows to abstract away implementation details and let the system try to choose the best possible algorithm to run this query.

However, after you have transformed all your data, ultimately you will need to access the records in an ordered way and one by one.

You don't care about how exactly the entries of a phonebook are stored on a hard drive, but a printer does require them to be feed in alphabetical order; and the formatting tags should be applied to each record individually.

That's exactly where the cursors come into play. Each time you are processing a resultset on the client side, you are using a cursor. You don't get megabytes of unsorted data from the server: you just get a tiny variable: a resultset descriptor, and just write something like this:

while (!rs.EOF) {
   process(rs);
   rs.moveNext();
}

That's cursor that implements all this for you.

This of course concerns database-client interaction.

As for the database itself: inside the database, you rarely need the cursors, since, as I have told above, almost all data transformations can be implemented using set operations more efficiently.

However, there are exceptions:

• Analytic operations in SQL Server are implemented very poorly. A cumulative sum, for instance, could be calculated much more efficiently with a cursor than using the set-based operations
• Processing data in chunks. There are cases when a set based operation should be sequentially applied to a portion of a set and the results of each chunk should be committed independently. While it's still possible to do it using set-based operations, a cursor is often a more preferred way to do this.
• Recursion in the systems that do not support it natively.

You also may find this article worth reading:

• The Island of Misfit Cursors