Using a cursor

 

When SQL runs a SELECT statement, the resulting rows comprise the result table. A cursor provides a way to access a result table.

It is used within an SQL program to maintain a position in the result table. SQL uses a cursor to work with the rows in the result table and to make them available to your program. Your program can have several cursors, although each must have a unique name.

Statements related to using a cursor include the following:

• A DECLARE CURSOR statement to define and name the cursor and specify the rows to be retrieved with the embedded select statement.

• OPEN and CLOSE statements to open and close the cursor for use within the program. The cursor must be opened before any rows can be retrieved.

• A FETCH statement to retrieve rows from the cursor's result table or to position the cursor on another row.

• An UPDATE ... WHERE CURRENT OF statement to update the current row of a cursor.

• A DELETE ... WHERE CURRENT OF statement to delete the current row of a cursor.

• Types of cursors
  SQL supports serial and scrollable cursors. The type of cursor determines the positioning methods that can be used with the cursor.

• Examples: Using a cursor
  These examples show the SQL statements that you can include in a program to define and work with a serial and a scrollable cursor.

• Using the multiple-row FETCH statement
  The multiple-row FETCH statement can be used to retrieve multiple rows from a table or view with a single FETCH statement. The program controls the blocking of rows by the number of rows requested on the FETCH statement (The Override Database File (OVRDBF) command has no effect.).

• Unit of work and open cursors
  When your program completes a unit of work, it should commit or roll back the changes that you have made.

 

Parent topic:

Using SQL in different environments

 

Related reference

Updating data as it is retrieved from a table
CLOSE
DECLARE CURSOR
DELETE
FETCH
UPDATE