Getting multiple rows with each fetch

You can use the set cursor rows command to change the number of rows that are returned by fetch. However, this option does not affect a fetch containing an into clause.

The syntax for set cursor rows is:

set cursor rows number for cursor_name

number specifies the number of rows for the cursor. The number can be a numeric literal with no decimal point, or a local variable of type integer. The default setting is 1 for each cursor you declare. You can set the cursor rows option for any cursor, whether it is open or closed.

For example, you can change the number of rows fetched for the authors_crsr cursor:

set cursor rows 3 for authors_crsr

After you set the number of cursor rows, each fetch of authors_crsr returns three rows:

fetch authors_crsr
au_id       au_lname            au_fname 
----------- ------------------- --------------- 
648-92-1872 Blotchet-Halls      Reginald 
712-45-1867 del Castillo        Innes 
722-51-5424 DeFrance            Michel 
 
(3 rows affected)

The cursor is positioned on the last row fetched (the author Michel DeFrance in the example).

Fetching several rows at a time works especially well for client applications. If you fetch more than one row, Open Client or Embedded SQL buffers the rows sent to the client application. The client still sees row-by-row access, but each fetch results in fewer calls to Adaptive Server, which improves performance.