Полякова Л.П. Основы SQL. Курс лекций
Подождите немного. Документ загружается.
Освобождение курсора
Закрытие курсора необязательно освобождает ассоциированную с
ним память. В некоторых реализациях нужно явным образом освободить
ее с помощью оператора DEALLOCATE. После освобождения курсора ос-
вобождается и память, при этом становится возможным повторное ис-
пользование имени курсора.
DEALLOCATE { имя_курсора | @имя_переменной_курсора }
Для контроля достижения конца курсора рекомендуется применять
функцию: @@FETCH_STATUS
Функция @@FETCH_STATUS возвращает:
0, если выборка завершилась успешно;
-1, если выборка завершилась неудачно вследствие попытки выборки
строки, находящейся за пределами курсора;
-2, если выборка завершилась неудачно вследствие попытки обраще-
ния к удаленной или измененной строке.
Пример 13.1. Объявление курсора.
DECLARE abc CURSOR SCROLL FOR
SELECT * FROM Клиент
Пример 13.2. Использование переменной для объявления курсора.
DECLARE @MyCursor CURSOR
SET @MyCursor=CURSOR LOCAL SCROLL FOR
SELECT * FROM Клиент
Пример 13.3. Объявление и открытие курсора:
DECLARE abc CURSOR GLOBAL SCROLL FOR
SELECT * FROM Клиент
OPEN abc
Пример 13.4. Использование переменной для переприсваивания
курсора.
DECLARE @MyCursor CURSOR
SET @MyCursor=abc
291
Лекция 13 Курсоры: принципы работы
Пример 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.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
Курс
292
Основы SQL
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
NTO @nam, @d, @p
END
CLOSE tovar_cursor
DEALLOCATE tovar_cursor
293
Лекция 13 Курсоры: принципы работы
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.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
-- переход к следующему клиенту
Курс
294
Основы SQL
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.8. Использование курсора как выходного параметра про-
цедуры. Процедура возвращает набор данных – список товаров.
CREATE PROC my_proc
@cur CURSOR VARYING OUTPUT
AS
SET @cur=CURSOR FORWARD_ONLY STATIC FOR
SELECT Название FROM Товар
OPEN @cur
Вызов процедуры и вывод на печать данных из выходного курсора
осуществляется следующим образом:
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
295
Лекция 13 Курсоры: принципы работы
Вариант 1
1. Разработан курсор для создания списка книг, вышедших в
издательстве «Мир».
DECLARE @n VARCHAR(50),
@iz VARCHAR(50),
@p MONEY,
@message VARCHAR(80)
DECLARE @cur1 CURSOR
PRINT ' Список книг'
DECLARE cursor1 CURSOR FOR
SELECT Название, Издательство, Цена
FROM Книга
WHERE Издательство=”Мир”
OPEN cursor1
SET @cur1=cursor1
FETCH NEXT FROM cursor1 INTO @n, @iz, @p
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @message='Книга '+@n+' Цена '+
CAST(@p AS VARCHAR(4))
PRINT @message
-- переход к следующему клиенту--
FETCH NEXT FROM cursor1 INTO @n, @iz,@p
END
CLOSE cursor1
DEALLOCATE cursor1
Укажите правильные ответы.
Курсор написан без ошибок
В операторе FETCH порядок следования переменных не
соответствует полям оператора
SELECT в объявлении курсора
Освобождение ресурсов производится до закрытия курсора, что
недопустимо
Переприсваивание открытого курсора (оператор SET)
недопустимо
Курс
296
Основы SQL
2. Разработана процедура, возвращающая список книг
издательства «Мир».
CREATE PROC my_proc
@cur CURSOR VARYING OUTPUT
AS
SET @cur=CURSOR FORWARD_ONLY STATIC FOR
SELECT Название, Издательство, Цена
FROM Книга
WHERE Издательство=”Мир”
OPEN @cur
Вызов процедуры и вывод на печать названий книг из
выходного курсора осуществляется следующим образом:
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
DEALLOCA
TE @my_cur
Укажите правильные ответы.
Ошибок ни в процедуре, ни в ее вызове нет
Процедура создана правильно. При вызове в обработке курсора
дважды происходит обращение к первой записи
В процедуре не следует открывать курсор. Он должен быть
открыт при вызове процедуры
Процедура создана правильно. При вызове процедуры для
обработки курсора в операторе
INTO список переменных не
соответствует полям оператора
SELECT в создании курсора в
процедуре
297
Лекция 13 Курсоры: принципы работы
3. Разработан курсор для печати списка книг.
DECLARE @n VARCHAR(50),
@iz VARCHAR(50),
@p MONEY,
@message VARCHAR(80)
DECLARE @cur1 CURSOR
PRINT ' Список клиентов'
DECLARE cursor1 CURSOR GLOBAL SCROLL KEYSET FOR
SELECT Название, Издательство, Цена
FROM Книга
WHERE Издательство=”Мир”
OPEN cursor1
SET @cur1=cursor1
FETCH LAST FROM cursor1 INTO @n, @iz, @p
FETCH ABSOLUTE 2 FROM cursor1 INTO @n, @iz, @p
FETCH RELATIVE -2 FROM cursor1 INTO @n, @iz, @p
SELECT @message='Книга '+@n+' Цена '+
CAST(@p AS VARCHAR(4))
PRINT @message
CLOSE cursor1
DEALLOCATE cursor1
Какая из имеющихся в курсоре записей будет распечатана?
Первая запись
Произошло обращение к строке за пределами курсора
Объявление курсора запрещает произвольный переход по
записям
Вторая запись
Курс
298
Основы SQL
4. Пусть в таблице Книга представлены записи, где значения
поля КодКниги равны 1, 2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 и 10. Создан
курсор для вывода списка книг издательства «Мир».
DECLARE @n VARCHAR(50),
@iz VARCHAR(50),
@p MONEY,
@message VARCHAR(80)
DECLARE @cur1 CURSOR
PRINT ' Список клиентов'
DECLARE cursor1 CURSOR GLOBAL SCROLL KEYSET FOR
SELECT Название, Издательство, Цена
FROM Книга
WHERE Издательство=”Мир”
OPEN cursor1
SET @cur1=cursor1
FETCH ABSOLUTE 9 FROM cursor1 INTO @n, @iz, @p
DELETE Книга
WHERE CURRENT OF @cur1
FETCH RELATIVE -2 FROM cursor1 INTO @n, @iz, @p
UPDATE Книга SET Цена=Цена*1.5
WHERE CURRENT OF cursor1
SELECT @message='Книга '+@n+' Цена '+
CAST(@p AS VARCHAR(4))
PRINT @message
CLOSE cursor1
DEALLOCATE cursor1
Какие действия выполнит система?
Успешно удаляется запись, где КодКниги=9 и изменяется
запись, где
КодАвтора=8
Успешно удаляется запись, где КодКниги=9 и изменяется
запись, где
КодАвтора=7
Делается попытка удалить несуществующую запись
Объявление курсора не позволяет изменять и удалять записи
299
Лекция 13 Курсоры: принципы работы
Вариант 2
1. Разработан курсор для вывода списка рейсов до Москвы.
DECLARE @n INT,
@k VARCHAR(50),
@p MONEY,
@message VARCHAR(80)
DECLARE @cur1 CURSOR
PRINT ' Список рейсов'
DECLARE cursor1 CURSOR FOR
SELECT Номер_рейса, Стоимость, Конечный_пункт
FROM Рейс
WHERE Конечный_пункт=”Москва”
OPEN cursor1
SET @cur1=cursor1
FETCH NEXT FROM cursor1 INTO @p, @n, @k
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @message='Рейс '+ CAST(@n AS VA
CHAR(20))+' Стоимость '+ CAST(@p
AS VARCHAR(4))
PRINT @message
FETCH NEXT FROM cursor1 INTO @p, @n, @k
END
DEALLOCATE cursor1
CLOSE cursor1
Укажите правильные ответы.
Курсор написан без ошибо
В операторе FETCH порядок следования переменных не
соответствует полям оператора
SELECT в объявлении курсора
Освобождение ресурсов производится до закрытия курсора, что
недопустимо
Переприсваивание открытого курсора (оператор SET)
недопустимо
Курс
300
Основы SQL