SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-19
Multirow SELECT Statement
* Process the retrieved values in the host variables.
...
Multirow SELECT Statement
When used with a cursor, a SELECT statement can return multiple rows from a table or
protection view, one row at a time. A cursor uses a FETCH statement to retrieve each
row and store the selected column values in host variables. The program can then
process the values (for example, list or save them in an array).
To execute a SELECT statement, a process started by a program must have read
access to all tables, protection views, and the underlying tables of shorthand views
used in the statement. For information about process access, see Required
Access Authority on page 7-1.
All statements that refer to the cursor, including the DECLARE CURSOR, OPEN,
FETCH, and CLOSE statements, must be within the same scope.
This example uses the GET-NAME-ADDRESS cursor to return the name and address
of all customers within a certain range from the CUSTOMER table. For data
consistency, the SELECT statement includes the REPEATABLE ACCESS clause to
lock the rows. The BETWEEN clause specifies the range of zip codes, and the
ORDER BY clause sorts the rows by zip code (POSTCODE).
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 HOST-VARIABLES.
02 BEGIN-CODE PIC X(10)
02 END-CODE PIC X(10)
...
EXEC SQL INVOKE =CUSTOMER AS CUSTOMER-REC END-EXEC.
...
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL DECLARE GET-NAME-ADDRESS CURSOR FOR
SELECT CUSTNAME, STREET, CITY, STATE, POSTCODE
FROM =CUSTOMER
WHERE POSTCODE BETWEEN :BEGIN-CODE AND :END-CODE
ORDER BY POSTCODE
REPEATABLE ACCESS END-EXEC.
...
PROCEDURE DIVISION.
...
EXEC SQL OPEN GET-NAME-ADDRESS END-EXEC.
* Set values for BEGIN-CODE and END-CODE.
...
1000-FETCH-A-ROW.
EXEC SQL FETCH GET-NAME-ADDRESS
INTO :CUSTNAME OF CUSTOMER-REC,
:STREET OF CUSTOMER-REC,
:CITY OF CUSTOMER-REC,
:STATE OF CUSTOMER-REC
:POSTCODE OF CUSTOMER-REC
END-EXEC.