SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Static SQL Cursors
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
6-4
Declare Required Host Variables
Declare Required Host Variables
In an SQL Declare Section, declare any host variables you specify in the query
expression of the DECLARE CURSOR declaration:
•
Before the DECLARE CURSOR declaration
•
Within the same scope as the SQL statements that refer to them
Declare the Cursor
Use the DECLARE CURSOR declaration to name a cursor and associate it with a
query expression. You can specify a row order for the result table of the query
expression. You can also specify the result table as read-only or to enable the update
of specific columns. Use this general syntax:
For complete syntax, see the DECLARE CURSOR Declaration in the SQL/MX
Reference Manual.
The name of a static cursor is an SQL identifier. The query expression typically
consists of a SELECT statement that specifies the rows that a subsequent FETCH
statement retrieves, one row at a time. The FETCH statement can also store the
retrieved table values into host variables, which your host language program can then
process. For example, your program can list, update, delete, or save the values in an
array.
You code a DECLARE CURSOR declaration:
•
In listing order before other SQL statements that refer to the cursor, including the
OPEN, FETCH, DELETE, UPDATE, and CLOSE statements
•
Within the scope of other SQL statements that refer to the cursor
Example
This example declares a read-only cursor named get_name_address that accesses
the CUSTOMER table. The query expression specifies the name and address of all
customers within a certain range of zip codes. The BETWEEN clause specifies the
range, and the ORDER BY clause sorts the rows by zip code:
EXEC SQL BEGIN DECLARE SECTION;
char first_postcode[11], last_postcode[11];
...
EXEC SQL END DECLARE SECTION;
...
EXEC SQL DECLARE get_name_address CURSOR FOR
SELECT custname,street,city,state,postcode
FROM customer
WHERE postcode BETWEEN :first_postcode AND :last_postcode
DECLARE cursor-name CURSOR FOR query-expression
[ORDER BY sort-specification]
[FOR {READ ONLY | UPDATE [OF column-name-list]}]
C