Обзор курсоров

Описание

Курсор — это структура, которая позволяет последовательно переходить по записям и выполнять обработку на основе результата.

MariaDB разрешает использование курсоров внутри сохранённых программ, и курсоры MariaDB являются нескроллируемыми, только для чтения и нечувствительными.

• Нескроллируемость означает, что строки могут быть извлечены только в порядке, указанном оператором SELECT. Строки нельзя пропускать, нельзя переходить к конкретной строке и нельзя извлекать строки в обратном порядке.
• Только для чтения означает, что данные не могут быть обновлены через курсор.
• Нечувствительность означает, что курсор указывает на фактические исходные данные. Этот тип курсора быстрее альтернативного, чувствительного курсора, поскольку данные не копируются во временную таблицу. Однако изменения в данных, используемых курсором, повлияют на данные курсора.

Курсоры создаются с помощью оператора DECLARE CURSOR и открываются оператором OPEN. Строки считываются с помощью оператора FETCH перед тем, как курсор будет закрыт оператором CLOSE.

При выполнении FETCH, если больше нет строк для извлечения, генерируется следующая ошибка:

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

Чтобы избежать проблем, обычно используется оператор DECLARE HANDLER. Обработчик должен обрабатывать ошибку 1329 или SQLSTATE '02000', или класс ошибок NOT FOUND.

Для курсоров допускаются только операторы SELECT, и они не могут быть помещены в переменную — поэтому они не могут быть составлены динамически. Однако можно выполнять SELECT из представления. Поскольку оператор CREATE VIEW может выполняться как подготовленный оператор, можно динамически создавать представление, к которому обращается курсор.

Начиная с MariaDB 10.3.0, курсоры могут иметь параметры. Параметры курсора могут появляться в любой части select_statement оператора DECLARE CURSOR, где разрешены переменные хранимых процедур (список выбора, WHERE, HAVING, LIMIT и т. д.). См. DECLARE CURSOR и OPEN для синтаксиса и пример ниже:

Примеры

CREATE TABLE c1(i INT);

CREATE TABLE c2(i INT);

CREATE TABLE c3(i INT);

DELIMITER //

CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE x, y INT;
  DECLARE cur1 CURSOR FOR SELECT i FROM test.c1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.c2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO x;
    FETCH cur2 INTO y;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF x < y THEN
      INSERT INTO test.c3 VALUES (x);
    ELSE
      INSERT INTO test.c3 VALUES (y);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END; //

DELIMITER ;

INSERT INTO c1 VALUES(5),(50),(500);

INSERT INTO c2 VALUES(10),(20),(30);

CALL p1;

SELECT * FROM c3;
+------+
| i    |
+------+
|    5 |
|   20 |
|   30 |
+------+

Начиная с MariaDB 10.3.0

DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(10));

INSERT INTO t1 VALUES (1,'old'),(2,'old'),(3,'old'),(4,'old'),(5,'old');

DELIMITER //

CREATE PROCEDURE p1(min INT,max INT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE va INT;
  DECLARE cur CURSOR(pmin INT, pmax INT) FOR SELECT a FROM t1 WHERE a BETWEEN pmin AND pmax;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  OPEN cur(min,max);
  read_loop: LOOP
    FETCH cur INTO va;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO t1 VALUES (va,'new');
  END LOOP;
  CLOSE cur; 
END;
//

DELIMITER ;

CALL p1(2,4);

SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|    1 | old  |
|    2 | old  |
|    3 | old  |
|    4 | old  |
|    5 | old  |
|    2 | new  |
|    3 | new  |
|    4 | new  |
+------+------+

См. также

• DECLARE CURSOR
• OPEN cursor_name
• FETCH cursor_name
• CLOSE cursor_name
• Курсоры в режиме Oracle