Retrieve multiple rows

When any chance exists that a query could return more than one row, the program must execute the query differently. Multirow queries are handled in two stages. First, the program starts the query. (No data is returned immediately.) Then the program requests the rows of data one at a time.

These operations are performed using a special data object called a cursor. A cursor is a data structure that represents the current state of a query. The following list shows the general sequence of program operations:
  1. The program declares the cursor and its associated SELECT statement, which merely allocates storage to hold the cursor.
  2. The program opens the cursor, which starts the execution of the associated SELECT statement and detects any errors in it.
  3. The program fetches a row of data into host variables and processes it.
  4. The program closes the cursor after the last row is fetched.
  5. When the cursor is no longer needed, the program frees the cursor to deallocate the resources it uses.

These operations are performed with SQL statements named DECLARE, OPEN, FETCH, CLOSE, and FREE.