Declaring cursors in an SQL procedure with nested compound statements

When you declare a cursor in an SQL procedure that has nested compound statements, you cannot necessarily reference the cursor anywhere in the procedure. The scope of the cursor is constrained to the compound statement in which you declare it.

Procedure

To declare a cursor in an SQL procedure with nested compound statements:

Specify the DECLARE CURSOR statement within the compound statement in which you want to reference the cursor. Use a cursor name that is unique within the SQL procedure.

You can reference the cursor within the compound statement in which it is declared and within any nested statements. If the cursor is declared as a result set cursor, even if the cursor is not declared in the outermost compound statement, any calling application can reference it.

Example

In the following example, cursor X is declared in the outer compound statement. This cursor can be referenced within the outer block in which it was declared and within any nested compound statements.

CREATE PROCEDURE SINGLE_CSR
   (INOUT IR1 INT, INOUT JR1 INT, INOUT IR2 INT, INOUT JR2 INT)
   LANGUAGE SQL
   DYNAMIC RESULT SETS 2
  BEGIN
    DECLARE I INT;
    DECLARE J INT;
    DECLARE X CURSOR WITH RETURN FOR --outer declaration for X
       SELECT * FROM CSRT1;

   SUB: BEGIN
          OPEN X;                   --references X in outer block
          FETCH X INTO I,J;         --references X in outer block
          SET IR1 = I;
          SET JR1 = J;
        END;

  FETCH X INTO I,J;                --references X in outer block
  SET IR2 = I;
  SET JR2 = j;
  CLOSE X;
 END