Retrieving Single Rows
If the result of a
SELECTstatement is known to be a single row, the
SELECT INTOstatement may be used as an alternative to fetching data through a cursor.
This is a much simpler programming construction, since cursors are not used and the only requirement is that host variables used in the
SELECT INTOstatement are declared in the
However, there are two disadvantages associated with
- An error occurs if the result set addressed by the search condition contains more than one row. In other words,
SELECT INTOcan only be reliably used when there is no possibility of a multi-row result set (essentially when the search condition includes the columns that form a
PRIMARY KEYcolumn or returns just the result of a set function, e.g.
- Execution of the
SELECT INTOstatement involves a check that the result set contains one single row, which may incur unnecessary overhead. Even if it is known that the result row is unique, a single
FETCHoperation through a cursor may be a more efficient implementation.
Use of a
SELECT INTOstatement is justified when the result set may contain several rows, but it is a condition for continued execution of the application program that the result row is unique. With a cursor, this would require a construction that checked that one and only one
FETCHoperation could be performed (alternatively, use a separate
SELECT COUNTwith the same search condition as the cursor). In such a case, a
SELECT INTOstatement with a check on the return code, see Handling Errors and Exceptions, is probably the preferred solution.
CALLstatement can be used to return information to the one or more host variables associated with the output parameter(s) of the procedure.
SETstatement can be used with a function invocation to return information to one host variable.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40