Progress
SQL-89
Guide and Reference


Using Cursors

In interactive SQL, the SELECT statement retrieves multiple rows of data and sends the results directly to the terminal. However, the basic SELECT statement cannot store and process multiple rows. Therefore, you must step through the rows that the SELECT statement chooses and manipulate them individually using a cursor.

A cursor is a pointer used to search through a retrieval set, pointing to each row in the set, one at a time. When a cursor is pointing to a row, it is positioned on that row. You can then update or delete the positioned row using the positioned forms of the UPDATE and DELETE statements.

Cursors let you retrieve column values from a row using a SELECT statement and let you assign the column values to procedure variables. To manipulate or update row values in the retrieval set, you must always use a cursor with each SELECT statement (except the singleton SELECT). Without cursors, there is no way to process individual rows.

Defining a cursor for a SELECT operation is a two-step process in which you declare the cursor with the DECLARE CURSOR statement and then open the cursor with the OPEN statement. Once you open the cursor, use the FETCH statement to retrieve the data. When you are finished with a cursor, close it with the CLOSE statement. You do not have to declare the cursor again if you reselect the data associated with it by reopening the cursor.

You can declare and open more than one cursor at a time to use with a single table, and there is no limit to the number of cursors you can declare in your procedure. However, you can use a cursor only in the procedure in which it is declared.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095