Jaa

CLOSE statement

Applies to: check marked yes Databricks Runtime 18.1 and above

Closes an open cursor and releases its resources.

The CLOSE statement closes a cursor that was previously opened with OPEN, freeing the memory and resources associated with its result set. After closing, the cursor can be reopened with OPEN to execute the query again with fresh parameter bindings.

This statement may only be used within a compound statement.

Syntax

CLOSE cursor_name

Parameters

  • cursor_name

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

Notes

Closing a cursor that is not open raises CURSOR_NOT_OPEN.

Cursors are automatically closed in the following scenarios:

  • When the compound statement exits due to an unhandled exception (see Error conditions).
  • When an EXIT handler is triggered (all cursors in the compound statement and nested compounds are closed).
  • When the compound statement that declares them exits normally.

Closing a cursor does not affect the cursor declaration. The cursor name remains in scope and can be reopened.

Examples

-- Basic cursor lifecycle
> BEGIN
    DECLARE x INT;
    DECLARE my_cursor CURSOR FOR SELECT id FROM range(3);

    OPEN my_cursor;
    FETCH my_cursor INTO x;
    VALUES (x);
    CLOSE my_cursor;
  END;
0

-- Close cursor in handler
> BEGIN
    DECLARE x INT;
    DECLARE my_cursor CURSOR FOR SELECT id FROM range(2);

    DECLARE EXIT HANDLER FOR NOT FOUND
      BEGIN
        CLOSE my_cursor;
        VALUES ('Cursor closed on completion');
      END;

    OPEN my_cursor;
    REPEAT
      FETCH my_cursor INTO x;
    UNTIL false END REPEAT;
  END;
Cursor closed on completion

-- Reopen cursor with different parameters
> BEGIN
    DECLARE result STRING DEFAULT '';
    DECLARE x INT;
    DECLARE param_cursor CURSOR FOR SELECT id FROM range(10) WHERE id = ?;

    OPEN param_cursor USING 3;
    FETCH param_cursor INTO x;
    SET result = 'First open: ' || CAST(x AS STRING);
    CLOSE param_cursor;

    OPEN param_cursor USING 7;
    FETCH param_cursor INTO x;
    SET result = result || '; Second open: ' || CAST(x AS STRING);
    CLOSE param_cursor;

    VALUES (result);
  END;
First open: 3; Second open: 7
  • Last updated on