Курсор базы данных

Курсор базы данных — это управляющая структура, используемая в системах управления базами данных (СУБД) для последовательной обработки строк, возвращаемых запросом. Курсор позволяет программе или хранимой процедуре получить доступ к результату запроса не как к единому набору данных, а как к отдельным записям, которые можно обрабатывать по одной. Это особенно важно в тех случаях, когда необходимо выполнить действия, зависящие от содержимого каждой конкретной строки, или когда результирующий набор слишком велик для единовременной загрузки в память.

Принцип работы и назначение

В основе работы реляционных баз данных лежит теория множеств: оператор SELECT возвращает набор строк (реляцию), который обрабатывается как единое целое. Однако для многих прикладных задач требуется построчная обработка. Курсор предоставляет механизм для итерации по этому набору.

Основные этапы работы с курсором включают:

1. Объявление (DECLARE): Создание курсора и связывание его с запросом SELECT.
2. Открытие (OPEN): Выполнение запроса и формирование результирующего набора строк, который будет доступен через курсор.
3. Выборка (FETCH): Извлечение одной строки из набора и перемещение указателя на следующую строку.
4. Закрытие (CLOSE): Освобождение ресурсов, связанных с курсором.
5. Освобождение (DEALLOCATE): Полное удаление определения курсора из памяти.

Курсоры широко применяются в хранимых процедурах, функциях и триггерах, а также в клиентских приложениях, работающих с базами данных через API (например, ODBC, JDBC, ADO.NET).

Классификация курсоров

Курсоры классифицируются по нескольким признакам, определяющим их поведение и возможности.

По направлению движения

• Однонаправленные (Forward-only): Позволяют перемещаться только вперёд по набору строк. После выборки строки вернуться к ней без повторного выполнения запроса невозможно. Это самый простой и производительный тип курсора.
• Прокручиваемые (Scrollable): Предоставляют возможность перемещаться по результирующему набору в любом направлении: вперёд, назад, к первой строке, к последней, к произвольной строке (абсолютная позиция). Это даёт большую гибкость, но требует больше ресурсов.

По чувствительности к изменениям

Эта классификация определяет, видит ли курсор изменения, вносимые другими транзакциями в данные после его открытия.

• Статические (Static): Создают снимок данных на момент открытия курсора. Изменения, вносимые другими пользователями (или самой транзакцией, но после открытия), не отображаются. Это самый безопасный, но и самый ресурсоёмкий тип, так как данные копируются во временную таблицу.
• Динамические (Dynamic): Отражают все изменения, вносимые в базовые таблицы во время существования курсора. Видимость изменений зависит от уровня изоляции транзакции. Этот тип наиболее актуален, но требует постоянного контроля блокировок и может быть медленным.
• Управляемые набором ключей (Keyset-driven): Компромиссный вариант. При открытии курсора фиксируется набор уникальных ключей строк, входящих в результирующий набор. При выборке строки курсор считывает актуальные данные по ключу. Таким образом, он видит изменения в неключевых полях, но не видит новые строки, добавленные другими пользователями, а строки, удалённые другими пользователями, становятся недоступными.

По расположению

• Курсоры на стороне сервера (Server-side): Полностью обрабатываются СУБД. Клиент получает только те строки, которые он явно запросил операцией FETCH.
• Курсоры на стороне клиента (Client-side): Весь результирующий набор данных передаётся клиентскому приложению, которое и управляет навигацией по нему. Это снижает нагрузку на сервер, но может потреблять много памяти на стороне клиента.

Примеры реализации в различных СУБД

Синтаксис и возможности курсоров различаются в зависимости от СУБД.

PostgreSQL

В PostgreSQL курсоры используются в основном в хранимых процедурах и анонимных блоках кода (PL/pgSQL). Поддерживаются как однонаправленные, так и прокручиваемые курсоры.

``sql DO $$ DECLARE cur CURSOR FOR SELECT id, name FROM users; rec RECORD; BEGIN OPEN cur; LOOP FETCH cur INTO rec; EXIT WHEN NOT FOUND; -- Обработка строки RAISE NOTICE 'User ID: %, Name: %', rec.id, rec.name; END LOOP; CLOSE cur; END $$; ``

Microsoft SQL Server (T-SQL)

T-SQL предлагает богатый набор опций для объявления курсоров, включая указание типа (статический, динамический, управляемый набором ключей) и возможности прокрутки.

```sql DECLARE @id INT, @name NVARCHAR(100);

DECLARE user_cursor CURSOR FOR SELECT id, name FROM users;

OPEN user_cursor;

FETCH NEXT FROM user_cursor INTO @id, @name;

WHILE @@FETCH_STATUS = 0 BEGIN -- Обработка строки PRINT 'User ID: ' + CAST(@id AS NVARCHAR(10)) + ', Name: ' + @name; FETCH NEXT FROM user_cursor INTO @id, @name; END

CLOSE user_cursor; DEALLOCATE user_cursor; ```

Oracle Database

В Oracle курсоры делятся на явные (объявляемые программистом) и неявные (создаваемые автоматически для операторов DML и SELECT INTO). Также существует тип SYS_REFCURSOR, позволяющий возвращать курсор как результат функции.

``sql DECLARE CURSOR cur IS SELECT id, name FROM users; rec cur%ROWTYPE; BEGIN OPEN cur; LOOP FETCH cur INTO rec; EXIT WHEN cur%NOTFOUND; -- Обработка строки DBMS_OUTPUT.PUT_LINE('User ID: ' || rec.id || ', Name: ' || rec.name); END LOOP; CLOSE cur; END; ``

Преимущества и недостатки

Преимущества

• Построчная обработка: Позволяет выполнять сложные вычисления, логику или вызовы других процедур для каждой строки индивидуально.
• Управление памятью: Обработка больших наборов данных без необходимости загружать их целиком в оперативную память клиента.
• Гибкость навигации: Прокручиваемые курсоры позволяют перемещаться по набору данных в произвольном порядке.

Недостатки

• Низкая производительность: Курсоры, как правило, работают значительно медленнее, чем операции, основанные на множествах (UPDATE, INSERT, DELETE с подзапросами). Каждый вызов FETCH может приводить к отдельному дисковому вводу-выводу.
• Блокировки: Динамические курсоры могут удерживать блокировки на строки, что снижает параллелизм работы с базой данных.
• Сложность кода: Код с использованием курсоров часто более громоздкий и сложный для понимания и сопровождения, чем эквивалентный код на основе множеств.

Альтернативы курсорам

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

• Подзапросы (Subqueries) и CTE (Common Table Expressions): Позволяют выполнить сложную логику в рамках одного оператора SQL.
• Оконные функции (Window Functions): Используются для вычисления агрегатов и ранжирования без группировки строк.
• Рекурсивные CTE: Применяются для обработки иерархических данных (например, дерево категорий).
• Массовые операции (Bulk Operations): Вставка, обновление или удаление сразу множества строк на основе условия.

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

Источники

1. Дейт К. Дж. Введение в системы баз данных. — 8-е изд. — М.: Вильямс, 2005.
2. Грофф Дж., Вайнберг П. SQL: Полное руководство. — 3-е изд. — М.: Вильямс, 2014.
3. Документация PostgreSQL: Глава 43. PL/pgSQL — Курсоры.
4. Документация Microsoft SQL Server: DECLARE CURSOR (Transact-SQL).
5. Документация Oracle Database: Database PL/SQL Language Reference — Cursors.