Fetch-Row Statement (for scrollable cursors)

The fetch-row statement sets the row position of the result set to the position indicated by Expr and fetches the row. The first row in a result set is position 1. Ident is the name of the cursor used for the corresponding SQL SELECT command. The cursor used in the fetch-row statement must be declared in the same transaction as a scrollable cursor (result set cursorname). If no row with position Expr is in the result set, the eos condition is set to true.

Syntax

Stat = "fetch" Ident "row" Expr.

Example

var
v_artname : string;
v_artno, v_price : number;

dcltrans
transaction TMain
begin
c1: SelArticle();
fetch c1 next 5;
end TMain;

dclsql
SelArticle:
SELECT articlenumber, price, name
INTO :v_artno, :v_price, :v_artname
FROM article
WHERE price > 100
ORDER BY price DESC;

After execution of the SQL command SelArticle, a result set is formed containing all articles where the price is greater than 100. The row position is set to the first row in the result set. At this time the values of the first row are bound to the variables v_artno, v_price and v_artname. The fetch c1 next 5 statement fetches 5 rows sequentially forward starting from the current row position. The row position after the fetch statement is set to the sixth row in the result set. At this time the values of the sixth row are bound to the variables v_artno, v_price and v_artname.