Курсоры. 
Базы данных

Курсор — это объект, позволяющий по отдельности обрабатывать строки из результирующего набора, возвращенного оператором SELECT. Далее будут рассматриваться курсоры, поддерживаемые в языке TransactSQL. Это серверные курсоры, существующие как объекты на стороне сервера БД. Бывают также клиентские курсоры, с которыми работают при создании клиентских приложений БД.

В литературе [16, 20] отмечается, что построчная обработка набора данных с помощью курсора в подавляющем большинстве случаев выполняется существенно медленнее, чем аналогичные действия, выполняемые средствами SQL для обработки множеств строк. Поэтому курсоры рекомендуется использовать только в тех случаях, когда описание требуемых действий через операции с множествами строк явно неэффективно или вообще невозможно.

Работа с курсором обычно включает следующие шаги:

• • объявление курсора;
• • открытие курсора;
• • считывание в переменные значений атрибутов из первой записи курсора;
• • перемещение по курсору (обычно в цикле) и обработка записей курсора;
• • закрытие курсора;
• • освобождение памяти, отведенной курсору.

Объявление курсора выполняется с помощью оператора DECLARE, формат которого представлен ниже. Надо отметить, что в SQL Server этот оператор поддерживает как синтаксис стандарта ISO SQL (версия стандарта в документации [23] нс уточняется), так и синтаксис, использующий набор расширений языка Transact-SQL [20, 23, 241.

Синтаксис ISO:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR.

FOR select_statement.

[FOR (READ ONLY I UPDATE [OF column_name [,…n]]}].

[;].

Расширенный синтаксис Transact-SQL:

DECLARE cursor_name CURSOR [LOCAL | GLOBAL].

[FORWARD_ONLY | SCROLL ].

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD].

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC].

[TY PE_WARNING].

FOR select_statement.

[FOR UPDATE [OF column_name [,…n]]][;].

Далее будет использоваться расширенный синтаксис, так как он позволяет полнее реализовать возможности курсора в SQL Server.

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

" Локальный" курсор, создаваемый по умолчанию или при явном указании LOCAL, доступен только в пакете заданий, хранимой процедуре или триггере, в которых он был создан. Такой курсор неявно освобождается после завершения выполнения пакета, хранимой процедуры или триггера. Исключение составляет случай, когда курсор передается через выходной параметр (OUTPUT) хранимой процедуры. Тогда курсор освобождается при освобождении всех ссылающихся на него переменных или при выходе из «области видимости» .

FORWARD_ONLY означает, что «передвигаться» по курсору можно только вперед (доступна только команда FETCH NEXT, см. далее), т. е. каждая запись в курсоре может быть обработана не более одного раза. Если FORWARD ONLY указано без ключевых слов STATIC, KEYSET или DYNAMIC, то курсор работает как курсор DYNAMIC (см. далее). Если не указан ни один из параметров FORWARD_ONLY или SCROLL, а также не указано ни одно из ключевых слов STATIC, KEYSET или DYNAMIC, то по умолчанию задается параметр FORWARD_ONLY.

