fetch cursor and cursor with return.

[Sankar Sabari]

Hi All,

What is difference between fetch cursor and cursor with return for please help on this.

[Sankar Sabari]

Hi,

Has someone used this before, if yes please help.

[zum13]

The most common way of using a cursor consists of a DECLARE CURSOR for a SELECT statement that will read a number of rows, an OPEN to start processing, a number of iterations of FETCH to retrieve the individual rows, then a CLOSE to terminate processing on the cursor. This will all be done within the same program.

The WITH RETURN option on a cursor declaration is used from a stored procedure and allows a particular result set to be returned to the caller. The stored procedure will declare and open a cursor then return it as a result set to the calling program. The caller uses ALLOCATE to translate the result set reference into a cursor at which point the data can be processed in the same way as before.

[Anuj Dhawan]

In addition to what zum13 said, this from the manuals can be of interest:

WITHOUT RETURN or WITH RETURN
Specifies whether the result table of the cursor is intended to be used as a result set that will be returned from a procedure. If statement-name is specified, the default is the corresponding prepare attribute of the statement. Otherwise, the default is WITHOUT RETURN.

WITHOUT RETURN
Specifies that the result table of the cursor is not intended to be used as a result set that will be returned from a procedure.

WITH RETURN
Specifies that the result table of the cursor is intended to be used as a result set that will be returned from a procedure. WITH RETURN is relevant only if the DECLARE CURSOR statement is contained within the source code for a procedure. In other cases, the precompiler might accept the clause, but it has not effect.

When a cursor that is declared using the WITH RETURN TO CALLER clause remains open at the end of a program or routine, that cursor defines a result set from the program or routine. Use the CLOSE statement to close a cursor that is not intended to be a result set from the program or routine. Although Db2 will automatically close any cursors that are not declared using with a WITH RETURN clause, the use of the CLOSE statement is recommended to increase the portability of applications.

For non-scrollable cursors, the result set consists of all rows from the current cursor position to the end of the result table. For scrollable cursors, the result set consists of all rows of the result table.

[Sankar Sabari]

Thankyou both. This is helpful.