Создание индексов, триггеров и хранимых процедур.
Индексы создаются для ускорения доступа к данным. Если мы в дереве объектов БД выберем Indexes, то увидим, что создание ключей привело к появлению в БД ряда индексов:
Рис.26 Ветвь Indexes содержит перечень всех индексов
Для ускорения доступа к данным в запросах о накладных, выданных за определенную дату, создадим для таблицы индекс по полю (по убыванию):
CREATE DESC INDEX NACL_PO_DATE ON NAKL(NAKL_DATE)
Добавим в таблицу TOVAR поле TOV_KVO, в котором будет храниться количество товара на складе, а в таблицу POKUP – поле POK_SUMM, в котором будет храниться сумма денег, авансированных покупателем для закупки товара:
ALTER TABLE TOVAR
ADD TOV_KVO INTEGER DEFAULT 0 CHECK(TOV_KVO >-1)
ALTER TABLE TOVAR
ALTER TOV_KVO POSITION 3
ALTER TABLE POKUP
ADD POK_SUMM DOUBLE PRECISION DEFAULT 0.0 CHECK(POK_SUMM > -25.0)
ALTER TABLE POKUP
ALTER POK_SUMM POSITION 4
Заполним поля данными, приведенными ниже:
Рис.27 Заполнение данными таблицы TOVAR
Рис.28 Заполнение данными таблицы POKUP
Реализация бизнес-правил на сервере средствами триггеров и хранимых процедур.
Последовательность выписки накладной на продажу товаров следующая:
Проверить сколько есть в наличии данного товара в таблице “TOVAR”. Если эта величина больше чем количество, продаваемое по накладной, то уменьшить количество товара в таблице “ TOVAR ” на то количество товара, которое записывается в таблицу OTPUSK. Если товара для продажи не хватает, то генерировать исключительную ситуацию «NO_TOVAR_QUANT».
CREATE EXCEPTION NO_TOVAR_QUANT 'Malo tovara.';
Определить Сумму на счету покупателя, Итоговую сумму в накладной, по которой продается товар и стоимость продаваемого товара.
Если ( Сумма на счету покупателя - Итоговая сумма в накладной - стоимость продаваемого товара > 10.0 ) то:
{ Записать новую Итоговую сумму в накладную;
Уменьшить Сумму на счету покупателя на соответствующую величину }
иначе
{. Если Денег на счету покупателя не хватает, то товар в накладную не включать.
Генерировать исключительною ситуацию «OTPUSK_CHN_NAKL_ID». };
CREATE EXCEPTION OTPUSK_CHN_NAKL_ID 'Nakl_ID must be unchanged.';
Для осуществления создадим следующие процедуры и триггера:
Процедура, подсчитывающая Итоговую сумму по накладной:
SET TERM !! ;
CREATE PROCEDURE TOTAL_NAKL (ID_NAKL integer)
RETURNS (NAKL_SUM DOUBLE PRECISION )
AS
BEGIN
SELECT SUM(O.KOLVO*T.TOV_CENA) as TOTAL
FROM OTPUSK O, TOVAR T
WHERE ( O.NAKL_ID = 1 ) and ( O.TOV_ID = T.ID_TOV)
INTO :NAKL_SUM;
EXIT;
END !!
SET TERM ; !!
Проверим ее работу при помощи ISQL:
EXECUTE PROCEDURE TOTAL_NAKL(1);
И вторая – возвращающая цену заданного товара:
SET TERM !! ;
CREATE PROCEDURE GET_TOV_CENA (TOV_ID integer)
RETURNS (T_CENA FLOAT )
AS
BEGIN
SELECT TOV_CENA
FROM TOVAR
WHERE ( ID_TOV = :TOV_ID )
INTO :T_CENA;
EXIT;
END !!
SET TERM ; !!
Создадим триггера AFTER INSERT, UPDATE и BEFORE DELETE для таблицы OTPUSK:
SET TERM !! ;
CREATE TRIGGER OTPUSK_AU FOR OTPUSK
AFTER UPDATE AS
DECLARE VARIABLE NAKL_TOT DOUBLE PRECISION;
DECLARE VARIABLE O_CENA FLOAT;
DECLARE VARIABLE N_CENA FLOAT;
BEGIN
IF( OLD.NAKL_ID <> NEW.NAKL_ID) THEN EXCEPTION OTPUSK_CHN_NAKL_ID;
ELSE
IF( (OLD.KOLVO <> NEW.KOLVO) OR (OLD.TOV_ID <> NEW.TOV_ID)) THEN BEGIN
IF(OLD.TOV_ID <> NEW.TOV_ID) THEN
BEGIN
UPDATE TOVAR SET TOV_KVO = (TOV_KVO + OLD.KOLVO)
WHERE (ID_TOV = OLD.TOV_ID);
UPDATE TOVAR SET TOV_KVO = (TOV_KVO - NEW.KOLVO)
WHERE (ID_TOV = NEW.TOV_ID);
END
ELSE
UPDATE TOVAR SET TOV_KVO = (TOV_KVO + OLD.KOLVO - NEW.KOLVO)
WHERE (ID_TOV = OLD.TOV_ID);
EXECUTE PROCEDURE TOTAL_NAKL(OLD.NAKL_ID) RETURNING_VALUES :NAKL_TOT;
EXECUTE PROCEDURE GET_TOV_CENA(OLD.TOV_ID) RETURNING_VALUES :O_CENA;
EXECUTE PROCEDURE GET_TOV_CENA(NEW.TOV_ID) RETURNING_VALUES :N_CENA;
UPDATE NAKL SET NAKL_ITOG = (:NAKL_TOT - :O_CENA*OLD.KOLVO + :N_CENA*NEW.KOLVO )
WHERE (ID_NAKL = OLD.NAKL_ID);
END
END !!
SET TERM ; !!
А также триггер
SET TERM !! ;
CREATE TRIGGER OTPUSK_AI FOR OTPUSK AFTER INSERT
AS
DECLARE VARIABLE NAKL_TOT DOUBLE PRECISION;
DECLARE VARIABLE N_CENA FLOAT;
DECLARE VARIABLE T_KVO INTEGER;
BEGIN
SELECT TOV_KVO FROM TOVAR
WHERE (ID_TOV = NEW.TOV_ID)
INTO :T_KVO;
IF(:T_KVO >= NEW.KOLVO ) THEN
BEGIN
EXECUTE PROCEDURE TOTAL_NAKL(NEW.NAKL_ID) RETURNING_VALUES :NAKL_TOT;
EXECUTE PROCEDURE GET_TOV_CENA(NEW.TOV_ID) RETURNING_VALUES :N_CENA;
UPDATE TOVAR SET TOV_KVO = (TOV_KVO - NEW.KOLVO)
WHERE (ID_TOV = NEW.TOV_ID);
UPDATE NAKL SET NAKL_ITOG = (:NAKL_TOT + :N_CENA*NEW.KOLVO )
WHERE (ID_NAKL = NEW.NAKL_ID);
END
END !!
SET TERM ; !!
И триггер для удаления записи:
SET TERM !! ;
CREATE TRIGGER OTPUSK_AD FOR OTPUSK AFTER DELETE
AS
DECLARE VARIABLE NAKL_TOT DOUBLE PRECISION;
DECLARE VARIABLE O_CENA FLOAT;
BEGIN
EXECUTE PROCEDURE TOTAL_NAKL(OLD.NAKL_ID) RETURNING_VALUES :NAKL_TOT;
EXECUTE PROCEDURE GET_TOV_CENA(OLD.TOV_ID) RETURNING_VALUES :O_CENA;
UPDATE TOVAR SET TOV_KVO = (TOV_KVO + OLD.KOLVO)
WHERE (ID_TOV = OLD.TOV_ID);
UPDATE NAKL SET NAKL_ITOG = (:NAKL_TOT - :O_CENA*OLD.KOLVO )
WHERE (ID_NAKL = OLD.NAKL_ID);
END !!
SET TERM ; !!
Осталось написать такие же триггера для таблицы NAKL
SET TERM !! ;
CREATE TRIGGER NAKL_AU FOR NAKL AFTER UPDATE AS
BEGIN
IF(OLD.NAKL_ITOG <> NEW.NAKL_ITOG ) THEN
UPDATE POKUP SET POK_SUMM = (POK_SUMM - NEW.NAKL_ITOG + OLD.NAKL_ITOG)
WHERE (ID_POKUP = NEW.POK_ID);
END !!
SET TERM ; !!
Подумайте как проверять сумму на счету покупателя и переделайте последний триггер.
Напишите процедуру, которая набор помещает в новую таблицу TOV_RASH суммарные количества и стоимости проданных за последний месяц товаров.