SCROLL означает, что «передвигаться» по курсору можно в любом направлении (в операторе FETCH доступно FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Параметр SCROLL не может указываться вместе с параметром FAST_FORWARD. Курсоры STATIC, KEYSET и DYNAMIC имеют значение по умолчанию SCROLL.

STATIC означает, что курсор необновляемый. Результирующий набор данных такого курсора извлекается из БД и сохраняется в базе для временных объектов tempdb. Изменения таблиц, служащих основой для курсора, после этого отображаться в курсоре не будут.

KEYSET — у данного типа курсора набор значений ключей, идентифицирующих отобранные записи, сохраняется во временной таблице. При движении по курсору значения неключевых атрибутов извлекаются из соответствующих таблиц, поэтому изменения в неключевых столбцах будут видны при работе с курсором. Если попавшая в курсор строка к моменту выборки ее оператором FETCH уже удалена из таблицы, служебная переменная @@ FETCH_STATUS вернет значение -2. Строки, добавленные в таблицы после открытия курсора, в курсоре не видны. Если формирующий курсор запрос задействует хотя бы одну таблицу, не имеющую уникального индекса, курсор типа KEYSET преобразуется в тип STATIC.

DYNAMIC — самый «затратный» по потребляемым ресурсам тип курсора, отображающий все изменения данных, сделанные в строках результирующего набора, включая вновь вставленные строки. Значения данных, порядок, а также членство строк в каждой выборке могут меняться. С динамическими курсорами нельзя использовать FETCH ABSOLUTE.

FAST_FORWARD — самый быстродействующий тип курсора, позволяющий перемещаться от одной строки к другой только «вперед». Это тип курсора, принятый по умолчанию (когда необязательные ключевые слова опущены). Он эквивалентен курсору, объявленному с параметрами FORWARD_ONLY и READ_ONLY.

READ_ONLY — определяет курсор «только для чтения»: изменения в БД через подобный курсор сделать не удастся.

SCROLL_LOCKS означает, что SQL Server блокирует строки по мере их считывания в курсор, что гарантирует возможность их обновления или удаления через курсор данного типа.

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

TYPE_WARNING указывает, что при неявном преобразовании курсора из запрашиваемого типа к другому (например, описанное выше преобразование курсора KEYSET в STATIC при отсутствии уникального индекса в таблице), клиенту будет отправлено предупреждение.

Select_statement — оператор SELECT, формирующий результирующий набор курсора.

Инструкция FOR UPDATE, определяет обновляемые столбцы в курсоре. Если указано OF column_name [,. .. n], то для изменений будут доступны только перечисленные столбцы. Если списка столбцов нет, обновление возможно для всех столбцов, кроме случая объявления курсора с параметром READ_ONLY.

Чтобы открыть и заполнить курсор, используется команда.

OPEN {{[GLOBAL] cursor_name} I @cursor_variable).

При открытии, курсор может указываться по имени (cursor_name) или через переменную типа CURSOR (@cursor_variable). Параметр GLOBAL указывает, что cursor_name — это глобальный курсор.

Для перемещения по набору данных курсора и получения данных в виде значений переменных используется оператор FETCH:

FETCH [[NEXT |PRIOR I FIRST |LAST |.

ABSOLUTE {n | @nvar}| RELATIVE {n |@nvar}].

FROM].

{{[GLOBAL] cursor_name] I @cursor_variable].

[INTO @variable_name [ ,…n]].

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

Важно отметить, что если курсор только что был открыт, первое выполнение FETCH NEXT приводит к переходу на первую запись курсора.

Таблица 10.10

Навигация по набору данных курсора

Глобальная переменная @@FETCH_STATUS позволяет узнать результат последнего выполнения оператора FETCH:

О — действие выполнено успешно;

• -1 — выполнение оператора завершилось неудачно, или строка оказалась вне пределов результирующего набора (курсор закончился);
• -2 — выбираемая строка отсутствует, например если за время работы с курсором «чувствительного к изменениям» типа текущая запись была удалена из БД.

Оператор CLOSE закрывает открытый курсор, освобождая память, использовавшуюся для хранения набора данных. Выборка данных и перемещение по закрытому курсору невозможны — для этого его надо повторно открыть.

CLOSE {{[GLOBAL] cursor_name}|@cursor_variable }.

Оператор DEALLOCATE удаляет связь между курсором и его именем или переменной. Если это последнее имя или переменная, ссылающаяся на курсор, сам курсор удаляется и освобождаются все используемые им ресурсы:

DEALLOCATE {{[GLOBAL] cursor_name] | @cursor_variable) Рассмотрим несложный пример использования курсора. Здесь из таблицы выбираются авторы и названия книг, изданных не ранее 2000 г., после чего данные в цикле выводят операторам SELECT — каждый раз одна запись с собственным заголовком. Дополнительные пояснения даются комментариями в коде:

use MyTest.

go.

/*объявляем переменные*/.

DECLARE @auth varchar (50), @title varchar (50).

/*объявляем курсор, в котором будут авторы и названия книг, изданных не ранее 2000 года*/.

DECLARE cursorl CURSOR FAST_FORWARD.

FOR.

SELECT Author, Title FROM dbo.Bookl.

WHERE [Year] >= 2000.

/*открываем курсор и «пробегаем» его, выводя автора и название отдельным оператором SELECT*/.

OPEN cursorl.

FETCH NEXT FROM cursorl INTO @auth, @title.

WHILE SSFETCH_STATUS = 0.

BEGIN.

SELECT Sauth as [Автор], Stitle as [Название].

FETCH NEXT FROM cursorl INTO @auth, Stitle.

END.

/*закрываем курсор и освобождаем его*/.

CLOSE cursorl.

DEALLOCATE cursorl.

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

use MyTest.

go.

DECLARE Sauth varchar (50), Stitle varchar (50).

/*объявляем переменную типа курсор*/.

DECLARE Scurl CURSOR.

DECLARE cursorl CURSOR FAST_FORWARD.

FOR.

SELECT Author, Title FROM dbo.Bookl.

WHERE [Year] >= 2000.

/*присваиваем переменной типа курсор значение*/.

SET Scurl = cursorl.

OPEN Scurl.

FETCH NEXT FROM Scurl INTO Sauth, Stitle.

WHILE SSFETCH_STATUS = 0.

BEGIN.

SELECT Sauth as [Автор], Stitle as [Название].

FETCH NEXT FROM Scurl INTO Sauth, Stitle.

END.

CLOSE Scurl.

DEALLOCATE Scurl.