What is the impact of using cursors in stored procedures? Is it good to use cursors (or) good to avoid using cursors in Stored Procedures?

Please eloborate.

TIA

Cursors are the only construct that allow you to manipulate multiple rows of data within a Stored Procedure. You can do without them if:

1) you do not return any results sets
2) you only select one row of values.

You have to use cursors to return result sets. You need them if any select returns anything more than a single row.

HTH

Andy

I came across the following recently, which has raised my doubts about cursors in stored procedures...

Do give your input...

Where did you get this? What is its context?


Cursors use memory - so. what else does not?
Cursors Lock tables - only if they are designated to (e.g. for update)
Cursors are slow - define slow, I use cursors in SP all the time, they seem pretty fast to me. Very acceptable response times.
Using Cursors defeat performance optimization - What is meant here?
The last statement seems a bit far fetched.

How can a SP return a result set without use of a cursor?
If you have business logic that needs to be performed on multiple rows of a table, how do you do that without cursors.

Andy

I agree with almost everything what ARWinner says, exept one thing. That statement about "10000" SELECTs is outright incorrect. SELECT is executed once when you open a cursor; after that a FETCH (from the result set) is used to obtain data from the current row.

Unless, of course, the piece you quote does not refer to DB2 cursors but rather to some other cursors, e.g. some kind of VB construct...

When using cursors, there is the issue of “cursor materialization.” This is the situation where all the rows in the answer set must be read by DB2 and placed in a temporary table (which you cannot see) before DB2 can return the first row as a result of the first fetch. However, cursor materialization is not an issue specific to stored procedures.

Cursor materialization can be caused by several things including the use of an ORDER BY in the cursor that causes a sort of the rows. But many times an index can be used by DB2 to order the rows without a sort, even when an ORDER BY is request.

When declaring a cursor, one should consider the use of the following options as appropriate:

WITH HOLD
WITH RETURN
FOR READ ONLY or FOR UPDATE
OPTIMIZE FOR n ROWS

My quote is with reference to DB2 cursors only. So, does that mean that '10000' SELECTs are issued, instead of 1 SELECT and then FETCH?

TIA.

I'll quote myself, if you don't mind

"SELECT is executed once when you open a cursor; after that a FETCH (from the result set) is used to obtain data from the current row".