Receive more than one row

Statements that return one row of data include a singleton SELECT and an EXECUTE FUNCTION statement whose user-defined function returns only one row of data. Statements that can return more than one row of data include:
  • A non-singleton SELECT.

    When a SELECT statement returns more than one row, define a select cursor with the DECLARE statement.

  • An EXECUTE FUNCTION statement whose user-defined function returns more than one row.

    When an EXECUTE FUNCTION statement executes a user-defined function that returns more than one row, define a function cursor with the DECLARE statement.

For the select or function cursor, you can use a sequential, scroll, hold, or update cursor. The following table summarizes the SQL statements that manage a select or function cursor.
Table 1. SQL statements that manage a select or function cursor
Task Select cursor Function cursor
Declare the cursor identifier DECLARE associated with a SELECT statement DECLARE associated with an EXECUTE FUNCTION statement
Execute the statement OPEN OPEN
Access a single row from the fetch buffer into the program FETCH FETCH
Close the cursor CLOSE CLOSE
Free cursor resources FREE FREE

For more information about any of these statements, see their entries in the HCL OneDB™ Guide to SQL: Syntax. You can change the size of the select or fetch buffer with the Fetch-Buffer-Size feature. For more information, see Size the cursor buffer.