SQL/MP Programming Manual for C

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for C—429847-008
4-15
Steps for Using a Cursor
The SQL statements used in Example 4-1 are described in detail later in this section:

DECLARE CURSOR Statement on page 4-18

OPEN Statement on page 4-19

FETCH Statement on page 4-20

Multirow SELECT Statement on page 4-21

UPDATE Statement on page 4-22

Multirow DELETE Statement on page 4-23

CLOSE Statement on page 4-24
For information about declaring host variables, see Section 2, Host Variables.
Steps for Using a Cursor
1. Declare any host variables you plan to use with the cursor.
2. Name and define the cursor using a DECLARE CURSOR statement. Follow the
conventions for an SQL identifier for the cursor name. The DECLARE CURSOR
statement also associates the cursor with a SELECT statement that specifies the
rows to retrieve.
3. Initialize any host variables you specified in the WHERE clause of the SELECT
statement in the DECLARE CURSOR statement.
4. Open the cursor using an OPEN statement. The OPEN statement determines the
result table and sorts the table if the SELECT statement includes the ORDER BY
clause. For audited tables or views, the OPEN statement also associates the
cursor with a TMF transaction.
5. Retrieve the column values from a row using the FETCH statement. The FETCH
statement positions the cursor at the next row of the result table and transfers the
column values defined in the associated SELECT statement to the corresponding
host variables. The FETCH statement also locks each row according to the access
specified by the SELECT statement.
For audited tables or views, the FETCH statement must run within the same TMF
transaction as the OPEN statement.
6. Process the column values returned from the current row to host variables. For
example, you might test a value and then delete or update the row.
7. After you process the current row, branch back to the FETCH statement and
retrieve the next row. Continue executing this loop until you have processed all
rows specified by the associated SELECT statement (and sqlcode equals 100).
8. Close the cursor using the CLOSE statement. The CLOSE statement releases the
result table established by the OPEN statement. (The FREE RESOURCES
statement also releases the result table.)