 |
Задание для индивидуальной работы 7
|
|
|
|
Создайте несколько хранимых процедур для вашей базы данных. Можете использовать запросы с параметрами из позапрошлого занятия.
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 2011-12-12 00:00:00.000 0 1000
2 2012-01-12 00:00:00.000 0 2000
3 2012-02-12 00:00:00.000 0 2000
|
|
|
4 2012-01-12 00:00:00.000 0 6000
5 2012-02-12 00:00:00.000 0 2000
6 2012-02-12 00:00:00.000 0 2500
7 2012-01-12 00:00:00.000 0 1500
8 2012-01-12 00:00:00.000 0 1200
9 2012-02-12 00:00:00.000 0 10000
Пусть, например, сегодня 1 мая 2012 г. Запустим процедуру.
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 2011-12-12 00:00:00.000 0 1000
2 2012-01-12 00:00:00.000 550 2000
3 2012-02-12 00:00:00.000 0 2000
4 2012-01-12 00:00:00.000 2750 6000
5 2012-02-12 00:00:00.000 790 2000
6 2012-02-12 00:00:00.000 988 2500
7 2012-01-12 00:00:00.000 275 1500
8 2012-01-12 00:00:00.000 110 1200
9 2012-02-12 00:00:00.000 3950 10000
|
|
|
II Письменное задание
II. Письменное задание
II. Письменное задание
III. Домашнее задание.
S_PEXT: Задание параметров и запуск таймера «Импульс с памятью»
XX. Дополнительное задание
Алгоритм работы управляющего автомата
Вопрос 15. Задание движения точки в полярных координатах.
Все студенты и ученики с ограниченными возможностями имеют право на разработку индивидуальной программы образования.
Все, что необходимо прописать в индивидуальной программе образования.
