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

Первое, что выполняется при работе с курсорами - это их объявление. Объявление курсора выполняется с помощью команды declare cursor.

SQL Server поддерживает два формата этой команды. Первый из них соответ­ствует требованиям стандарта ANSI SQL-92 и предоставляет базовый набор опций. Второй же формат является собственной разработкой Microsoft и предоставляет пользователям более широкий набор возможностей.

Рассмотрим оба варианта команды чуть позже, а сейчас поговорим о стан­дарте ANSI SQL-92:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR select_statement

[FOR {READ ONLY I UPDATE [OF column_name [,...n]]]

Рассмотрим назначение параметров команды:

 

cursor_name — имя курсора, которое в дальнейшем будет использоваться для обращения к нему. Имя курсора должно удовлетворять общим требо­ваниям именования объектов. В отличие от имени переменной, имя кур­сора не должно начинаться символом @.

 

insensitive — это ключевое слово предписывает серверу создать статиче­ский курсор. Результирующий набор курсора будет размещаться в базе данных tempdb. Работа с таким курсором будет разрешена лишь в режиме "только для чтения". Если ключевое слово insensitive не указывается, то создается динамический курсор.

 

scroll — в создаваемом курсоре можно выполнять выборку данных в произвольном порядке. Если же опция scroll не указывается, то курсор можно будет просматривать только последовательно, в направлении от первой строки к последней. Однако не нужно путать курсор, не обес­печивающий выборку в произвольном порядке, с последовательным кур­сором.

 

select_statement — указывает код запроса select, применяемого для формирования результирующего набора курсора. При написании кода запроса следует учитывать, что в запросе не допускается наличие разде­лов INTO, GROUP BY И COMPUTE.

 

read only — работа с курсором будет возможна в режиме "только для чтения". Указывать этот параметр не обязательно при создании статиче­ского курсора, который по своей природе не допускает изменений. Од­нако не нужно путать курсор "только для чтения" со статическим курсо­ром. Применение параметра read only для динамических курсоров по­зволяет повысить скорость операций работы с курсором.

 

update [of column__name [,...n]] — разрешает выполнять с помощью курсора изменение исходных данных. Не разрешается применение этого параметра совместно с параметром insensitive. Аргумент column_name [,...n] определяет список столбцов, для которых будет поддерживать возможность изменения данных. Если имена столбцов не указаны, то по умолчанию разрешается обновлять все столбцы курсора.

 

Как видим, стандарт ANSI SQL-92 предоставляет пользователям весьма скудные возможности работы с курсорами. В частности, пользователи не могут работать с ключевыми и последовательными курсорами.

 

Более широкие возможности по созданию курсоров предоставляет команда declare cursor формата Transact-SQL:

 

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY I SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR select_statement

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

 

Рассмотрим назначение параметров этого формата команды, однако опус­тим параметры, уже описанные ранее:

local — создаваемый курсор будет локальным, то есть будет существовать только во время выполнения транзакции, хранимой процедуры или паке­та команд. По завершении работы создавшей курсор транзакции, про­цедуры или пакета происходит неявное удаление курсора, причем даже в том случае, если курсор не был закрыт. Данные локального курсора могут быть переданы из хранимой процедуры при указании параметра output. Курсор доступен только в пределах создавшей его структуры.

 

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

 

forward_only — предписывает серверу создать курсор, из которого воз­можна только последовательная выборка в направлении от первой строки к последней.

 

scroll — в отличие от предыдущего параметра, позволяет создавать кур­сор, допускающий выборку данных в произвольном порядке.

 

static — будет создан статический курсор.

 

keyset — предписывает создание ключевого курсора.

 

dynamic — предписывает создание динамического курсора.

 

fast_forward — совместно с параметром read_only позволяет создавать курсор, оптимизированный для последовательного просмотра данных, то есть когда строки выбираются друг за другом в направлении от первой к последней. Применение fast_forward не допускается совместно с па­раметрами SCROLL ИЛИ FOR_UPDATE. Кроме того, параметры FORWARD_ONLY И FAST_FORWARD являются взаимоисключающими.

 

read_only — позволяет запретить применение курсора для выполнения любых изменений исходных данных (изменение или удаление). Параметр read_only не является обязательным для статического курсора. Использо­вание же этого параметра с курсорами других типов позволяет оптимизи­ровать операции работы с курсорами. Рекомендуется всегда указывать этот параметр, если не предполагается выполнять изменения с помощью курсора.

 

scroll_locks — сервер блокирует все строки, входящие в результирую­щий набор. Это повышает скорость операций чтения и модификации Данных с помощью курсора, т. к. не надо ожидать каждый раз разблоки­рования нужных ресурсов. Одновременное использование параметров fast_forward и scroll_locks не допускается.

 

optimistic — позволяет контролировать попытки изменения через курсор данных, которые уже были изменены пользователями. При этом не при­меняется полное блокирование данных результирующего набора, как это происходит при указании параметра scroll_locks. Если в исходной таб­лице есть столбец timestamp, то он автоматически используется для от­слеживания изменений. Если же такого столбца нет, то выполняется подсчет контрольной суммы каждой строки результирующего набора курсора. Когда пользователь пытается выполнить изменение или удале­ние данных, происходит сравнение значений, хранимых во внутренних структурах курсора, со значениями строки. Если они совпадают, то изме­нения выполняются. В противном случае генерируется сообщение о том, что исходные данные были изменены, и операция откатывается. Не до­пускается одновременное применение опций fast_forward и optimistic.

 

type_warning — если пользователь неверно указал тип создаваемого кур­сора, то сервер выполняет неявное автоматическое преобразование типа курсора. При этом по умолчанию пользователь не получает никакого со­общения о выполняемых изменениях. Параметр type_warning предписы­вает серверу в обязательном порядке информировать пользователя о вы­полняемом преобразовании типа курсора.

Приведем пример создания курсора с применением переменной:

 

DECLARE @state char(2)

SET @state = 'CA'

DECLARE [auth state] CURSOR SCROLL KEYSET LOCAL FOR

SELECT au__id, au_lname, au__fname, state

FROM authors

WHERE state = @state

 

Порядок расположения строк в курсоре соответствует их порядку в результате выборки, возвращаемой после выполнения ассоциированного с курсором за­проса select. С помощью раздела order by пользователи могут управлять порядком расположения строк в курсоре.