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.
