Курсоры в хранимых процедурах MySQL

Курсор в хранимой процедуре MySQL последовательно читает строки выборки. Для безопасного завершения цикла добавьте обработчик NOT FOUND и проверяйте флаг сразу после FETCH. В статье есть готовая процедура, тестовые данные, возврат результата через OUT и SELECT, пример PDO и разбор ошибок.

Что такое курсор в MySQL и когда он нужен

Курсор связывает имя с запросом и выдает строки по одной. Процедура сохраняет промежуточное состояние в локальных переменных между итерациями.

MySQL разрешает курсоры только внутри хранимых программ: процедур, функций, триггеров и событий. В обычной SQL-сессии отдельной командой курсор не объявить. Примеры используют процедуры, которые вызываются через CALL.

Курсор MySQL имеет три свойства:

• Только чтение. Курсор не обновляет строку через свою позицию. Отдельный UPDATE по идентификатору работает по обычным правилам.
• Только вперед. Успешный FETCH получает следующую строку. Вернуться назад или перейти к произвольной позиции нельзя.
• Asensitive. Сервер может скопировать результат или читать его без копии. Алгоритм не должен рассчитывать на то, что изменения исходной таблицы появятся в уже открытом курсоре.

Если порядок обработки важен, задайте ORDER BY. При одинаковых значениях сортировки добавьте уникальный идентификатор. Синтаксис описан в документации MySQL по курсорам.

Синтаксис курсора: DECLARE, OPEN, FETCH, CLOSE

Работа с курсором проходит в четыре этапа. Объявите его в начале блока BEGIN … END, затем откройте, прочитайте строки и закройте. После OPEN строки еще нет в переменной: ее получает первый FETCH.

В каждом блоке соблюдайте порядок объявлений:

1. Объявите локальные переменные и условия.
2. Объявите курсоры.
3. Объявите обработчики.
4. Напишите исполняемые команды.

Число переменных в FETCH должно совпадать с числом столбцов запроса. Значения распределяются по позиции, поэтому порядок переменных тоже важен.

Шаблон процедуры с курсором и обработчиком NOT FOUND

Когда строки заканчиваются, FETCH вызывает условие NOT FOUND. Обработчик ставит флаг, а цикл выходит до обработки старых значений переменных.

Полный шаблон с LOOP и LEAVE

Для примеров нужен установленный MySQL Server и учетная запись с правами создания учебной базы и процедур. Выполните листинг в клиенте mysql: процедура складывает числа из выборки.

CREATE DATABASE IF NOT EXISTS cursor_demo;
USE cursor_demo;

DROP PROCEDURE IF EXISTS cursor_template;

DELIMITER $$
CREATE PROCEDURE cursor_template(OUT p_total INT)
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE v_value INT;

    DECLARE cur CURSOR FOR
        SELECT 1 AS n UNION ALL SELECT 2 AS n;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET p_total = 0;
    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_value;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET p_total = p_total + v_value;
    END LOOP;

    CLOSE cur;
END$$
DELIMITER ;

CALL cursor_template(@total);
SELECT @total AS total;

Запрос вернет значение total = 3.

Обработчик CONTINUE передает выполнение команде после FETCH, поэтому цикл успевает проверить done. Затем LEAVE завершает помеченный цикл, а CLOSE закрывает курсор.

Проверяйте флаг сразу после FETCH. Неудачное чтение не дает новой строки, поэтому переменная может сохранить предыдущее значение.

DELIMITER меняет разделитель команд консольного клиента. Без него внутренние точки с запятой завершат определение процедуры раньше времени. Через PDO отправляйте SQL без строк DELIMITER.

Варианты цикла WHILE и REPEAT

LOOP с LEAVE явно разделяет чтение строки и выход. WHILE проверяет условие до итерации, поэтому после нового FETCH ему нужен дополнительный IF.

Замените блок от read_loop: LOOP до END LOOP; в шаблоне таким фрагментом:

WHILE NOT done DO
    FETCH cur INTO v_value;
    IF NOT done THEN
        SET p_total = p_total + v_value;
    END IF;
END WHILE;

REPEAT выполняет тело хотя бы один раз и проверяет условие после чтения.

REPEAT
    FETCH cur INTO v_value;
    IF NOT done THEN
        SET p_total = p_total + v_value;
    END IF;
UNTIL done
END REPEAT;

Оба варианта сохраняют результат шаблона: total = 3. При пустой выборке проверка IF NOT done пропустит обработку строки.

При повторном открытии курсора в одном вызове сбросьте done перед OPEN. Закрытие курсора не меняет локальный флаг.

Пример: обработка строк курсором

Процедура посчитает текущую серию одинаковых результатов команды. Она читает игры от последней к первой и останавливается при смене результата.

