Управление курсором в среде MS SQL Server

Управление курсором реализуется путем выполнения следующих команд:

· DECLARE – создание или объявление курсора ;

· OPEN – открытие курсора, т.е. наполнение его данными;

· FETCH – выборка из курсора и изменение строк данных с помощью курсора;

· CLOSE – закрытие курсора ;

· DEALLOCATE – освобождение курсора, т.е. удаление курсора как объекта.

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

В стандарте SQL для создания курсора предусмотрена следующая команда:

При использовании ключевого слова INSENSITIVE будет создан статический курсор. Изменения данных не разрешаются, кроме того, не отображаются изменения, сделанные другими пользователями. Если ключевое слово INSENSITIVE отсутствует, создается динамический курсор.

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

SELECT-оператор задает тело запроса SELECT, с помощью которого определяется результирующий набор строк курсора.

При указании аргумента FOR READ_ONLY создается курсор "только для чтения", и никакие модификации данных не разрешаются. Он отличается от статического, хотя последний также не позволяет менять данные. В качестве курсора "только для чтения" может быть объявлен динамический курсор, что позволит отображать изменения, сделанные другим пользователем.

Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца, во всех столбцах.

В среде MS SQL Server принят следующий синтаксис команды создания курсора:

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

Если указано ключевое слово GLOBAL, создается глобальный курсор ; он существует до закрытия текущего соединения.

При указании FORWARD_ONLY создается последовательный курсор ; выборку данных можно осуществлять только в направлении от первой строки к последней.

При указании SCROLL создается прокручиваемый курсор ; обращаться к данным можно в любом порядке и в любом направлении.

При указании STATIC создается статический курсор.

При указании KEYSET создается ключевой курсор.

При указании DYNAMIC создается динамический курсор.

Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC.

В курсоре, созданном с указанием аргумента OPTIMISTIC, запрещается изменение и удаление строк, которые были изменены после открытия курсора.

При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT.

Открытие курсора

Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:

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

Выборка данных из курсора

Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:

При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора, которая становится текущей строкой.

При указании LAST возвращается самая последняя строка курсора. Она же становится текущей строкой.

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

Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.

Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора. Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.

Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.

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

В конструкции INTO @имя_переменной [,...n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре, а тип данных переменной – типу данных в столбце курсора. Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды SELECT – данные выводятся на экран.

Изменение и удаление данных

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

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

Для удаления данных посредством курсора используется команда DELETE в следующем формате:

В результате будет удалена строка, установленная текущей в курсоре.

Закрытие курсора

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

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

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

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

@имя_переменной_курсора }

Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS

Функция @@FETCH_STATUS возвращает:

0, если выборка завершилась успешно;

-1, если выборка завершилась неудачно вследствие попытки выборки строки, находящейся за пределами курсора ;

-2, если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.

DECLARE abc CURSOR SCROLL FOR

SELECT * FROM Клиент

Пример 13.1. Объявление курсора.

DECLARE @MyCursor CURSOR

SET @MyCursor=CURSOR LOCAL SCROLL FOR

SELECT * FROM Клиент

Пример 13.2. Использование переменной для объявления курсора.

DECLARE abc CURSOR GLOBAL SCROLL FOR

SELECT * FROM Клиент

OPEN abc

Пример 13.3. Объявление и открытие курсора.

DECLARE @MyCursor CURSOR

SET @MyCursor=abc

Пример 13.4. Использование переменной для переприсваивания курсора.

Пример 13.5. Разработать курсор для вывода списка фирм и клиентов из Москвы.

DECLARE @firm VARCHAR(50),

@fam VARCHAR(50),

@message VARCHAR(80)

PRINT ' Список клиентов'

DECLARE klient_cursor CURSOR LOCAL FOR

SELECT Фирма, Фамилия

FROM Клиент

WHERE Город='Москва'

ORDER BY Фирма, Фамилия

OPEN klient_cursor

FETCH NEXT FROM klient_cursor INTO @firm, @fam

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @message='Клиент '+@fam+

' Фирма '+ @firm

PRINT @message

-- переход к следующему клиенту--

FETCH NEXT FROM klient_cursor

INTO @firm, @fam

END

CLOSE klient_cursor

DEALLOCATE klient_cursor

Пример 13.5. Курсор для вывода списка фирм и клиентов из Москвы.

Пример 13.6. Разработать курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. В одинкурсор заносятся все московские клиенты, затем для каждой строки курсора, т.е. для каждого клиента, определяется и распечатывается другой курсор – его покупки. Подсчитывается общая стоимость покупок клиента.

DECLARE @id_kl INT,

