Курсор можно рассматривать как механизм, предоставляющий пользователю доступ к любой строке набора данных, связанного с курсором и
формируемого с помощью команды выборки данных SELECT. При этом в каждый момент времени можно работать только с одной строкой набора данных. Далее можно перейти к другой строке и т.д.
Существуют статические и динамические курсоры. Статический курсор представляет собой моментальный снимок выбираемых строк на момент его открытия. В дальнейшем содержимое такого курсора не меняется и хранится в системной базе данных tempdb.
Динамические курсоры каждый раз обновляются при обращении к его строкам (т.е. обращение к другой строке формирует набор данных заново путем выполнения команды SELECT). При работе с динамическими курсорами пользователи могут вносить изменения в строки набора данных с помощью команд UPDATE, INSERT, DELETE. Однако каждая такая команда за один раз может работать только с одной строкой.
Кроме того, курсоры бывают последовательные (Forward-only) и прокручиваемые (Scrollable). Последовательные курсоры обеспечивают только последовательное считывание строк в прямом направлении, т.е. начиная с первой строки и заканчивая последней.
Прокручиваемые курсоры обеспечивают как последовательное считывание строк в обоих направлениях (прямом и обратном), так и обращение к произвольной строке набора данных. Однако они работают медленнее, чем последовательные курсоры.
Имеются еще так называемые ключевые курсоры, которые представляют собой набор ссылок на строки набора данных, формируемого командой SELECT. Таким образом, ключевые курсоры выбирают не всю строку, а лишь ключевые поля, позволяющие, в свою очередь, однозначно идентифицировать каждую строку набора данных, связанного с курсором. Такой подход позволяет отображать в курсоре любые изменения, возникающие в исходном наборе данных за счет действий других пользователей. Однако набор ключей формируется только в момент открытия курсора, помещается в системную базу данных tempdb и впоследствии не изменяется.
Весь процесс использования курсора включает в себя пять этапов.
1. Объявление курсора. Подразумевает указание его имени и запроса SELECT, который будет использоваться для формирования набора данных, связанного с курсором. Также указывается тип курсора. Команда имеет две разновидности, различающиеся синтаксисом (см. [1], стр. 1224).
Синтаксис стандарта SQL-92:
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | 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 ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
2. Открытие курсора. В процессе открытия курсора выполняется ассоциированный с курсором запрос SELECT, в результате чего создается связанный с курсором набор данных. Команда имеет следующий синтаксис (см. [1], стр. 1228):
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
3. Манипуляции со строками курсора. После того, как курсор открыт, можно приступать к выборке данных из этого курсора, а если тип курсор позволяет, то и к операциям обновления и удаления данных.
Команда считывания строки данных из курсора имеет следующий синтаксис (см. [1], стр. 1229):
FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
Примечание. Непосредственно после слова FETCH опцией указывается строка, которую нужно выбирать, поэтому сначала эта строка становится текущей, и только после этого производится выборка данных. При этом возможен выход за пределы диапазона строк набора данных. Эту ситуацию можно контролировать с помощью встроенной функции @@FETCH_STATUS, которая возвращает значение 0, если последняя операция выборки данных из курсора была выполнена успешно. Если возвращается отрицательное значение, то это означает, что была предпринята попытка выборки строки, находящейся за пределами набора данных.
Можно также использовать функцию @@CURSOR_ROWS. Она возвращает количество строк набора данных, но только для статического курсора. Для динамического курсора всегда возвращается -1.
Обновление данных посредством курсора выполняется с помощью команды UPDATE, имеющей следующий синтаксис (см.[1], стр. 1232):
UPDATE table_name
SET { column_name = { expression | DEFAULT | NULL }} [ ,...n ] WHERE CURRENT OF cursor_name
Изменения, внесенные этой командой, будут касаться только текущей строки курсора. За одну операцию обновления данных допускается изменение значений полей, относящихся к одной таблице. Если курсор строится на основе нескольких таблиц и необходимо изменить все значения строки курсора, то для этого придется выполнить несколько команд UPDATE.
Кроме того, допускается изменение любых столбцов таблицы, в том числе и не входящих в набор данных курсора.
Удаление данных посредством курсора выполняется с помощью команды DELETE, имеющей следующий синтаксис (см.[1], стр. 1234):
DELETE [FROM] table_name
WHERE CURRENT OF cursor_name
При выполнении этой команды происходит удаление строки указанной таблицы, связанной с текущей строкой курсора. Если курсор строится на основе нескольких таблиц, то для удаления данных из этих таблиц необходимо выполнить команду DELETE отдельно для каждой таблицы.
4. Закрытие курсора. После того, как были выполнены все необходимые манипуляции со строками курсора, его можно закрыть. Это приводит к высвобождению выделенных для него ресурсов (например, пространства в системной базе данных tempdb) и снятию блокировок, если они были установлены в процессе работы курсора. Команда имеет следующий синтаксис (см.[1], стр. 1228):
CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }
5. Освобождение курсора. Закрытый курсор может быть удален, что подразумевает удаление из оперативной памяти описания курсора как объекта. Если курсор закрыт, но не удален, он может быть повторно открыт для использования. При этом в него будет помещен новый набор данных.
Команда освобождения курсора имеет следующий синтаксис (см. [1], стр. 1237):
DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
ТРИГГЕРы
Триггеры представляют собой хранящиеся в базах данных подпрограммы на языке SQL, выполняющиеся автоматически при операциях вставки, обновления и удаления данных в таблицах базы данных. Каждый триггер связан только с одной из таблиц базы данных. Автоматическое срабатывание триггеров в ответ на изменения табличных данных позволяет использовать их, например, для реализации сложных алгоритмов проверки данных, для гарантии их правильности и достоверности, для создания сложного значения по умолчанию, вычисляя его с помощью других столбцов и функций Transact-SQL, для обеспечения нестандартной ссылочной целостности, поддержание которой обычными средствами SQL Server невозможно и т.д. Использование триггеров превращает сервер из пассивного наблюдателя за происходящими изменениями данных, в систему, оперативно реагирующую на такие изменения. Таким образом, правила, в соответствие с которыми осуществляются активные действия сервера, определяются триггерами (эти правила называют также бизнес-правилами).
В SQL Server 2000/2005 существует два вида триггеров:
· AFTER-триггеры, которые запускаются после успешного выполнения команд, связанных с изменением табличных данных. Как команда, так и триггер реализуются в рамках одной и той же транзакции. Поэтому откат при выполнении триггера приведет и к откату команды, вызвавшей его запуск. AFTER-триггеры широко используются и полезны, например, в тех случаях, когда при модификации строк необходимо сравнивать исходные значения
полей с их новыми значениями. С каждой таблицей может быть связано несколько AFTER-триггеров;
· INSTEAD OF-триггеры, тело которых выполняется вместо операций вставки, обновления и удаления строк, вызвавших запуск триггера этого вида. С каждой таблицей может быть связано не более трех AFTER-триггеров (по одному для каждой из команд INSERT, UPDATE, DELETE). Триггеры этого вида могут создаваться не только для таблиц, но и для представлений.
Синтаксис команды создания триггера (см. [1], стр. 1242):
CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { FOR [ { AFTER | INSTEAD OF }]
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } }
В этой команде, в частности, присутствуют функции UPDATE(column) и COLUMNS_UPDATED( ), используемые для определения того, какой столбец или группу столбцов пользователь пытается изменить (см. [1], стр. 1244-1246). Кроме того, всегда можно получить полную информацию об изменениях, которые пытается произвести пользователь. Эту информацию дают таблицы inserted и deleted, которые автоматически создаются сервером при запуске триггера. Содержимое этих таблиц зависит от команды, вызвавшей запуск триггера:
· Команда INSERT. В таблице inserted будут содержаться все строки, которые пользователь пытается вставить в таблицу. Таблица deleted будет пуста.
· Команда DELETE. В таблице deleted будут содержаться все строки, которые пользователь пытается удалить. Таблица inserted будет пуста.
· Команда UPDATE. В таблице deleted будут содержаться все строки, которые пользователь пытается изменить. В таблице inserted указываются строки, которые будут внесены в таблицу вместо соответствующих строк таблицы deleted.
Для внесения изменений в текст существующего триггера используется та же команда, что и для его создания, с тем лишь отличием, что вместо зарезервированного слова CREATE используется слово ALTER (см. [1], стр. 1246).
Для удаления триггера используется команда, имеющая следующий синтаксис (см. [1], стр. 1247):
DROP TRIGGER { trigger } [ ,...n ]
