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.