Для выполнения изменений с помощью курсора необходимо выполнить команду UPDATE в следующем формате:
UPDATE имя_таблицы SET {имя_столбца={ DEFAULT | NULL | выражение}}[,...n] WHERE CURRENT OF {{[GLOBAL] имя_курсора} |@имя_переменной_курсора}
За одну операцию могут быть изменены несколько столбцов текущей строки курсора, но все они должны принадлежать одной таблице.
Для удаления данных посредством курсора используется команда DELETE в следующем формате:
DELETE имя_таблицы WHERE CURRENT OF {{[GLOBAL] имя_курсора} |@имя_переменной_курсора}
В результате будет удалена строка, установленная текущей в курсоре.
CLOSE {имя_курсора | @имя_переменной_курсора}
После закрытия курсор становится недоступным для пользователей программы. При закрытии снимаются все блокировки, установленные в процессе его работы. Закрытие может применяться только к открытым курсорам. Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор.
Закрытие курсора необязательно освобождает ассоциированную с ним память. В некоторых реализациях нужно явным образом освободить ее с помощью оператора DEALLOCATE. После освобождения курсора освобождается и память, при этом становится возможным повторное использование имени курсора.
Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS
Функция @@FETCH_STATUS возвращает:
0, если выборка завершилась успешно;
-1, если выборка завершилась неудачно вследствие попытки выборки строки, находящейся за пределами курсора;
-2, если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.
DECLARE abc CURSOR SCROLL FORSELECT * FROM Клиент
Пример 13.1. Объявление курсора. (html, txt)
DECLARE @MyCursor CURSORSET @MyCursor=CURSOR LOCAL SCROLL FORSELECT * FROM Клиент
Пример 13.2. Использование переменной для объявления курсора. (html, txt)
DECLARE abc CURSOR GLOBAL SCROLL FORSELECT * FROM КлиентOPEN abc
Пример 13.3. Объявление и открытие курсора. (html, txt)
DECLARE @MyCursor CURSORSET @MyCursor=abc
Пример 13.4. Использование переменной для переприсваивания курсора. (html, txt)
Пример 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_cursorFETCH NEXT FROM klient_cursor INTO @firm, @famWHILE @@FETCH_STATUS=0BEGIN SELECT @message='Клиент '+@fam+ ' Фирма '+ @firm PRINT @message -- переход к следующему клиенту-- FETCH NEXT FROM klient_cursor INTO @firm, @famENDCLOSE klient_cursorDEALLOCATE klient_cursor
Пример 13.5. Курсор для вывода списка фирм и клиентов из Москвы. (html, txt)
Пример 13.6. Разработать курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. В один курсор заносятся все московские клиенты, затем для каждой строки курсора, т.е. для каждого клиента, определяется и распечатывается другой курсор – его покупки. Подсчитывается общая стоимость покупок клиента.
DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INTSET @s=0PRINT ' Список покупок'DECLARE klient_cursor CURSOR LOCAL FOR SELECT КодКлиента, Фирма, Фамилия FROM Клиент WHERE Город='Москва' ORDER BY Фирма, Фамилия OPEN klient_cursorFETCH NEXT FROM klient_cursorINTO @id_kl, @firm, @famWHILE @@FETCH_STATUS=0BEGIN 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, @famENDCLOSE klient_cursorDEALLOCATE klient_cursor
Пример 13.6. Курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. (html, txt)
Пример 13.7. Разработать прокручиваемый курсор для клиентов из Москвы. Если номер телефона начинается на 1, удалить клиента с таким номером и в первой записи курсора заменить первую цифру в номере телефона на 4.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80)PRINT ' Список клиентов'DECLARE klient_cursor CURSOR GLOBAL SCROLLKEYSET FOR SELECT Фирма, Фамилия, Телефон FROM Клиент WHERE Город='Москва' ORDER BY Фирма, ФамилияFOR UPDATEOPEN klient_cursorFETCH NEXT FROM klient_cursor INTO @firm, @fam, @telWHILE @@FETCH_STATUS=0BEGIN 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, @telEND FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- в первой записи заменить первую цифру в-- номере телефона на 4 UPDATE Клиент SET Телефон=’4’ + RIGHT(@tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursorSELECT @message='Клиент '+@fam+' Фирма '+ @firm ' Телефон '+ @tel PRINT @messageCLOSE klient_cursorDEALLOCATE klient_cursor
Пример 13.7. Прокручиваемый курсор для клиентов из Москвы. (html, txt)
Пример 13.8. Использование курсора как выходного параметра процедуры. Процедура возвращает набор данных – список товаров.
CREATE PROC my_proc@cur CURSOR VARYING OUTPUTASSET @cur=CURSOR FORWARD_ONLY STATIC FORSELECT Название FROM ТоварOPEN @cur
Пример 13.8. Использование курсора как выходного параметра процедуры. (html, txt)
Вызов процедуры и вывод на печать данных из выходного курсора осуществляется следующим образом:
DECLARE @my_cur CURSORDECLARE @n VARCHAR(20)EXEC my_proc @cur=@my_cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @nWHILE (@@FETCH_STATUS=0)BEGIN FETCH NEXT FROM @my_cur INTO @n SELECT @nENDCLOSE @my_curDEALLOCATE @my_cur
