1. Создайте функцию F_TAX, рассчитывающую подоходный налог с зарплаты (13%). В качестве параметра должна выступать зарплата сотрудника. Для описания типа данных параметра и возвращаемого значения используйте атрибут %TYPE.
2. Создайте функцию F_COMMISION, возвращающую размер комиссионных сотрудника. Если сотрудник не получает комиссионные, то возвращаться должен 0, а не пустое значение. В качестве параметра должен передаваться номер сотрудника. Для описания типа данных параметра и возвращаемого значения используйте атрибут %TYPE.
3. Создайте функцию F_TAKE_COM, которая проверяет получает ли сотрудник комиссионные. Эта функция в качестве параметра должна получат номер сотрудника. А возвращать должна логическую переменную (BULEAN). Для описания типа данных параметра и возвращаемого значения используйте атрибут %TYPE. В теле функции используйте оператор выбора IF, в условии которого вызовите функцию F_COMMISION. И в зависимости от условия верните либо TRUE, либо FALSE.
4. В анонимном блоке рассчитайте общую сумму налога всех для работников не получающих комиссионные. Используйте функцию F_TAKE_COM, чтобы проверить получает ли работник комиссионные, и функцию F_TAX для расчета налога. Используте цикл, чтобы проверить всех работников. Общую сумму налога сохраняйте в локальной переменной. В конце напечатайте ее с поясняющим предложением («Общая сумма налога равна ___»).
Лабораторная работа *5
Основы PL/SQL. Триггеры
Триггер — это блок PL/SQL, хранимый в памяти, который срабатывает при наступлении определенного события.
Триггеры могут быть связаны с таблицей, представлением, схемой данных и базой данных в целом. Будем рассматривать только триггеры на таблицы и представления.
Типы событий, на которые срабатывают триггеры
· DML-команды (Delete, Insert, Update [of column], Merge)
· DDL-команды (Create, Alter, Drop)
· События сервера БД
Будем рассматривать только DML-команды. DML-тригеры бывают простые и комбинированные. Простые триггеры могут срабатывать до наступления события (Before), после наступления события (After) или вместо события (Instead of; для представлений).
Также триггеры делятся на операторные (statement), которые срабатывают один раз, и строчные, которые срабатывают на каждую обработанную строку (могут не выполниться ни разу, если обработано 0 строк).
Порядок срабатывания триггеров (на одну DML-операцию):
1. Before statement trigger
2. Before row trigger
3. After row trigger
4. … (Before row trigger и After row trigger для каждой последующей строки)
5. After statement trigger
Синтаксис создания триггера
CREATE [OR REPLACE] TRIGGER имя_триггера
время_срабатывания событие_1 [OR событие_2 OR...]
ON имя_объекта
[REFERENCING OLD AS old | NEW AS new ]
[FOR EACH ROW
[WHEN (условие)]]
[DECLARE
объявления_локальных_переменных; …]
BEGIN
команды
[EXCEPTION...]
END [ имя_триггера ];
время_срабатывания BEFORE | AFTER | INSTEAD OF
события INSERT | DELETE | UPDATE | UPDATE OF column_list
После ключевого слова WHEN можно указать ограничение действия для строчного триггера
Квалификаторы OLD и NEW
Когда срабатывают строчные триггеры, машина PL/SQL создает и заполняет две структуры данных:
1. OLD: содержит исходные значения полей строки, для которой сработал триггер
2. NEW: содержит новые значения
NEW и OLD имеют такую же структуру как и строка таблицы, с которой работает триггер. Что хранится в этих квалификаторах в зависимости от DML-команды показано в таблице.
Обращаться к данным через квалификаторы нужно следующим образом:
:OLD.column_name
:NEW.column_name
Пример использования квалификаторов OLD и NEW.
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp(user_name, time_stamp, id,
old_last_name, new_last_name, old_title,
new_title, old_salary, new_salary)
VALUES (USER, SYSDATE,:OLD.employee_id,
:OLD.last_name,:NEW.last_name,:OLD.job_id,
:NEW.job_id,:OLD.salary,:NEW.salary);
END;
В данном примере выполняется журнализация (логирование) действий с таблицей employees.
Удалить триггер можно командой DROP.
DROP TRIGGER trigger_name;
Комбинированные триггеры
Комбинированные триггеры ( Сompound trigger) дают возможность определить действия для каждой из 4х временных точек в одном триггере:
· До срабатывания команды (before statement)
· До обработки каждой строки (before each row)
· После обработки каждой строки (after each row)
· После срабатывания команды (after statement)
Тело комбинированного триггера может включать в себя глобальны переменные, используемые во всех обработчиках событий. Они инициализируются при срабатывании триггера и уничтожаются после завершения DML-оператора. В комбинированный триггер можно включать явный код инициализации триггера (выполняется перед «before statement»). Также можно написать секцию завершения (finalization). Она выполняется после «after statement» даже при ошибках.
Синтаксис создания комбинированного триггера для таблиц.
CREATE OR REPLACE TRIGGER schema.trigger
FOR INSERT | DELETE | UPDATE ON schema.table
COMPOUND TRIGGER
-- Секция инициализации
-- Описания переменных, типов и т.п.
-- Локальные подпрограммы
-- Далее секции событий, необязательные;
-- Секции должны располагаться именно в таком порядке
BEFORE STATEMENT IS
… команды;
AFTER STATEMENT IS
… команды;
BEFORE EACH ROW IS
… команды;
AFTER EACH ROW IS
… команды;
Синтаксис создания комбинированного триггера для представлений.
CREATE OR REPLACE TRIGGER schema.trigger
FOR INSERT | DELETE | UPDATE ON schema.view
COMPOUND TRIGGER
-- Секция инициализации (Описания переменных, типов и т.п.; подпрограммы)
-- Далее секция событий, необязательно;
INSTEAD OF EACH ROW IS
… команды;
Команды в секциях событий следует заключать в блок (BEGIN … END;)
:OLD and:NEW не могут присутствовать в разделе declaration, секциях BEFORE STATEMENT, или AFTER STATEMENT.
Только в секции BEFORE EACH ROW можно изменить значение:NEW.