SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-33
Multirow SELECT
A cursor is the mechanism for dealing with a set of rows returned in sequence to a
program. To use a cursor, you use the DECLARE CURSOR, OPEN, FETCH, and
CLOSE statements as shown in the following steps. These steps are required even
when only the next single row is needed and only one FETCH is done.
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.
Initialize any host variables used in the WHERE clause of the cursor declaration.
Once the cursor is declared and the values initialized, you can open the cursor and
fetch each selected row sequentially.
2. Open the cursor using the OPEN statement.
3. Fetch each selected row into the program with the FETCH statement.
4. Close the cursor with the CLOSE statement.
The following example shows a cursor SELECT for a cursor named LISTNEXT. A row
is returned each time the FETCH statement is executed. This example retrieves all the
rows with COLUMN1 values greater than the :HOSTVAR_KEY value.
EXEC SQL
DECLARE LISTNEXT CURSOR FOR
SELECT COLUMN1,
COLUMN2,
COLUMN3
FROM TABLE1
WHERE COLUMN1 > :HOSTVAR_KEY;
...
{ Move the initial value to :HOSTVAR_KEY }
...
EXEC SQL
OPEN LISTNEXT;
{ Loop until no more rows (SQLCODE = 100): }
EXEC SQL
FETCH LISTNEXT
INTO :HOSTVAR1,
:HOSTVAR2,
:HOSTVAR3;
EXEC SQL
CLOSE LISTNEXT;
Cursor Scope. A static SQL cursor can be referred to only by the procedures in the
same compilation unit in which the cursor was declared. This means that for static SQL
programs, the DECLARE CURSOR, OPEN, FETCH, DELETE WHERE CURRENT,
UPDATE WHERE CURRENT, and CLOSE statements must be in the same
compilation unit.