SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-34
SELECT
Executing a Multi-Row SELECT Statement
A multi-row SELECT statement returns multiple rows one row at a time. Because host
variables cannot hold more than the first row, you must declare this type of SELECT
statement as a cursor.
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 these 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.
This example shows a cursor SELECT for a cursor named LISTNEXT:
EXEC SQL
DECLARE listnext CURSOR FOR
SELECT column1,
column2,
column3
FROM table
WHERE column1 > :host^var^key ;
...
! Move the initial value to :host^var^key
EXEC SQL
OPEN listnext ;
EXEC SQL
FETCH listnext
INTO :host^var1,
:host^var2,
:host^var3 ;
EXEC SQL
CLOSE listnext ;
A row is returned each time the FETCH statement is executed. This example retrieves
all the rows with COLUMN1 values greater than the :HOST^VAR^VKEY value.