Example: Using the OFFSET clause with a cursor

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 ONLY¹.

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.

CREATE PROCEDURE GET_CATALOG_ROWS 
     (offset_value INT DEFAULT 0, 
      page_size INT DEFAULT 1000)
    RESULT SETS 1
  BEGIN

    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;

  END

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.

CALL GET_CATALOG_ROWS (0, 100);
CALL GET_CATALOG_ROWS (500, 100);
CALL GET_CATALOG_ROWS (250, 100);

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.