Курсоры: построчная обработка результатов на стороне СУБД

Введение

Большинство SQL-операций (статьи 394-401) работают с результатом запроса целиком, как с набором — курсор даёт альтернативный, построчный способ обработки результата непосредственно на стороне СУБД (внутри хранимой процедуры/функции), что иногда необходимо для логики, естественно выражаемой через итерацию, а не через множественные операции.

Концепция

Курсор позволяет открыть результат запроса и затем перемещаться по нему построчно (FETCH), обрабатывая каждую строку отдельно внутри процедурного кода — это противоречит общему, «декларативному» духу SQL (где обычно описывается ЧТО нужно получить, а не КАК, шаг за шагом, это вычислить), и в большинстве случаев, где задачу можно выразить через обычный декларативный SQL-запрос (включая агрегатные и оконные функции, статьи 399, 404), это предпочтительнее курсора как по производительности, так и по простоте кода — курсоры оправданы прежде всего для случаев, действительно требующих построчной, процедурной обработки, не сводимой естественным образом к декларативному запросу.

Пример кода: Курсоры

-- Курсор внутри хранимой процедуры — построчная обработка с накоплением сложной логики
CREATE OR REPLACE PROCEDURE process_pending_orders()
LANGUAGE plpgsql AS $$
DECLARE
    order_cursor CURSOR FOR
        SELECT id, customer_id, total_amount FROM orders WHERE status = 'pending';
    current_order RECORD;
BEGIN
    OPEN order_cursor;

    LOOP
        FETCH order_cursor INTO current_order;
        EXIT WHEN NOT FOUND; -- условие выхода из цикла — курсор исчерпан

        -- Произвольная, потенциально сложная процедурная логика для каждой строки отдельно
        IF current_order.total_amount > 10000 THEN
            UPDATE orders SET status = 'requires_approval' WHERE id = current_order.id;
        ELSE
            UPDATE orders SET status = 'approved' WHERE id = current_order.id;
        END IF;
    END LOOP;

    CLOSE order_cursor; -- явное закрытие — освобождение ресурсов курсора
END;
$$;

-- Эквивалентная задача, решённая БЕЗ курсора через обычный декларативный SQL —
-- в большинстве случаев предпочтительный подход, проще и обычно эффективнее
UPDATE orders
SET status = CASE
    WHEN total_amount > 10000 THEN 'requires_approval'
    ELSE 'approved'
END
WHERE status = 'pending';

Пояснения к коду

Цикл OPEN/FETCH/EXIT WHEN NOT FOUND/CLOSE — стандартный, повторяющийся паттерн использования курсора внутри процедурного блока: курсор открывается, затем в цикле построчно читается (FETCH) до тех пор, пока не будут прочитаны все строки результата (NOT FOUND становится истинным, когда очередной FETCH не нашёл больше строк), и явно закрывается после завершения обработки. Сравнение с эквивалентной декларативной версией через UPDATE ... CASE WHEN ... END явно демонстрирует основной практический урок статьи — для этой конкретной, в действительности достаточно простой задачи (применение условной логики к каждой строке для определения нового значения столбца) курсор полностью избыточен, и обычный, единственный UPDATE с выражением CASE решает ту же задачу значительно проще, без процедурного цикла, и как правило эффективнее, поскольку обрабатывается СУБД как единая, оптимизируемая операция, а не как последовательность отдельных операций над каждой строкой.

Подводные камни

• Использование курсора для задачи, которая естественно выражается декларативным SQL-запросом (как в показанном сравнении) — это, возможно, самая частая практическая ошибка с курсорами: разработчики, привыкшие к императивному, процедурному стилю программирования из других языков, иногда инстинктивно тянутся к курсору как к «привычному» циклу по строкам, не замечая, что задача легко решается одним декларативным запросом, который СУБД способна выполнить значительно эффективнее, оптимизировав весь набор операций целиком, а не последовательность отдельных, изолированных шагов.
• Забытое явное закрытие курсора (CLOSE) после завершения работы с ним, особенно в путях кода с раннимvыходом (ошибка, неожиданное условие) — открытый, не закрытый курсор удерживает ресурсы (включая, в зависимости от настроек, блокировки на затронутых данных) дольше необходимого, и для надёжного, корректного кода стоит обеспечить закрытие курсора во всех путях выполнения, включая обработку исключений (через блоки EXCEPTION, аналогичные finally в обычных языках программирования).
• Использование курсора для итерации по очень большому количеству строк без оценки реальной производительности по сравнению с пакетной, декларативной обработкой — построчная обработка через курсор по своей природе медленнее набора операций, оптимизированного и выполненного СУБД как единое целое, и для действительно больших объёмов данных эта разница в производительности может стать весьма значительной, особенно если внутри цикла курсора выполняются дополнительные, не самые быстрые операции (как отдельный UPDATE на каждой итерации в примере, вместо одного пакетного UPDATE).
• Применение курсоров на уровне клиентского приложения (через QSqlQuery/аналогичные API, статья 437) там, где это путается с курсорами внутри хранимых процедур СУБД — стоит различать курсор как механизм построчного чтения результата на стороне СУБД (как в этой статье) и обычное, построчное чтение результата запроса через клиентский API на стороне приложения (которое, строго говоря, тоже часто называется «курсором» в широком смысле, но представляет собой совершенно иной, обычно вполне уместный и эффективный способ обработки результата на стороне приложения, не разделяющий проблем, обсуждаемых в этой статье именно для серверных курсоров внутри процедурной логики СУБД).

Второй блок цикла «Углублённый цикл SQL» (статьи 403-412, продвинутые запросы и проектирование) завершён. Далее следует блок 3 — производительность и индексы (413-422).