Selecting a Single Row
Go Up to Working with Data
An operation that retrieves a single row of data is called a singleton select. To retrieve a single row from a table, to retrieve a column defined with a unique index, or to select an aggregate value like COUNT() or AVG() from a table, use the following SELECT statement syntax:
SELECT <col> [, <col> ...] INTO :variable [, :variable ...] FROM table WHERE <search_condition>;
The mandatory INTO clause specifies the host variables where retrieved data is copied for use in the program. Each host variable’s name must be preceded by a colon (:). For each column retrieved, there must be one host variable of a corresponding data type. Columns are retrieved in the order they are listed in the SELECT clause, and are copied into host variables in the order the variables are listed in the INTO clause.
The WHERE clause must specify a search condition that guarantees that only one row is retrieved. If the WHERE clause does not reduce the number of rows returned to a single row, the SELECT fails.
To select data from a table, a user must have
SELECT privilege for a table, or a stored procedure invoked by the user’s application must have SELECT privileges for the table.In the following example, the SELECT retrieves information from the
DEPARTMENT table for the Publications department:
EXEC SQL SELECT DEPARTMENT, DEPT_NO, HEAD_DEPT, BUDGET, LOCATION, PHONE_NO INTO :deptname, :dept_no, :manager, :budget, :location, :phone FROM DEPARTMENT WHERE DEPARTMENT = 'Publications';
When SQL retrieves the specified row, it copies the value in DEPARTMENT to the host variable, deptname, copies the value in DEPT_NO to :dept_no, copies the value in HEAD_DEPT to :manager, and so on.