Share via

FETCH statement

Applies to: check marked yes Databricks Runtime 18.1 and above

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').

This statement may only be used within a compound statement.

Note

For FETCH, casting errors (for example, DATATYPE_MISMATCH) can be raised when assigning column values to variables. Runtime errors such as DIVIDE_BY_ZERO may also occur and can be unrelated to the row being fetched, due to the set-oriented nature of SQL processing.

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; otherwise ASSIGNMENT_ARITY_MISMATCH is raised:

    • 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.

Notes

When no more rows are available, FETCH raises the CURSOR_NO_MORE_ROWS condition (SQLSTATE '02000'). This is a completion condition, not an exception: it does not abort execution. A NOT FOUND or CURSOR_NO_MORE_ROWS handler can process it; NOT FOUND catches all SQLSTATE '02xxx' conditions.

Fetching from a cursor that is not open raises CURSOR_NOT_OPEN. Type compatibility follows store assignment rules: incompatible types raise a DATATYPE_MISMATCH error; implicit casts are applied when possible.

Variables can be local variables declared in the compound statement or session variables created with DECLARE VARIABLE at the session level.

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
  • Last updated on