Declare a select cursor

To execute a SELECT statement that returns more than one row, you must declare a select cursor. The select cursor enables the application to handle multiple rows that a query returns.

Your program must take the following actions to use a select cursor:
  1. Declare host variables to receive the values that the database server returns.

    For a prepared SELECT statement, these values are the select-list columns. For a prepared EXECUTE FUNCTION statement, these values are the return values of the user-defined function.

  2. Assemble and prepare the statement.

    A prepared SELECT statement can contain input parameters in the WHERE clause. A prepared EXECUTE FUNCTION statement can contain input parameters as function arguments.

  3. Declare the select cursor.

    The DECLARE statement associates the prepared SELECT statement with the select cursor.

  4. Execute the query.

    The OPEN statement sends any input parameters that its USING clause specifies to the database server and tells the database server to execute the SELECT statement.

  5. Retrieve the rows of values from the select cursor.

    The FETCH statement retrieves one row of data that matches the query criteria.

Restriction: Do not use the INTO clause in both a SELECT statement that is associated with a cursor and in a FETCH statement that retrieves data from the cursor. The preprocessor or the executable program cannot generate an error for this condition. Using the INTO clause in both statements, however, can generate unexpected results.