Declared Cursors
This information does not apply to result set cursors.
An embedded or stored procedure SQL SELECT statement can retrieve at most one row of data at a time. It is an error for a SELECT statement to retrieve more than one row of data in these applications.
Without knowing the number of rows to be retrieved from a request, it is impossible to know the number of host variables required to hold the results of the SELECT. Thus, only a single result row is allowed.
This is not a problem for so-called singleton SELECTs, which are SELECT statements that you write so that they return only one row. However, SQL queries frequently return multiple rows in the form of a result table or response set. This situation is one that typical programming languages are not equipped to handle.
Traditional programming languages such as COBOL, C, and PL/I are record-oriented, while relational databases and their operators are inherently set-oriented.
Cursors enable record-oriented languages to process set-oriented data. Think of a cursor as a pointer to a single data row in a result table.
Cursors use SQL statements unique to embedded SQL and stored procedures to step through the result table, which is held in a data structure known as a spool file, one row at a time.
Result Set Cursors
You can specify that a stored procedure return up to 15 result sets.
Related Topics
For more information on specifying a stored procedure to return results, see Returning Result Sets from a Stored Procedure and DECLARE CURSOR (Stored Procedures Form).