SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-12
Using the SELECT Statement in Programs
Multiple-Row (Cursor) SELECT
A multiple-row SELECT statement returns multiple rows one row at a time. This
technique is usually preferred over a single-row SELECT when retrieving multiple
rows.
A host variable cannot hold data from more than one row, so you must declare a cursor
for this type of SELECT statement. A cursor is the mechanism for dealing with a set of
rows returned in sequence to an application program. To use cursors, use these SQL
statements in your programs:
•
DECLARE CURSOR
•
OPEN
•
FETCH
•
CLOSE
Use these statements as indicated in these steps:
1. Name and define a cursor in a DECLARE CURSOR statement. The DECLARE
CURSOR statement includes a SELECT statement to describe the rows to be
returned.
2. Initialize any host variables used in the SELECT statement. After the cursor is
declared and the values initialized, you can open the cursor and fetch each
selected row sequentially.
3. Open the cursor using the OPEN statement.
4. Fetch each selected row into the program with the FETCH statement.
5. Close the cursor with the CLOSE statement.
These steps are required even when only the next single row is needed and only one
FETCH is done.
Here is a pseudocode example of a multiple-row SELECT statement using a cursor:
EXEC SQL
DECLARE listnext CURSOR FOR
SELECT partnum,
partdesc,
price,
qty_available
FROM parts
WHERE partnum > :hvkey
END-EXEC.
...
Move initial value to :hvkey
EXEC SQL
OPEN listnext
END-EXEC.
EXEC SQL