CLOSE statement

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.

Syntax

CLOSE cursor_name

Parameters

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 x INT;
    DECLARE param_cursor CURSOR FOR SELECT id FROM range(10) WHERE id = ?;

    OPEN param_cursor USING 3;
    FETCH param_cursor INTO x;
    VALUES ('First open:', x);
    CLOSE param_cursor;

    OPEN param_cursor USING 7;
    FETCH param_cursor INTO x;
    VALUES ('Second open:', x);
    CLOSE param_cursor;
  END;
First open:|3
Second open:|7

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

      OPEN outer_cur;
      FETCH outer_cur INTO x;

      inner_lbl: BEGIN
        FETCH outer_lbl.outer_cur INTO x;
      END;

      CLOSE outer_lbl.outer_cur;
      VALUES ('Closed from outer scope');
    END;
  END;
Closed from outer scope

-- Processing all rows before close
> BEGIN
    DECLARE x INT;
    DECLARE done BOOLEAN DEFAULT false;
    DECLARE results STRING DEFAULT '';
    DECLARE my_cursor CURSOR FOR SELECT id FROM range(5);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

    OPEN my_cursor;
    REPEAT
      FETCH my_cursor INTO x;
      IF NOT done THEN
        SET results = results || CAST(x AS STRING) || ',';
      END IF;
    UNTIL done END REPEAT;
    CLOSE my_cursor;

    VALUES (results);
  END;
0,1,2,3,4,

Notes