FETCH statement

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

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