Тестовая таблица и данные

Создайте учебную базу и таблицу в отдельном окружении.

CREATE DATABASE IF NOT EXISTS cursor_demo;
USE cursor_demo;

CREATE TABLE cursor_games (
    id INT PRIMARY KEY,
    season INT NOT NULL,
    winlose ENUM('W', 'L') NOT NULL
);

INSERT INTO cursor_games (id, season, winlose) VALUES
    (1, 1, 'W'),
    (2, 1, 'L'),
    (3, 1, 'W'),
    (4, 1, 'L'),
    (5, 1, 'L'),
    (6, 1, 'L'),
    (7, 2, 'W'),
    (8, 2, 'W');

SELECT id, season, winlose
FROM cursor_games
ORDER BY id;

Запрос покажет 8 игр, распределенных между двумя сезонами. В примере больший id означает более позднюю игру. В рабочей таблице сортируйте по дате и уникальному идентификатору.

Процедура расчета текущей серии

Создайте процедуру в выбранной базе cursor_demo.

DROP PROCEDURE IF EXISTS current_streak;

DELIMITER $$
CREATE PROCEDURE current_streak(
    IN p_season INT,
    OUT p_status CHAR(1),
    OUT p_length INT
)
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE v_result CHAR(1);

    DECLARE cur CURSOR FOR
        SELECT g.winlose
        FROM cursor_games AS g
        WHERE g.season = p_season
        ORDER BY g.id DESC;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET p_status = NULL;
    SET p_length = 0;
    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_result;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF p_length = 0 THEN
            SET p_status = v_result;
        ELSEIF v_result <> p_status THEN
            LEAVE read_loop;
        END IF;

        SET p_length = p_length + 1;
    END LOOP;

    CLOSE cur;
END$$
DELIMITER ;

CALL current_streak(1, @status, @length);
SELECT @status AS status, @length AS streak_length;

Для сезона 1 процедура вернет L и 3. Курсор прочитает игры с идентификаторами 6, 5 и 4, а на игре 3 остановится при смене результата.

Проверьте конец данных и пустую выборку:

CALL current_streak(2, @status, @length);
SELECT @status AS status, @length AS streak_length;

CALL current_streak(99, @status, @length);
SELECT @status AS status, @length AS streak_length;

Как вернуть результат из процедуры

Процедура MySQL передает отдельные значения через параметры OUT и отправляет набор строк обычным SELECT. Выбирайте форму по задаче вызывающего кода.

OUT-параметры и пользовательские переменные

Передайте пользовательские переменные с символом @, затем прочитайте их в том же соединении:

CALL current_streak(1, @status, @length);
SELECT @status AS status, @length AS streak_length;

Пользовательские переменные принадлежат текущей сессии. Другое соединение их значений не получит.

Набор строк через SELECT

Обычный SELECT внутри процедуры возвращает набор строк клиенту. Конструкция SELECT … INTO записывает данные в переменные и набора не создает.

DROP PROCEDURE IF EXISTS season_games;

DELIMITER $$
CREATE PROCEDURE season_games(IN p_season INT)
BEGIN
    SELECT g.id, g.winlose
    FROM cursor_games AS g
    WHERE g.season = p_season
    ORDER BY g.id DESC;
END$$
DELIMITER ;

CALL season_games(1);

Вызов вернет 6 строк для сезона 1. Временная таблица для прямого SELECT не нужна. Она пригодится, если цикл постепенно накапливает вычисленные строки, которые затем нужно вернуть одной выборкой.

Не помещайте возвращающий строки SELECT в каждую итерацию. Каждый такой запрос создаст отдельный набор результатов для клиента.

Вызов процедуры с курсором из PHP (PDO)

Для примера нужны PHP, расширение pdo_mysql и созданные процедуры. PDO получает результаты CALL через объект PDOStatement.

Укажите свои имя пользователя и пароль вместо demo_user и replace_with_password. Учетная запись должна иметь доступ к учебной базе и право вызова процедур. Сохраните код в файл cursor_demo.php и запустите командой php cursor_demo.php.

<?php
$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=cursor_demo;charset=utf8mb4',
    'demo_user',
    'replace_with_password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

$stmt = $pdo->prepare('CALL current_streak(?, @status, @length)');
$stmt->execute([1]);
while ($stmt->nextRowset()) {
    // У этой процедуры нет полезных наборов строк.
}
$stmt->closeCursor();

$outStmt = $pdo->query(
    'SELECT @status AS status, @length AS streak_length'
);
$out = $outStmt->fetch();
$outStmt->closeCursor();

