Start of change

Example: Using the OFFSET clause with a cursor

This example shows how you can use the OFFSET clause with a cursor to read a logical page of data.

For some applications, you want to read a number of rows starting at a certain position in the result set. To do this, define your cursor using the OFFSET and the FETCH n ROWS clauses. The OFFSET clause indicates how many rows to skip in the result set before returning data. The FETCH n ROWS clause indicates the maximum number of rows to return. For example, if you have a query that returns 1000 rows but you only want to consume 50 rows starting with row 701, you would use OFFSET 700 ROWS FETCH NEXT 50 ROWS ONLY1.

In this example, define a procedure to return a cursor where the caller decides the offset position and the page size. The items returned will be ordered from least expensive to most expensive.

     (offset_value INT DEFAULT 0, 
      page_size INT DEFAULT 1000)

    DECLARE catalog_page CURSOR FOR
      SELECT * FROM catalog_list 
        ORDER BY item_price 
        OFFSET offset_value ROWS
        FETCH NEXT page_size ROWS ONLY;

    OPEN catalog_page;


To call this procedure, you can pass the values for the offset and fetch clauses. If you omit the values, the defaults for the parameters will be used to always return the first 1000 rows. The following 3 calls to the procedure will return a cursor open to a result set of 100 rows skipping the number of rows identified by the first argument.


Note that the ordering for this query is not deterministic if any items in the catalog have the same price. This means that when these items cross "page" boundaries, multiple calls to the procedure could return the same item for more than one page or an item could never be returned. It is important to take this into consideration when defining an ordering for your query.

When using a cursor to read through the resulting data, you cannot read any rows prior to the OFFSET position or beyond where FETCH NEXT n ROWS ends. These cases are treated as if you reached the beginning or end of the data. If the offset value is greater than the number of rows in the result query, no rows are returned.

End of change
1 You can use this alternate supported syntax: LIMIT 50 OFFSET 700.