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.