The single-row SELECT statement extracts only a single-row of retrieval results from a table.

Because a cursor need not be used when the single-row SELECT statement is used, you can retrieve the table with only one SQL statement.

The single-row SELECT statement is effective when used in the cases listed below. You can also dynamically execute a single-row SELECT statement that is constructed during UAP execution.

• You know that the retrieval results will be contained within a single-row
• You use a set function without grouping (using a GROUP BY clause)

Even when a single-row is retrieved, using a cursor results in better processing efficiency for updating or deleting the retrieved row. You should consider whether the single-row SELECT statement or the cursor will be used.

If retrieval results include multiple rows, the UAP cannot receive them all at once. A cursor is used to extract one row at a time. The flow from cursor declaration to retrieval completion is described as follows.

1. Execute DECLARE CURSOR to declare a cursor.
2. Execute the OPEN statement to open and use the declared cursor.
3. Execute the FETCH statement to position the cursor at the first row of the retrieval results. Embedded variables specified by the INTO clause of the FETCH statement are used to extract the retrieval results.
4. Execute the FETCH statement to advance the cursor to the next row (the retrieval results are extracted one row at a time in this manner).
5. Repeat the operation in step 4 until there are no more rows to be retrieved.
6. When the retrieval is completed, execute the CLOSE statement to close the cursor.

Use the dynamic SELECT statement to extract multiple retrieval results through dynamic SQL execution. To extract retrieval results with the dynamic SELECT statement, you must either declare a cursor in advance or allocate a cursor by using the ALLOCATE CURSOR statement. Once you declare or allocate a cursor, use the PREPARE statement to preprocess the SQL statements that are constructed during UAP execution. You can then perform the same operations as in normal retrieval using a cursor.