Раздел I. Использование курсоров в языке Transact-SQL

Курсор можно рассматривать как механизм, предоставляющий пользователю доступ к любой строке набора данных, связанного с курсором и формируемого с помощью команды выборки данных 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 }

 

Пример. Пусть необходимо подсчитать суммарную стоимость в национальной валюте всех товаров, заказанных в течение указанного интервала времени.

Одним из вариантов решения этой задачи будет создание хранимой процедуры pr_СтоимостьНВ_Интервал с двумя входными параметрами (@НачалоИнтервала, @КонецИнтервала) и одним выходным параметром (@СтоимостьНВ).

É

/* Подсчет стоимости товаров в национальной валюте */

CREATE PROCEDURE pr_СтоимостьНВ_Интервал

@НачалоИнтервала DATETIME,

@КонецИнтервала DATETIME,

@СтоимостьНВ MONEY OUTPUT

AS

IF @НачалоИнтервала IS NULL

SET @НачалоИнтервала = getdate() - 60

IF @КонецИнтервала IS NULL

SET @КонецИнтервала = getdate()

-- выполняем начальную установку

SET @СтоимостьНВ = 0

-- объявляем локальную переменную @СтоимостьЗаказа

DECLARE @СтоимостьЗаказа MONEY

-- объявляем курсор myCursor. При этом набор данных, связанный

-- с курсором, будет содержать всего один столбец