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.
| 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 Informix® 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.