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

Introduction
HP NonStop SQL/MX Programming Manual for C and COBOL—544617-003
1-6
Declaring and Using Static SQL Cursors
rows of the result table one at a time by using the cursor. Finally, after processing the
rows, you release the result table when you close the cursor.
Examples
In these C examples, a semicolon (;) ends an embedded SQL statement. In a COBOL
program, the keyword END-EXEC ends an embedded SQL statement.
•
DECLARE CURSOR
EXEC SQL DECLARE get_customer CURSOR FOR
SELECT custname, street, city, state, postcode
FROM persnl.customer
WHERE postcode = :hv_postcode;
DECLARE CURSOR is a preprocessor declarative, not an executable statement. It
specifies that, when OPEN executes for this cursor, the SELECT statement returns
five columns of data where the rows are selected by postal code. The value of
postal code is provided by a host variable.
•
OPEN statement
EXEC SQL OPEN get_customer;
The OPEN statement establishes the result table of SELECT. The selection of the
rows is determined by the current value of the host variable or variables. OPEN
positions the cursor before the first row of the result table.
•
FETCH statement
EXEC SQL FETCH get_customer
INTO :hv_custname,:hv_street,:hv_city,
:hv_state,:hv_postcode;
The FETCH statement positions the cursor on the next row of the result table,
retrieves values from that row, and places the values in the host variables. The
cursor is positioned at the retrieved row.
•
Positioned DELETE statement
EXEC SQL DELETE FROM persnl.customer
WHERE CURRENT OF get_customer;
The DELETE statement deletes a single row at the current position of the cursor
and positions the cursor before the next row in the result table.
•
Positioned UPDATE statement
EXEC SQL UPDATE persnl.customer
SET credit = 'A1'
WHERE CURRENT OF get_customer;
The UPDATE statement updates values in a single row at the current position of
the cursor. The cursor remains positioned on the current row.
C