You use SELECT to read precisely one row in a table. The column values read are stored in the output destination.
If a derived table contains more than one row, the SELECT statement does not read a row and an appropriate SQLSTATE is set. If you want to read derived tables with more than one row, you must use a cursor.
In order to execute a SELECT statement, you must own the table in which you are querying values, or you must have the SELECT privilege for the table involved.
SELECT [ALL | DISTINCT] select_list
[INTO parameter-declaration [, parameter-declaration ]...
FROM table_specification ,...
[WHERE search_condition ]
[GROUP BY column ,...]
[HAVING search_condition ]
parameter-declaration ::=
{
: host_variable [[INDICATOR] : indicator_variable ] |
routine_parameter |
local_variable
}
With the exception of the INTO clause, the clauses of the SELECT statement are defined exactly as they are for the SELECT expression and are described in the section "SELECT expression"ff.
INTO
Only for static SELECT statements.
In the case of a static SELECT statement or a SELECT statement in a procedure, you must specify the output destination that is to be assigned the column values of the derived row in the INTO clause.
Indicates where the values read are to be stored.
:host_variable, routine_parameter, local_variable
Name of a host variable (if the statement is not part of a routine) or name of a procedure parameter of the type INOUT or OUT or of a local variable (if the statement is part of a routine). The column value of the derived row is assigned to the specified output destination.
The data type must be compatible with the data type of the corresponding derived column (see section "Reading values into host variables or a descriptor area"). If a derived column is an aggregate with several elements, the corresponding host variable must be a vector with the same number of elements.
The number of specified output destinations must be the same as the number of columns in the SELECT list of the cursor description. The value of the nth column in the SELECT list is assigned to the nth output destination in the INTO clause. If the value to be assigned is the NULL value, the output destination is not set.
If there is no derived row or more than one derived row, no output destination is set.
If there is no derived row, an SQLSTATE is set that can be handled with WHENEVER NOT FOUND. If there is more than one derived row, an SQLSTATE is set that can be handled with WHENEVER SQLERROR.
indicator_variable
Name of the indicator variable for the preceding host variable. If the host variable is a vector, the indicator variable must also be a vector with the same number of elements.
The indicator value indicates whether the NULL value was transferred or whether data was lost:
0 -1 > 0 | The host variable contains the value read. The assignment was error free. The value to be assigned is the NULL value. For alphanumeric or national values: |
indicator_variable omitted:
If the value to be assigned is the NULL value, an appropriate SQLSTATE is set.
Dynamic SELECT statement
You must not specify an INTO clause in a dynamic SELECT statement. Instead, you specify the INTO clause with the host variables or SQL descriptor area for receiving the derived values in the EXECUTE statement with which you execute the dynamic SELECT statement.
Example
Read the name and VAT rate of the service with the specified service number and store this information in the host variables SERVICE_TEXT and VAT.
The service number is defined by the host variable SERVICE_NUM. Because the service number is unique within the SERVICE table, you can be sure that the query will return only one row.
 |
|