CCL (Cursor Control language) – язык управления курсорами, составная часть SQL.
Как вы уже поняли, команды манипулирования данными SELECT, UPDATE, DELETE работают сразу с группами строк. Эти группы, вплоть до отдельных строк, можно выбрать с помощью опции WHERE. Что же делать в том случае, если требуется перебрать строки некоторой таблицы последовательно, одну за другой? Для этого в языке SQL существует такое понятие, как курсор. Курсор (current set of record) – это временный набор строк, которые можно перебирать последовательно, с первой до последней.
Для работы с курсорами существуют следующие команды.
Объявление курсора:
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())
Первый параметр этой функции означает единицу измерения (дни), второй и третий – даты, для которых мы вычисляем разность (текущая дата минус дата счета).
Далее для текущего счета вычисляем общую сумму пени. Пени будут начисляться на неоплаченную часть суммы счета, по заданному проценту за каждый день просрочки.