@firm VARCHAR(50),

@fam VARCHAR(50),

@message VARCHAR(80),

@nam VARCHAR(50),

@d DATETIME,

@p INT,

@s INT

SET @s=0

PRINT ' Список покупок'

DECLARE klient_cursor CURSOR LOCAL FOR

SELECT КодКлиента, Фирма, Фамилия

FROM Клиент

WHERE Город='Москва'

ORDER BY Фирма, Фамилия

OPEN klient_cursor

FETCH NEXT FROM klient_cursor

INTO @id_kl, @firm, @fam

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @message='Клиент '+@fam+

' Фирма '+ @firm

PRINT @message

SELECT @message='Наименование товара Дата

покупки Стоимость'

PRINT @message

DECLARE tovar_cursor CURSOR FOR

SELECT Товар.Название, Сделка.Дата,

Товар.Цена*Сделка.Количество AS

Стоимость

FROM Товар INNER JOIN Сделка ON Товар.

КодТовара=Сделка.КодТовара

WHERE Сделка.КодКлиента=@id_kl

OPEN tovar_cursor

FETCH NEXT FROM tovar_cursor

INTO @nam, @d, @p

IF @@FETCH_STATUS<>0

PRINT ' Нет покупок'

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @message=' '+@nam+' '+

CAST(@d AS CHAR(12))+' '+

CAST(@p AS CHAR(6))

PRINT @message

SET @s=@s+@p

FETCH NEXT FROM tovar_cursor

INTO @nam, @d, @p

END

CLOSE tovar_cursor

DEALLOCATE tovar_cursor

SELECT @message='Общая стоимость '+

CAST(@s AS CHAR(6))

PRINT @message

-- переход к следующему клиенту--

FETCH NEXT FROM klient_cursor

INTO @id_kl, @firm, @fam

END

CLOSE klient_cursor

DEALLOCATE klient_cursor

Пример 13.6. Курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости.

Пример 13.7. Разработать прокручиваемый курсор для клиентов из Москвы. Если номер телефона начинается на 1, удалить клиента с таким номером и в первой записи курсора заменить первую цифру в номере телефона на 4.

DECLARE @firm VARCHAR(50),

@fam VARCHAR(50),

@tel VARCHAR(8),

@message VARCHAR(80)

PRINT ' Список клиентов'

DECLARE klient_cursor CURSOR GLOBAL SCROLL

KEYSET FOR

SELECT Фирма, Фамилия, Телефон

FROM Клиент

WHERE Город='Москва'

ORDER BY Фирма, Фамилия

FOR UPDATE

OPEN klient_cursor

FETCH NEXT FROM klient_cursor

INTO @firm, @fam, @tel

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @message='Клиент '+@fam+

' Фирма '+@firm ' Телефон '+ @tel

PRINT @message

-- если номер телефона начинается на 1,

-- удалить клиента с таким номером

IF @tel LIKE ‘1%’

DELETE Клиент

WHERE CURRENT OF klient_cursor

ELSE

-- переход к следующему клиенту

FETCH NEXT FROM klient_cursor

INTO @firm, @fam, @tel

END

FETCH ABSOLUTE 1 FROM klient_cursor

INTO @firm, @fam, @tel

-- в первой записи заменить первую цифру в

-- номере телефона на 4

UPDATE Клиент SET Телефон=’4’ +

RIGHT(@tel,LEN(@tel)-1))

WHERE CURRENT OF klient_cursor

SELECT @message='Клиент '+@fam+' Фирма '+

@firm ' Телефон '+ @tel

PRINT @message

CLOSE klient_cursor

DEALLOCATE klient_cursor

Пример 13.7. Прокручиваемый курсор для клиентов из Москвы.

Пример 13.8. Использование курсора как выходного параметра процедуры. Процедура возвращает набор данных – списоктоваров.

CREATE PROC my_proc

@cur CURSOR VARYING OUTPUT

AS

SET @cur=CURSOR FORWARD_ONLY STATIC FOR

SELECT Название FROM Товар

OPEN @cur

Пример 13.8. Использование курсора как выходного параметра процедуры.

Вызов процедуры и вывод на печать данных из выходного курсора осуществляется следующим образом:

DECLARE @my_cur CURSOR

DECLARE @n VARCHAR(20)

EXEC my_proc @cur=@my_cur OUTPUT

FETCH NEXT FROM @my_cur INTO @n

SELECT @n

WHILE (@@FETCH_STATUS=0)

BEGIN

FETCH NEXT FROM @my_cur INTO @n

SELECT @n

END

CLOSE @my_cur

DEALLOCATE @my_cur