if ($out['status'] === null) {
    echo "Нет игр\n";
} else {
    $label = $out['status'] === 'L' ? 'поражений' : 'побед';
    printf("Текущая серия: %d %s\n", (int) $out['streak_length'], $label);
}

$stmt = $pdo->prepare('CALL season_games(?)');
$stmt->execute([1]);
$games = $stmt->fetchAll();
while ($stmt->nextRowset()) {
    // Дополнительные наборы этому примеру не нужны.
}
$stmt->closeCursor();

foreach ($games as $game) {
    printf("Игра %d: %s\n", (int) $game['id'], $game['winlose']);
}

Скрипт выведет строку «Текущая серия: 3 поражений» и 6 игр сезона 1. Сравнение статусов использует ===, а переводы строк записаны как \n.

После CALL может оставаться дополнительный пустой набор результатов. Обработайте оставшиеся наборы через nextRowset() и закройте результат методом closeCursor() перед следующим запросом. Эти методы описаны в документации PDOStatement::nextRowset() и PDOStatement::closeCursor().

closeCursor() закрывает результат PDO. SQL-оператор CLOSE cur внутри процедуры закрывает другой, серверный курсор.

Ошибки при работе с курсорами в MySQL

Сначала найдите сообщение сервера и место сбоя. Текст ошибки показывает, нарушено ли объявление или состояние открытого курсора.

Сообщение cursor not exists не относится к текстам ошибок MySQL для необъявленного курсора. Ищите Undefined CURSOR. Справочник сообщений находится в MySQL Server Error Message Reference.

Цикл не заканчивается после последней строки

Без обработчика NOT FOUND окончание выборки не устанавливает ваш флаг. Если цикл продолжает работу, переменные могут сохранить прежние значения.

Добавьте DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; после объявления курсора. Поставьте IF done THEN LEAVE read_loop; END IF; сразу после FETCH. Вызов для пустой выборки должен завершиться без обработки строки.

Вложенный курсор обрывает внешний цикл

Общий обработчик может установить общий флаг после окончания внутренней выборки. Внешний цикл увидит этот флаг и завершится раньше времени. Если оба цикла используют один done, сбросьте его после внутреннего цикла перед продолжением внешнего.

Надежнее выделить внутреннему курсору отдельный блок BEGIN … END. Объявите внутри свой флаг, курсор и обработчик. Используйте имена outer_done и inner_done, чтобы состояния не смешивались.

SELECT INTO неожиданно устанавливает done

Условие NOT FOUND возникает и при SELECT … INTO, который не нашел строк. Если запрос стоит в теле цикла, общий обработчик может ошибочно установить флаг основного курсора.

Поместите дополнительный запрос во вложенный блок. Назначьте ему отдельный обработчик и отдельный флаг. Тогда внешний done будет отражать только окончание основного FETCH. Область действия обработчиков описана в документации DECLARE … HANDLER.

Когда курсор не нужен

Построчная обработка добавляет расходы на чтение и выполнение тела цикла. Сначала попробуйте решить задачу одним запросом с агрегацией, обновлением или оконными функциями.

MySQL 8.0 поддерживает оконные функции. Прием gaps-and-islands группирует соседние одинаковые результаты и позволяет получить текущую серию без курсора.

WITH numbered AS (
    SELECT
        g.id,
        g.winlose,
        ROW_NUMBER() OVER (ORDER BY g.id DESC) AS rn,
        ROW_NUMBER() OVER (
            PARTITION BY g.winlose ORDER BY g.id DESC
        ) AS result_rn
    FROM cursor_games AS g
    WHERE g.season = 1
), islands AS (
    SELECT
        winlose,
        rn - result_rn AS island_id,
        MIN(rn) AS first_rn,
        COUNT(*) AS streak_length
    FROM numbered
    GROUP BY winlose, rn - result_rn
)
SELECT
    MAX(CASE WHEN first_rn = 1 THEN winlose END) AS status,
    COALESCE(
        MAX(CASE WHEN first_rn = 1 THEN streak_length END),
        0
    ) AS streak_length
FROM islands;

Запрос вернет L и 3 для сезона 1. Первый ROW_NUMBER() нумерует все игры, второй нумерует отдельно победы и поражения. Разность счетчиков остается постоянной внутри одной серии. Группа с first_rn = 1 содержит последнюю игру и текущую серию.

Курсор оправдан, когда следующее действие зависит от накопленного состояния или вызывает другую процедуру с параметрами текущей строки. Если задачу решают оконные функции и обычная группировка, запрос обычно проще поддерживать.

Когда выбирать курсор и как проверить процедуру

Выбирайте курсор для задач с последовательной обработкой и промежуточным состоянием. Перед использованием процедуры проверьте три сценария: смену результата, исчерпание данных и пустую выборку.