КАТЕГОРИИ: Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748) |
CCL. Курсоры
CCL (Cursor Control language) – язык управления курсорами, составная часть SQL. Как вы уже поняли, команды манипулирования данными SELECT, UPDATE, DELETE работают сразу с группами строк. Эти группы, вплоть до отдельных строк, можно выбрать с помощью опции WHERE. Что же делать в том случае, если требуется перебрать строки некоторой таблицы последовательно, одну за другой? Для этого в языке SQL существует такое понятие, как курсор. Курсор (cur rent s et of r ecord) – это временный набор строк, которые можно перебирать последовательно, с первой до последней. Для работы с курсорами существуют следующие команды.
Объявление курсора: DECLARE имя_курсора CURSOR FOR SELECT текст_запроса Таким образом, любой курсор создается на основе некоторого оператора SELECT.
Открытие курсора: OPEN имя_курсора Только после открытия курсора он становится активным, и из него можно читать строки.
Чтение следующей строки из курсора: FETCH имя_курсора INTO список_переменных Переменные в списке должны иметь тот же количество и тип, что и столбцы курсора. Глобальная переменная @@FETCH_STATUS принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю, и оператор FETCH перепишет значения полей из текущей строки в переменные.
Закрытие курсора: CLOSE имя_курсора
Для удаления курсора из памяти используется команда DEALLOCATE имя_курсора
Для иллюстрации использования курсора создадим процедуру для начисления пени по тем счетам, по которым истек срок платежа.
CREATE PROCEDURE peni @percent NUMERIC(5,2) AS DECLARE @num INT, @dat DATETIME, @days INT, @sum NUMERIC(6)
IF @percent > 0 AND @percent < 100
BEGIN DECLARE cur1 CURSOR FOR SELECT bill_num, bill_term FROM k_bill b WHERE bill_term<GETDATE() AND (bill_sum> (SELECT SUM(payment_sum) FROM k_payment p WHERE b.bill_num=p.bill_num) OR NOT EXISTS (SELECT bill_num FROM k_payment p WHERE b.bill_num=p.bill_num) )
OPEN cur1 FETCH cur1 INTO @num, @dat
WHILE @@FETCH_STATUS=0 BEGIN SELECT @days=DATEDIFF(day, @dat, GETDATE())
SELECT @sum=ISNULL(SUM(payment_sum),0) FROM k_payment WHERE @num=bill_num
UPDATE k_bill SET bill_peni=(bill_sum-@sum)*@percent/100*@days WHERE @num=bill_num
FETCH cur1 INTO @num, @dat
END DEALLOCATE cur1
END GO
Рассмотрим эту процедуру более подробно. Параметром этой процедуры является процент для вычисления пени. Объявляем курсор на основе следующего запроса: выбрать счета, которые оплачены не полностью и по которым истек срок платежа (т.е, срок оплаты менее текущей даты). В эту выборку попадут частично оплаченные счета, для которых выполняется условие bill_sum> (SELECT SUM(payment_sum) FROM k_payment p WHERE b.bill_num=p.bill_num)
а также полностью неоплаченные счета, для которых платежей вообще не существует, т.е., выполняется условие NOT EXISTS (SELECT bill_num FROM k_payment p WHERE b.bill_num=p.bill_num)
Открываем курсор и читаем из него в цикле последовательно по одной строке. Каждая строка содержит информацию об одном просроченном неоплаченном счете. Для текущего счета вычисляем количество дней, на который он просрочен, с помощью функции DATEDIFF: DATEDIFF(day, @dat, GETDATE()) Первый параметр этой функции означает единицу измерения (дни), второй и третий – даты, для которых мы вычисляем разность (текущая дата минус дата счета). Далее для текущего счета вычисляем общую сумму оплаты. Пени будут начисляться на неоплаченную часть суммы счета, по заданному проценту за каждый день просрочки.
Напечатаем список счетов до выполнения процедуры: SELECT bill_num, bill_term, bill_peni, bill_sum FROM k_bill
bill_num bill_term bill_peni bill_sum -------- --------------------------- --------- -------- 1 2006-04-02 16:46:10.800 0 1000 2 2006-04-02 16:46:10.810 0 2000 3 2006-04-02 16:46:10.810 0 2000 4 2006-04-02 16:46:10.810 0 6000 5 2006-04-02 16:46:10.810 0 2000 6 2006-04-02 16:46:10.810 0 2500 7 2006-04-02 16:46:10.810 0 1500 8 2006-04-02 16:46:10.810 0 1200 9 2006-04-02 16:46:10.810 0 10000
(9 row(s) affected)
Пусть, например, сегодня 1 мая 2006 г. Запустим процедуру.
EXEC peni 0.5
Напечатаем список счетов после выполнения процедуры: SELECT bill_num, bill_term, bill_peni, bill_sum FROM k_bill
bill_num bill_term bill_peni bill_sum ------------------------------------------------------- 1 2006-04-02 16:46:10.800 0 1000 2 2006-04-02 16:46:10.810 145 2000 3 2006-04-02 16:46:10.810 0 2000 4 2006-04-02 16:46:10.810 725 6000 5 2006-04-02 16:46:10.810 290 2000 6 2006-04-02 16:46:10.810 363 2500 7 2006-04-02 16:46:10.810 0 1500 8 2006-04-02 16:46:10.810 29 1200 9 2006-04-02 16:46:10.810 1450 10000
(9 row(s) affected)
Задание для индивидуальной работы 9 Создайте хранимую процедуру с использованием курсора для вашей базы данных.
Дата добавления: 2015-05-09; Просмотров: 446; Нарушение авторских прав?; Мы поможем в написании вашей работы! Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет |