SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-30
Single-Row SELECT
Single-Row SELECT
A single-row SELECT is a request to return a single row to the program. The INTO
clause returns the single row to one or more host variables. The row is typically
identified by a unique key value or by a unique value of a column within the row. You
can write a single SELECT statement to return the desired row, whether the identifying
value is a key value or a non-key value. Such a SELECT statement contains a
WHERE clause that uniquely identifies the row.
SELECT Using a Column Value. The following example shows a static SELECT
statement that returns only one row based on unique value of a column within the row
(a non-key value). This example:
•
Assumes that a user enters a customer number to retrieve the customer’s name
and address. (Each customer is identified by a unique customer number so that
only one customer can satisfy the query.)
•
Declares the host variable FIND_THIS_CUSTOMER to hold the value of the
customer number whose address is requested (set to 5635 in the example, but
would be entered by a user in an actual program).
•
Declares other host variables for the returned column values.
•
Uses a WHERE clause to specify that the column CUSTOMER.CUSTNUM
contains a unique value that is equal to the value of a host variable
:FIND_THIS_CUSTOMER.
•
Uses an INTO clause to return the single-row to the host variables.
•
Uses BROWSE ACCESS because it does not modify the data, there is a need to
avoid waiting for locked data, and the possibility of reading uncommitted data is not
a problem.
•
Uses the NOT FOUND condition of the WHENEVER directive to direct control to a
recovery routine (not shown here) if the customer number is not found.
When the SELECT statement is executed, the system scans the database to find the
first row with the specified value in CUSTOMER.CUSTNUM. When found, this specific
row is returned to the program. Because CUSTOMER.CUSTNUM is not a primary key,
the program then reads the rest of the table to make sure the row it found is the only
qualifying row; if it is not, the program returns an error.
{ Variable declarations: }
...
EXEC SQL BEGIN DECLARE SECTION;
TYPE
CUSTOMER_TYPE = RECORD
CUSTNUM : INT16;
CUSTNAME : FSTRING(18);
STREET : FSTRING(22);
CITY : FSTRING(14);
STATE : FSTRING(12);