Select Cursor or Function Cursor

When an SQL statement returns more than one group of values to the program, you must declare a cursor to save the multiple groups, or rows, of data and to access these rows one at a time. You must associate one of the following SQL statements with a cursor:
  • If you associate a SELECT statement with a cursor, the cursor is called a Select cursor.

    A Select cursor is a data structure that represents a specific location within the active set of rows that the SELECT statement retrieved.

  • If you associate an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement with a cursor, the cursor is called a Function cursor.

    The Function cursor represents the columns or values that a user-defined function returns. Function cursors behave the same as Select cursors that are enabled as update cursors.

In HCL OneDB™, for compatibility with legacy applications, if an SPL function was created with the CREATE PROCEDURE statement, you can create a Function cursor with the EXECUTE PROCEDURE statement. With external functions, you must use the EXECUTE FUNCTION statement.

When you associate a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement with a cursor, the statement can include an INTO clause. However, if you prepare the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, you must omit the INTO clause in the PREPARE statement and use the INTO clause of the FETCH statement to retrieve the values from the Collection cursor.

A Select or Function cursor can scan returned rows of data and to move data row by row into a set of receiving variables, as the following steps describe:
  1. DECLARE

    Use DECLARE to define a cursor and associate it with a statement.

  2. OPEN

    Use OPEN to open the cursor. The database server processes the query until it locates or constructs the first row of the active set.

  3. FETCH

    Use FETCH to retrieve successive rows of data from the cursor.

  4. CLOSE

    Use CLOSE to close the cursor when its active set is no longer needed.

  5. FREE

    Use FREE to release the resources that are allocated for the cursor.