Interview Questions   Tutorials   Discussions   Programs   

DB2 - What is the syntax required for the creation of a cursor?

asked mar August 21, 2014 11:33 AM  

What is the syntax required for the creation of a cursor?


1 Answers

answered By Mswami   0  

DECLARE CURSOR statementThe DECLARE CURSOR statement defines a cursor.


>-DECLARE--cursor-name--CURSOR--+-----------+------------------>< /p>

                                 '-WITH HOLD-'


   |              .-TO CALLER-. |       '-statement-name---'

                   '-WITH RETURN--+-----------+-'

                  '-TO CLIENT-'



Specifies the name of the cursor created when the source program is run. The name must not be the same as the name of another cursor declared in the source program. The cursor must be opened before use.


Maintains resources across multiple units of work. The effect of the WITH HOLD cursor attribute is as follows:

  • For units of work ending with COMMIT:
    • Open cursors defined WITH HOLD remain open. The cursor is positioned before the next logical row of the results table.
    • Valid operations on cursors defined WITH HOLD immediately following a COMMIT request are:
      • FETCH: Fetches the next row of the cursor.
      • CLOSE: Closes the cursor.
    • UPDATE and DELETE CURRENT OF CURSOR are valid only for rows that are fetched within the same unit of work.
    • The set of rows modified by:
      • A data change statement
      • Routines that modify SQL data embedded within open WITH HOLD cursors
  • For units of work ending with ROLLBACK:
    • All open cursors are closed.
    • All locks acquired during the unit of work are released.
    • LOB locators are freed.


This clause indicates that the cursor is intended for use as a result set from a procedure. WITH RETURN is relevant only if the DECLARE CURSOR statement is contained with the source code for a procedure. In other cases, the precompiler may accept the clause, but it has no effect.

Within an SQL procedure, cursors declared using the WITH RETURN clause that are still open when the SQL procedure ends, define the result sets from the SQL procedure. All other open cursors in an SQL procedure are closed when the SQL procedure ends.


Specifies that the cursor can return a result set to the caller. For example, if the caller is another procedure, the result set is returned to that procedure. If the caller is a client application, the result set is returned to the client application.


Specifies that the cursor can return a result set to the client application. This cursor is invisible to any intermediate nested procedures. If a function or method called the procedure either directly or indirectly, result sets cannot be returned to the client and the cursor will be closed after the procedure finishes.


Identifies the SELECT statement of the cursor. The select-statement must not include parameter markers, but can include references to host variables.


The SELECT statement of the cursor is the prepared SELECT statement identified by the statement-name when the cursor is opened. The statement-name must not be identical to a statement-name specified in another DECLARE CURSOR statement of the source program.

   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!