SQL/MP Programming Manual for COBOL

Data Retrieval and Modification

HP NonStop SQL/MP Programming Manual for COBOL—529758-003

4-13

Steps for Using a Cursor

These steps are shown in Figure 4-1 on page 4-12. Each step is described in detail on

subsequent pages in this section.

1. Declare any host variables you plan to use with the cursor.

2. Name and define the cursor by 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 by 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 execute within the same

TMF transaction as the OPEN statement.

6. Process the column values returned from the current row to the 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.)

Access Requirements for Cursors

To use an SQL cursor, a process started by the program must have the access

authority. SQL/MP checks this authority when the program opens the cursor.

Access SQL Objects

Read Tables or protection views referred to in the SELECT statement associated with

the cursor (that is, specified in the DECLARE CURSOR statement)

Read Tables or protection views underlying the shorthand view, if the cursor refers to

a shorthand view

Write Tables referenced, if the cursor declaration includes the FOR UPDATE clause