Главная » Microsoft SQL Server, Базы данных » Все о курсорах

0

По своей сути курсор является указателем на одну строку данных. Для прохождения по набору данных до его конца обычно используют цикл while. SQL Server поддерживает стандартный синтаксис ANSI SQL-92 и расширенный синтаксис Т-SQL, предлагающий дополнительные возможности.

Пять этапов жизни курсора

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

1.              Объявление курсора определяет тип и режим его работы, а также описывает инструкцию SELECT, поставляющую ему данные. При объявлении курсора никакие данные не извлекаются. Это единственный случай, когда инструкция declare не требует амперсанда. Курсор SQL-92 объявляется с помощью инструкции cursor for:

DECLARE имя_курсора CURSOR FOR инструкция_БЕЬЕСТ FOR параметры_курсора

Расширенный курсор T-SQL объявляется аналогичным образом:

DECLARE имя_курсopa CURSOR параметры_курсора FOR инструкция_5ЕЬЕСТ

2.              При открытии курсора извлекаются данные, которыми он и заполняется:

OPEN имя_курсора

3.              Курсор перемещается к следующей строке и заполняет значениями ее столбцов локальные переменные (эти переменные должны быть предварительно объявлены): FETCH [направление] имя_курсора INTO @переменная1, @переменная2

По умолчанию команда FETCH перемещает курсор к следующей строке (направление NEXT), однако при желании можно переместить курсор к предыдущей (PRIOR), первой (FIRST) и последней (LAST) строке. Также с помощью этой команды можно переместить курсор к строке с некоторым абсолютным номером (ABSOLUTE п) или сместить относительно текущей позиции на определенное расстояние (RELATIVE п). Проблема последнего подхода состоит в том, что в реляционной базе данных номер строки не имеет определенного смысла. Если в коде требуется перейти к конкретной строке, чтобы получить логический результат, то это должно быть заранее предусмотрено в модели базы данных.

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

CLOSE имя_курсора

5.              Демонтаж курсора высвобождает отведенную под него память и аннулирует его определение:

DEALLOCATE имя_курсора

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

Управление курсором

Так как курсор проходит по набору данных построчно, код Т-SQL требует команд постоянного смещения курсора. Для управления этим циклическим процессом язык Т-SQL предлагает две глобальные переменные, содержащие информацию о состоянии курсора.

Переменная @@cursor_rows возвращает общее количество строк в курсоре. Если курсор заполняется асинхронно, то в этой переменной будет содержаться отрицательное значение.

Существенной для управления курсором является глобальная переменная @@fetch_ status. Она отчитывается о состоянии курсора после последней команды FETCH. Это состояние важно для управления перемещением курсора и оценки, достиг ли он одного из концов набора данных. Возможные значения переменной @@f etch_status свидетельствуют о следующем.

?               0 — последняя операция FETCH успешно извлекла строку.

?               1 — последняя операция FETCH достигла конца набора данных.

?               2 — строка, к которой переместился курсор, оказалась недоступной; она была удалена.

Комбинирование переменной @@f etch_status с оператором while позволяет создать циклы, позволяющие успешно перемещаться по строкам набора данных.

Обычно в пакетах создается курсор, после чего выполняется первая команда FETCH и начинается цикл while, который продолжается до тех пор, пока не будет достигнут конец набора данных. В верхней части цикла проверяется значение переменной @@fetch_status для определения, создан ли курсор. В следующем примере продемонстрированы все пять этапов жизни курсора и управление итеративным процессом его перемещения с помощью цикла while и переменной @@f etch_status:

– Этап 1

DECLARE cDetail CURSOR FAST_FORWARD

FOR SELECT DetaillD

FROM Detail

WHERE AdjAmount IS NULL — Этап 2 OPEN cDetail — Этап 3

FETCH cDetail INTO @cDetailID

EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT

UPDATE Detail

SET AdjAmount = @SprocResult WHERE DetaillD = @cDetailID

WHILE @@Fetch_Status = <>1 BEGIN BEGIN

EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT UPDATE Detail

SET AdjAmount = @SprocResult WHERE DetaillD = @cDetailID

END

– Итеративный проход по курсору

FETCH cDetail INTO @cDetailID — fetch next

END

– Этап 4 CLOSE cDetail — Этап 5

DEALLOCATE cDetail

Обновление курсора

Поскольку курсор последовательно проходит по набору данных, SQL Server знает, какая строка является текущей. Указатель курсора может использоваться в предложениях WHERE инструкций SQL DML (SELECT, INSERT, UPDATE и DELETE), чтобы манипулировать корректными данными.

Параметр FOR UPDATE инструкции DECLARE позволяет выполнять обновления с помощью курсора. При этом если явно указаны какие-либо столбцы, именно они будут обновлены; если никаких столбцов не указано, будут обновлены все.

DECLARE cDetail CURSOR FOR SELECT DetaillD FROM Detail WHERE AdjAmount IS NULL FOR UPDATE OF AdjAmount

В теле цикла, после того, как было выполнено перемещение к нужной строке, инструкция DML может включить курсор в предложение WHERE, используя синтаксис CURRENT OF. В следующем примере, взятом из сценария KilltheCursor. sql, выполняется ссылка на курсор cDetail:

UPDATE Detail

SET AdjAmount = @SprocResult WHERE CURRENT OF cDetail

Область определения курсора

Так как курсоры имеют тенденцию использоваться в самых запутанных ситуациях, понимание области их определения исключительно важно. Эта область определяет, виден ли курсор только в пакете, в котором создан или может использоваться во всех вызываемых процедурах. Область определения курсора задается при его объявлении:

DECLARE имя_курсора CURSOR Local или Global FOR инструкция     SELECT

По умолчанию областью определения курсора является вся база данных. Это указывается в параметре CURSOR_DEFAULT:

ALTER DATABASE Family SET CURSOR_DEFAULT LOCAL

Для успешного выполнения процедур важно знать текущую область определения курсора. Это выполняется с помощью функции DATABASEPROPERTYEX () :

SELECT DATABASEPROPERTYEX(‘Family’, 1IsLocalCursorsDefault’)

Результат выполнения функции следующий:

1

Кроме параметров global и for update курсоры имеют еще несколько, управляющих возможностью перемещения и обновления данных, — static, keyset, dynamic и optimistic. Мне не хочется тратить место в книге на описание этих параметров, так как лучшей стратегией все-таки остается максимальное ограничение сферы действия курсоров и переход на пакетные решения.

Курсоры и транзакции

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

Компромисс заключается в использовании функции RowNumber () с пакетным решением для обновления больших групп строк.

Одной из методик, которая часто используется для повышения производительности курсоров, является объединение всего курсора в одну логическую транзакцию. В этом решении есть свои за и против. Несмотря на то что производительность повышается примерно на 50%, за это приходится расплачиваться блокировкой, устанавливаемой транзакцией.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии