layout: global title: FETCH statement displayTitle: FETCH statement license: | Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Fetches the next row from an open cursor into variables.

The FETCH statement retrieves one row at a time from the cursor's result set and assigns column values to the specified variables. If no more rows are available, the CURSOR_NO_MORE_ROWS condition is raised (SQLSTATE '02000').

Syntax

FETCH [ [ NEXT ] FROM ] cursor_name INTO variable_name [, ...]

Parameters

  • cursor_name

    The name of an open cursor. The cursor can be optionally qualified with a compound statement label (e.g., outer_label.my_cursor).

  • NEXT FROM

    Optional keywords. NEXT and FROM are syntactic sugar and do not affect behavior. Only forward fetching is supported.

  • variable_name

    A local or session variable to receive column values. The number of variables must match the number of columns in the cursor's result set, with one exception:

    • If exactly one variable is specified and it is a STRUCT type, and the cursor returns multiple columns, the column values are assigned to the struct's fields by position.

    Column data types must be compatible with the target variables (or struct fields) according to store assignment rules.

Examples

-- Basic fetch into variables
> BEGIN
    DECLARE x INT;
    DECLARE y STRING;
    DECLARE my_cursor CURSOR FOR
      SELECT id, 'row_' || id FROM range(3);

    OPEN my_cursor;
    FETCH my_cursor INTO x, y;
    VALUES (x, y);
    CLOSE my_cursor;
  END;
0|row_0

-- Fetch multiple rows with REPEAT loop
> BEGIN
    DECLARE x INT;
    DECLARE done BOOLEAN DEFAULT false;
    DECLARE total INT DEFAULT 0;
    DECLARE sum_cursor CURSOR FOR SELECT id FROM range(5);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

    OPEN sum_cursor;
    REPEAT
      FETCH sum_cursor INTO x;
      IF NOT done THEN
        SET total = total + x;
      END IF;
    UNTIL done END REPEAT;
    CLOSE sum_cursor;

    VALUES (total);
  END;
10

-- Fetch into a struct variable
> BEGIN
    DECLARE result STRUCT<id: INT, name: STRING>;
    DECLARE struct_cursor CURSOR FOR
      SELECT id, 'name_' || id FROM range(3);

    OPEN struct_cursor;
    FETCH struct_cursor INTO result;
    VALUES (result.id, result.name);
    CLOSE struct_cursor;
  END;
0|name_0

-- Using NEXT FROM (optional syntax)
> BEGIN
    DECLARE x INT;
    DECLARE cursor1 CURSOR FOR SELECT id FROM range(3);

    OPEN cursor1;
    FETCH NEXT FROM cursor1 INTO x;
    VALUES (x);
    CLOSE cursor1;
  END;
0

-- Qualified cursor name with label
> BEGIN
    outer_lbl: BEGIN
      DECLARE outer_cur CURSOR FOR SELECT id FROM range(5);
      DECLARE x INT;

      OPEN outer_cur;

      inner_lbl: BEGIN
        FETCH outer_lbl.outer_cur INTO x;
        VALUES (x);
      END;

      CLOSE outer_cur;
    END;
  END;
0

-- Exit handler for NOT FOUND
> BEGIN
    DECLARE x INT;
    DECLARE my_cursor CURSOR FOR SELECT id FROM range(2);

    DECLARE EXIT HANDLER FOR NOT FOUND
      BEGIN
        VALUES ('No more rows');
      END;

    OPEN my_cursor;
    FETCH my_cursor INTO x;
    FETCH my_cursor INTO x;
    FETCH my_cursor INTO x; -- Triggers EXIT handler
    VALUES ('This will not execute');
    CLOSE my_cursor;
  END;
No more rows

-- Specific CURSOR_NO_MORE_ROWS handler
> BEGIN
    DECLARE x INT DEFAULT 0;
    DECLARE done BOOLEAN DEFAULT false;
    DECLARE count INT DEFAULT 0;
    DECLARE my_cursor CURSOR FOR SELECT id FROM range(3);

    DECLARE CONTINUE HANDLER FOR CURSOR_NO_MORE_ROWS SET done = true;

    OPEN my_cursor;
    WHILE NOT done DO
      FETCH my_cursor INTO x;
      IF NOT done THEN
        SET count = count + 1;
      END IF;
    END WHILE;
    CLOSE my_cursor;

    VALUES (count);
  END;
3

Notes

  • The cursor must be opened with OPEN before calling FETCH. Attempting to fetch from a closed cursor raises a CURSOR_NOT_OPEN error.
  • Each FETCH advances the cursor position by one row.
  • When no more rows are available, FETCH raises the CURSOR_NO_MORE_ROWS condition:
    • SQLSTATE: '02000'
    • Error condition: CURSOR_NO_MORE_ROWS
    • This is caught by NOT FOUND handlers (which catch all SQLSTATE '02xxx' conditions)
  • If no CONTINUE HANDLER or EXIT HANDLER is declared for NOT FOUND, the completion condition is silently ignored and execution continues. This allows scripts to continue after exhausting a cursor.
  • Type compatibility follows store assignment rules:
    • Implicit casts are applied when possible
    • Incompatible types raise a type mismatch error
  • Variables can be local variables declared in the compound statement or session variables created with DECLARE VARIABLE at the session level.

Related articles