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

HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
6-1
6 Static SQL Cursors
In NonStop SQL/MX, a mechanism called a cursor allows an application program to
select and then retrieve a set of rows one row at a time. Each row in the set satisfies
the criteria in the search condition of the SELECT statement that specifies the cursor.
NonStop SQL/MX builds a result table to hold all the rows retrieved by executing the
SELECT statement and then uses a cursor to make rows from the result table
available to your program. The cursor identifies the current row of the result table.
The SELECT statement that specifies the cursor must be within a DECLARE CURSOR
declaration, which defines and names the cursor, identifying the set of rows to be
retrieved.
The result table of a cursor is processed like a sequential data set. First open the
cursor with an OPEN statement before any rows are retrieved. Then use a FETCH
statement to retrieve the cursor’s current row. The program can test the data in each
row at the current cursor position and then, if the data meets certain criteria, it can
display, update, delete, or bypass the row. Use FETCH repeatedly until all rows have
been retrieved. When you have finished processing the rows, close the cursor with a
CLOSE statement.
This section describes:
•
DML Statements for Static SQL Cursors on page 6-1
•
Steps for Using a Static SQL Cursor on page 6-2
•
Using Date-Time and INTERVAL Data Types on page 6-12
•
Considerations When Using a Cursor on page 6-14
DML Statements for Static SQL Cursors
The DML statements you can use with static SQL cursors are:
For detailed statement descriptions, see the SQL/MX Reference Manual.
DECLARE CURSOR Defines a cursor and associates it with a query
expression.
OPEN Opens a cursor.
FETCH Positions a cursor on the next row of a table and retrieves
values from the row.
Positioned UPDATE Updates a row from a table or view at the current cursor
position.
Positioned DELETE Deletes a row from a table or view at the current cursor
position.
CLOSE Closes a cursor.