DECLALE

   Описание переменных и констант

BEGIN

   Операторы

EXCEPTION

   Операторы

END;

Имя [CONSTANT] тип данных [:= значение];

-- переменные

окружения

set serveroutput on;

set echo on;

DECLARE

Pi CONSTANT real :=3.14;

x real :=1;

BEGIN

DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x));

x:=x+1;

DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x));

END;

/

Операторы управления выполнением программы PL/SQL

Операторы PL/SQL выполняются последовательно. Такая схема называется потоком команд. Изменить последовательный порядок выполнения команд можно с помощью команд управления потоком - оператором ветвления, оператором цикла и командой выхода из цикла.

Оператор IF условие TNEN группа операторов 1 ELSE группа операторов 2 позволяет проверить условие и в зависимости от результата проверки выполнить различные группы операторов. Если условие принимает значение TRUE, то выполняется группа операторов 1, в противном случае - группа операторов 2. Границы действия оператора IF определяются закрывающейся операторной скобкой END IF. Для расширения структуры ветвления предусмотрена операторная скобка ELSIF.

Синтаксис оператора ветвления следующий:

IF

Условие

THEN

   группа операторов 1

   ELSIF условие 1

   THEN

   Группа операторов 3

   ELSIF

…

ELSE 

группа операторов 2

END IF

Пример. Изменение потока команд

DECLARE

Pi CONSTANT real :=3.14;

x real :=1;

BEGIN

x:=Input_Data;

IF (x>0.5)

   THEN DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x));

ELSIF (x< 0.4)

THEN DBMS_OUTPUT.PUT_LINE ('y ='|| cos(Pi*x));

ELSE

x:=x+1;

DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x));

END IF;

END;

/

Переменная, имени которой предшествует знак &, вводится с терминала пользователя.

Организация цикла в программах на PL/SQL может быть выполнена несколькими способами. Рассмотрим примеры вычисления суммы.

Пример. Оператор LOOP.

DECLARE

X number;

I number;

Limit number:=1000;

BEGIN

I:=0;

X:=0;

LOOP

EXIT WHEN I > Limit;

I:=I+1;

X:=X+I*I;

END LOOP;

DBMS_OUTPUT.PUT_LINE (x);

END;

/

Оператор LOOP открывает цикл. Конструкция EXIT WHEN обеспечивает выход из цикла при выполнении условия, а закрывающая операторная скобка END LOOP завершает цикл.

Пример. Оператор WHILE

DECLARE

X number;

I number;

Limit number:=1000;

BEGIN

I:=0;

X:=0;

WHILE I <= Limit LOOP

I:=I+1;

X:=X+I*I;

END LOOP;

DBMS_OUTPUT.PUT_LINE (x);

END;

/

Управление потоком команд типа WHILE обеспечивает выполнение цикла до тех пор, пока условие является истинным.

Пример. Оператор FOR

DECLARE

X number;

Limit number:=1000;

BEGIN

I:=0;

X:=0;

FOR I IN 0..Limit LOOP

X:=X+I*I;

END LOOP;

DBMS_OUTPUT.PUT_LINE (x);

END;

/

Цикл, управляемый оператором FOR, используется, когда точно известно, сколько раз нужно выполнить операторы цикла. Переменную цикла описывать в блоке DECLARE не нужно.

Курсоры PL/SQL

Курсор - это поименованный запрос, содержащий некоторое число строк в выборке. По сути, курсор является некоторой структурой, через которую пользователь получает доступ к строкам результирующей таблицы запроса.

Рассмотрим пример доступа к информации, которая хранится в базе данных, с использованием курсоров.

Пусть в базе данных есть таблица базы данных с именем T01, такая, как показана ниже.

Опишем курсор для доступа к данным таблицы Т01.

CURSOR cur01 IS SELECT * FROM T01;

Работа с курсором выполняется по следующему алгоритму:

1.    Открываем курсор:

2.    OPEN cur01;

3.    Выбираем данные из курсора в набор совместимых по типу переменных командой FETCH:

4.    FETCH cur01 INTO x1,x2,x3;

5.    Обрабатываем полученные данные.

6.    Выполняем команду FETCH для получения данных из следующей строки результирующей таблицы запроса.

И т.д.

В PL/SQL для курсоров предусмотрено несколько методов. Метод %NOTFOUND возвращает булевское истинное значение, если выборка в курсор пуста. Метод %FOUND возвращает булевское истинное значение, если выборка в курсор непуста. После открытия курсора до первой команды FETCH значения, возвращаемые этими методами, равны NULL. Метод %ROWCOUNT возвращает число строк в выборке после открытия курсора.

Предопределенный в PL/SQL метод %TYPE позволяет определить тип переменной как совпадающий с типом переменной таблицы.

PL/SQL поддерживает тип данных RECORD, который позволяет создать объект, соответствующий строке таблицы, как показано в примере ниже.

DECLARE TYPE t01_rec_type IS RECORD

( x1 t01.A1%TYPE,

x2 t01.A2%TYPE,

x3 t01.A3%TYPE);

t01_rec t01_rec_type;

…

FETCH cur1 INTO t01_rec;

DBMS_OUTPUT.PUT_LINE (cur1%ROWCOUNT||' '||t01_rec.x2);

ѕ.

Обработка исключительных ситуаций в PL/SQL

Исключительная ситуация - это возникновение предопределенного и описанного события в системе. Например, ошибки преобразования типов переменных или переполнения при делении на нуль. Пример некоторых предопределенных ситуаций, распознаваемых в PL/SQL, приведен в таблице 12.1 ниже. Для получения полного списка таких ситуаций следует обратиться к документации по PL/SQL.

При возникновении исключительной ситуации в системе происходит автоматическая передача управления в блок EXCEPTION программы PL/SQL.

Рассмотрим пример обработки исключительной ситуации в программе, когда кодировщик пропустил оператор открытия курсора.

Пример

DECLARE

X1 T01_A1%TYPE;

X2 T01_A2%TYPE;

X3 T01_A3%TYPE;

CURSOR CUR1 IS SELECT * FRON T01;

BEGIN

WHILE CUR1%FOUND LOOP

FETCH CUR1 INTO X1,X2,X3;

DBMS_OUTPUT.PUT_LINE (X1||' '||X2||' '||X3);

END LOOP

EXCEPTION

WHEN INVALID_CURSOR THEN

DBMS_OUTPUT.PUT_LINE ('ОШИБКА. НЕ

ОТКРЫТ

КУРСОР1');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('НЕИЗВЕСТНАЯ

ОШИБКА.');

END;

/

Для обработки ситуаций, не входящих в перечень стандартных, можно использовать специальный обработчик PL/SQL OTHERS или описать пользовательскую исключительную ситуацию и запрограммировать ее обработку. Ключевое слово OTHERS блока EXCEPTION определяет универсальный алгоритм обработки исключительных ситуаций, не входящий в список стандартных.

Исключительная ситуация, задаваемая пользователем, должна быть описана в блоке DECLARE, как Имя исключительной ситуации EXCEPTION. После обнаружения исключительной ситуации в программе PL/SQL она должна быть сгенерирована указанием ключевого слова и имени исключительной ситуации - RAISE Имя исключительной ситуации. В блоке EXCEPTION должен быть предусмотрен код для ее обработки.

Например,

DECLARE

…

User_ecx EXCEPTION;

…

BEGIN

IF x1 < 0

THEN RAISE User_exc;

END IF;

…

EXCEPTION

WHEN User_exc THEN

DBMS_OUTPUT.PUT_LINE (ПОЛЬЗОВАТЕЛЬСКАЯ ИСКЛЮЧИТЕЛЬНАЯ СИТУАЦИЯ.');

…

В этом разделе мы рассмотрели основные конструкции расширения SQL в Oracle PL/SQL. Для более глубокого изучения этого языка следует обратиться к списку литературы или документации.

Создание хранимых процедур и функций

Определение хранимых процедур и функций в PL/SQL

Процедура или функция PL/SQL имеет уникальное имя. Как и программы на PL/SQL, процедуры и функции имеют блок объявлений, блок исполняемого кода и, опционально, блок обработки исключительных ситуаций. Но процедура может принимать и возвращать значения параметром, а функция дополнительно возвращает значение.

Описание процедуры имеет следующий синтаксис:

PROCEDURE имя [(параметр [, параметр, ...])] IS

   [объявление локальных переменных, пользовательских типов данных, 

   пользовательских исключительных ситуаций, локальных подпрограмм и функций]

BEGIN

   Исполняемый код

[EXCEPTION

   обработчики исключительных ситуаций]

END [имя];

Параметры процедуры обеспечивают входные и выходные данные процедуры. Параметров у процедуры может и не быть, так что этот раздел не является обязательным. Для задания параметров используется следующий синтаксис:

Имя параметра [IN | OUT | IN OUT] тип данных [{:= | DEFAULT} выражение]

В определении параметров нельзя использовать ограничение NOT NULL, а в определении типа данных нельзя использовать никакие ограничения. Для каждого параметра должен быть указан его тип (parameter mode) - IN, OUT или IN OUT. Указание типа IN означает, что значение параметра определяется при обращении к процедуре и не изменяется процедурой. Попытка изменить такой параметр в теле процедуры приведет к возникновению ошибки. Указание типа OUT предполагает изменение значения параметра в процессе выполнения процедуры, т.е. это возвращаемый параметр. Указание типа IN OUT говорит о том, что при вызове процедуры такому параметру должно быть присвоено значение, которое может быть изменено в теле процедуры. Типом по умолчанию считается IN. Ниже в таблице 12.2 суммирована информация о типах параметров.

Определение процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END. Тело процедуры состоит из трех частей, которые отвечают блокам программы PL/SQL.

Пример. Для нашей учебной базы данных можно рассмотреть вопрос о создании процедуры, которая увеличивает зарплату сотрудника по значению его номера.

PROCEDURE raise_salary (empid INTEGER, increase REAL) IS

   current_salary REAL;

   salary_missing EXCEPTION;

BEGIN

    SELECT sal INTO current_salary FROM employee WHERE 

   empno = emp_id;

    IF current_salary IS NULL THEN

      RAISE salary_missing;

   ELSE

      UPDATE employee SET sal = sal + increase

         WHERE empno = emp_id;

   END IF;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      INSERT INTO emp_audit VALUES (emp_id, 'Нет

сотрудника

   с

таким

номером');

   WHEN salary_missing THEN

      INSERT INTO emp_audit VALUES (emp_id, Зарплата

не

назначена');

END raise_salary;

При вызове процедура принимает номер сотрудника и величину прибавки к зарплате в качестве параметров. Номер сотрудника используется далее для выборки значения текущей зарплаты из таблицы EMPLOYEE в локальную переменную current_salary. Если номер сотрудника не найден в базе данных или значение зарплаты не установлено, то возникают исключительные ситуации, стандартная и определенная пользователем соответственно. В противном случае зарплата обновляется.

Процедура вызывается как команда PL/SQL

raise_salary (emp_num, amount);

В примере использована вспомогательная таблица базы данных emp_audit для отслеживания возникновения ошибочных ситуаций при работе с базой данных.

Рассмотрим еще один пример использования процедуры для выборки зарплаты служащего по его фамилии из таблицы EMPLOYEE учебной базы данных.

PROCEDURE emp_salary (sName INTEGER, sal_p OUT REAL) IS

   current_salary REAL;

   salary_missing EXCEPTION;

BEGIN

    SELECT sal INTO current_salary FROM employee WHERE ENAME=:sName';

    IF current_salary IS NULL THEN

      RAISE salary_missing;

    ELSE

       sal_p = current_salary

    END IF;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      INSERT INTO emp_audit VALUES (emp_id, 'Нет

сотрудника

      с

такой

фамилией');

   WHEN salary_missing THEN

      INSERT INTO emp_audit VALUES (emp_id, Зарплата

не

назначена');

END raise_salary;

Описание функции имеет следующий синтаксис:

FUNCTION имя [(параметр [, параметр, ...])] RETURN тип данных IS

   [объявление локальных переменных, пользовательских типов данных, 

   пользовательских исключительных ситуаций, локальных подпрограмм и функций]

BEGIN

   Исполняемый код

[EXCEPTION

   обработчики исключительных ситуаций]

END [имя];

Как можно видеть, описание функции отличается от описания процедуры наличием в спецификации процедуры предложения RETURN с указанием типа возвращаемого значения. Возврат значения функции выполняется командой PL/SQL RETURN.

Пример. Рассмотрим пример функции, которая возвращает логическое значение "истина" или "ложь", если зарплата служащего находится в установленных пределах.

FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS

   min_sal REAL;

   max_sal REAL;

BEGIN

   SELECT losal, hisal INTO min_sal, max_sal 

      FROM salaryes WHERE job = title;

   RETURN (salary >= min_sal) AND (salary <= max_sal);

END sal_ok;

При вызове функции она принимает зарплату сотрудника и его должность в качестве входных параметров. Она выбирает установленные нижнюю и верхнюю границы зарплаты из таблицы базы данных salaryes для заданной должности. В предложении RETURN идентификатор функции sal_ok устанавливается к логическому значению в результате проверки.

Функция вызывается как часть выражения PL/SQL, где идентификатор функции действует как переменная, значение которой зависит от параметров, например

IF sal_ok(new_sal, new_title) THEN ...

Создание хранимых процедур и функций

Хранимая процедура или функция есть объект реляционной базы данных, который является поименнованным набором операторов SQL и, в случае СУБД Oracle, набором операторов PL/SQL, который может быть скомпилирован и необязательно сохранен в базе данных. Если процедура сохраняется в базе данных, то она называется хранимой процедурой или функцией. Описание хранимых процедур и функций хранится в словаре данных реляционной базы данных.

Обычно хранимые процедуры используются для выполнения набора типовых действий, характерных для достижения целей создания базы данных, а хранимые функции - для вычисления значений.

Код хранимых процедур и функций хранится в базе данных. Поэтому использование хранимых процедур и функций для выполнения типовых операций, характерных для приложения базы данных, приводит к упрощению приложений за счет передачи обработки на сервер базы данных. Такой подход уменьшает накладные расходы за счет сокращения фазы синтаксического анализа и уменьшения времени передачи запроса на конкретное действие по вычислительной сети, что приводит в целом к снижению трафика сети. Обычно при этом улучшается производительность, но это необязательно для маломощных серверов баз данных.

Хранимые процедуры и функции, как объекты базы данных, создаются командой CREATE и уничтожаются командой DROP. Команда создания хранимой процедуры имеет следующий синтаксис:

CREATE [OR REPLACE] PROCEDURE [имя схемы].имя процедуры

[имя [(параметр [, параметр, ...])] {IS|AS}

программа на PL/SQL;

Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если процедура с таким именем уже определена, но ключевое слово OR REPLACE не указано, то возвращается сообщение об ошибке и замещения старого текста не происходит.

При описании переменных хранимой процедуры ключевое слово DECLARE не используется. Блок описания переменных начинается сразу после ключевого слова IS или AS.

Пример.

PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS

BEGIN

   INSERT INTO dept

      VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);

END create_dept;

Исполнение созданной процедуры может быть выполнено оператором EXEC PL/SQL, как показано ниже:

EXEC ;

Команда создания хранимой процедуры имеет следующий синтаксис:

CREATE [OR REPLACE] FUNCTION [имя

схемы].имя

функции

[имя [(параметр [, параметр, ...])] 

RETURN тип данных

{IS|AS}

программа на PL/SQL;

Как можно увидеть, синтаксис команды создания хранимой функции похож на синтаксис команды создания хранимой процедуры, за исключением того, что в описании функции используется предложение RETURN. Описание типа данных для возвращаемого значения требуется обязательно.

Пример. Для нашей учебной базы данных создадим функцию, которая считает число сотрудников, поступивших в организацию за определенный период:

CREATE OR REPLACE FUNCTION emp_cnts (data1 IN date, data2 IN date)

RETURN Integer

IS

I_count number:=0;

BEGIN

SELECT COUNT(*) INTO i_count FRON employee

WHERE hiredate BETWEEN date1 AND date2;

RETURN i_count;

END;

Вызвать созданную хранимую функцию можно следующим образом:

EXEC DBMS_OUTPUT.PUT_LINE(emp_cnts('01-may-02', ''01-jul-02'));

Для уничтожения хранимой процедуры или функции в базе данных используется команда DROP в формате DROP [имя схемы].имя процедуры;

Или DROP [имя схемы].имя функции;

Особенности использования процедур и функций в СУБД Oracle

В этом подразделе рассмотрим некоторые особенности использования процедур и функций в PL/SQL:

· Способы связывания формальных и фактических параметров в процедурах и функциях.

· Использование для параметров значений по умолчанию.

· Возникновение алиасных имен при передаче параметров.

· Перезагрузка имен процедур и функций.

При вызове процедур и функций в PL/SQL допускается передача параметров по имени и по позиции. Это означает, что вы указываете, как происходит связывание формальных и действительных параметров. Например, пусть имеется гипотетическая программа

DECLARE

   x1 INTEGER;

   x2  REAL;

PROCEDURE proc1 (p1 INTEGER, p2 REAL) IS 

BEGIN 

... 

END;

Процедуру Proc1 можно вызвать следующими эквивалентными способами:

BEGIN

    ...

  proc1 (x1, x2);             -- передача параметров по позиции

  proc1 (p2 => x2, p1 => x1);  -- передача параметров по имени

  proc1 (p1 => x1, p2 => x2);  -- передача параметров по имени

  proc1 (x1, p2 => x2);        -- передача параметров и по 

                              -- позиции, и по имени

END;

При передаче параметра по позиции компилятор PL/SQL последовательно связывает первый фактический параметр с первым формальным параметром, второй фактический параметр - со вторым формальным параметром и так далее.

При передаче параметра по имени стрелка, называемая оператором связывания (association operator), связывает формальный параметр слева от стрелки с фактическим параметром справа от стрелки, причем порядок следования таких пар не имеет значения.

Можно комбинировать передачу параметра по позиции и по имени. Но при этом следует соблюдать требование того, чтобы передача параметра по позиции предшествовала передаче параметра по имени.

В процедуре или функции можно инициализировать параметр типа IN значением по умолчанию. Таким способом можно передавать различное число действительных параметров в процедуры или функции, принимая или переопределяя значение по умолчанию. Рассмотрим пример.

Пример. Для нашей учебной базы данных разработаем процедуру создания нового отдела таблице DEPARTAMENT.

PROCEDURE create_dept (new_dname CHAR DEFAULT 'Новый', new_loc 

                     CHAR DEFAULT 'Москва') 

IS

BEGIN

   INSERT INTO departament VALUES (deptno_seq.NEXTVAL, new_dname, 

                                 new_loc);

END create_dept;

Если при вызове процедуры фактический параметр не передается, то используется значение по умолчанию. Рассмотрим следующие вызовы этой процедуры:

create_dept;

create_dept('Маркетинг');

create_dept('Маркетинг', Черноголовка);

При первом вызове не передается никаких параметров, так что используются значения параметра по умолчанию - Новый отдел в г. Москве. Во втором вызове передается один параметр - первый. Для второго используется значение по умолчанию. В третьем случае передаются оба параметра и значения по умолчанию не используются.

Для оптимизации выполнения процедур и функций компилятор PL/SQL может выбирать различные методы передачи параметров (по значению или по ссылке) для различных параметров в одном и том же вызове процедуры или функции. Когда компилятор выбирает метод передачи параметра по значению, то его значение копируется в процедуре или функции. Когда компилятор выбирает метод передачи параметра по ссылке, то адрес фактического параметра передается в процедуру или функцию. Может создаться ситуация когда переменная в процедуре или функции будет иметь два имени - так называемая проблема алиасных имен. В этом случае результат будет неопределенным. Это происходит, когда глобальная переменная передается по адресу, как в примере ниже.

Пример

DECLARE

rent REAL;

PROCEDURE raise_rent (increase IN OUT REAL) IS

BEGIN

rent := rent + increase; -- в случае передачи параметра по адресу 

                  -- одна и та же переменная будет иметь два имени.

END raise_rent;

BEGIN

...

raise_rent(rent);  -- indeterminate

Такая ситуация может также возникнуть, когда фактическая переменная дважды передается в процедуру или функцию. Если оба формальных параметра имеют тип IN, то результат не определен.

Пример. Рассмотрим процедуру реверсирования строки.

DECLARE

   str VARCHAR2(10);

   PROCEDURE reverse (in_str VARCHAR2, out_str OUT VARCHAR2) IS

   BEGIN

      ...

   END reverse;

   ...

BEGIN

   str := 'abcd';

   reverse(str, str);  -- Не определен

Результат выполнения процедуры будет зависеть от выбранного компилятором способа передачи параметра в процедуру.

Компилятор PL/SQL позволяет также перезагружать (overload) имена процедур и функций, т.е. вы можете использовать одно и то же имя для нескольких различных процедур или функций. При этом число параметров, их порядок и типы данных могут быть различными.

Таким образом, основными преимуществами использования процедур и функций в базах данных являются:

· обеспечение расширяемости, т.е. они расширяют декларативный язык SQL возможностями процедурной обработки;

· обеспечение модульности, т.е. позволяют использовать поэтапный подход обработки данных из базы данных на основе хорошо определенной бизнес-логики;

· создание серверного кода базы данных с целью увеличения производительности за счет сокращения сетевого трафика и накладных расходов на компиляцию кода.

CREATE [OR REPLACE] PACKAGE [имя схемы].имя AS 

         Определения типов и объектов

         Спецификации процедур и функций

END [имя];

CREATE OR REPLACE PACKAGE paket1 AS 

A1 CONSTANT number:= 1.3;

PROCEDURE Pr1;

FUNCTION F01 (x1 real) RETURN real;

END;

/

CREATE [OR REPLACE] PACKAGE BODY [имя

схемы].имя AS 

         Объявления локальных типов и переменных

         Тела процедур и функций

[BEGIN

команды инициализации

END [имя];

CREATE OR REPLACE PACKAGE BODY paket1 AS 

Cnt number:=0;

FUNCTION F01(x1 real) RETURN real IS

BEGIN

Pr1;

RETURN x1*A1;

END;

PROCEDURE Pr1 IS

BEGIN

Cnt:=Cnt+1;

INSERT INTO T01 VALUES(Cnt,SYSDATE);

COMMIT;

END;

END;

/

package_name.type_name

package_name.object_name

package_name.subprogram_name

DROP PACKAGE [BODY] [имя схемы].имя пакета;

CREATE PACKAGE emp_actions AS 

TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);

CURSOR desc_salary RETURN EmpRecTyp;

PROCEDURE hire_employee (

      ename  VARCHAR2,

      job    VARCHAR2,

      mgr    NUMBER,

      sal    NUMBER,

      comm   NUMBER,

      deptno NUMBER);

PROCEDURE fire_employee (emp_id NUMBER);

END emp_actions;

CREATE PACKAGE BODY emp_actions AS  

CURSOR desc_salary RETURN EmpRecTyp IS

SELECT empno, sal FROM employee ORDER BY sal DESC;

PROCEDURE hire_employee (

      ename  VARCHAR2,

      job    VARCHAR2,

      mgr    NUMBER,

      sal    NUMBER,

      comm   NUMBER,

      deptno NUMBER) IS

   BEGIN

INSERT INTO employee VALUES (empno, ename, job, mgr, SYSDATE, 

                           sal, comm, deptno);

END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS

BEGIN

      DELETE FROM employee WHERE empno = emp_id;

END fire_employee;

Создание триггеров PL/SQL

Триггер базы данных (database trigger) является объектом реляционной базы данных, который активизирует выполнение хранимой (или встроенной) PL/SQL-процедуры при изменении пользователем данных в таблице. Событие, управляющее запуском триггера, описывается в виде логических условий. Например, попытка модифицировать данные в таблице активизирует триггер, соответствующий данной команде манипулирования данными. Число триггеров на таблицу базы данных не ограничено.

Обычно триггеры используют для реализации ограничений ссылочной целостности, для предотвращения несогласованных изменений в базе данных (поддержка целостности базы данных), для выполнения скрытых операций при модификации, а также для снижения сетевого трафика за счет передачи обработки на сервер. Операции завершения транзакции выполняются после обработки триггеров.

При выполнении команды UPDATE с помощью триггера можно проверить, что модифицируемые данные удовлетворяют ограничениям целостности базы данных до выполнения операции (при этом возможен доступ к новым данным!). После выполнения операции с помощью триггера можно выполнить скрытую обработку данных с учетом поступивших изменений (старые данные также могут быть доступны).

При выполнении команды INSERT также можно проверить данные до вставки в таблицу на допустимость ограничениям целостности, а после - выполнить операции над только что вставленными данными.

При выполнении команды DELETE можно проверить данные до их удаления или восстановить данные после удаления.

Для создания триггера предусмотрена специальная команда SQL CREATE TPIGGER. Эта команда создает триггер на таблице, которой владеет пользователь. Невозможно создать триггер для виртуальной таблицы.

Синтаксис команды следующий:

CREATE [OR REPLACE] TPIGGER [имя схемы.]имя триггера

{BEFORE|AFTER}

{INSERT|DELETE|UPDATE [OF имя колонки [, имя колонки ѕ]]}

[OR {INSERT|DELETE|UPDATE [OF имя колонки [, имя колонки ѕ]]}]

ON [имя схемы.]{имя таблицы|имя представления}

{FOR EACH ROW][WHEN условие]

спецификация пакета на PL/SQL

Ключевое слово OR REPLACE указывает на безусловное замещение старого теста триггера. Если оно не указывается, а триггер определен в базе данных, то замещения старого триггера не происходит, и возвращается сообщение об ошибке.

Определение триггера состоит из нескольких частей:

· задание имени триггера;

· указание команды SQL, к которой относится триггер;

· указание таблицы или представления, для которой определяется триггер;

· задание ограничений триггера;

· задание действия в теле триггера.

Если имя схемы опущено, то триггер создается в схеме текущего пользователя.

Первая часть - это указание команды, которая запускает триггер. При создании триггера необходимо указывать, к какой команде манипулирования данными он относится - INSERT, DELETE или UPDATE. Для последней модно указывать конкретные колонки, указав фразу OF имя_колонки [, имя_колонки ...] в предложении UPDATE.

Ключевое слово ON задает имя таблицы или представления, для которого создается триггер.

Ограничения триггера указывают дополнительные условия, которые должны удовлетворяться для строки, чтобы триггер запустился.

Триггер имеет время действия - до или после выполнения команды манипулирования данными, - которое задается ключевыми словами BEFORE или AFTER, соответственно. Обычно нельзя определить два триггера на одну и ту же операцию с одинаковым временем действия.

Необязательное ключевое слово ON EACH ROW определяет триггер как строчный, т.е. запускаемый для каждой строки результирующего множества команды SQL. Если оно опущено, то триггер запускается только один раз в начале обработки команды. Таким образом, условие "для каждой строки" активизируется, только когда есть строки (например, предложение WHERE дает истинное значение условий поиска), в то время как для условия "для каждой команды" триггер сработает и в этом случае.

Дополнительные условия, сужающие область действия триггера, могут быть заданы в предложении WHEN. Условия, задаваемые в этом предложении, являются стандартными для SQL условиями, должны содержать корреляционные имена и не могут содержать запрос. Это предложение может быть указано только для строчного триггера.

Действие, которое выполняет триггер, задается в теле триггера блоком кода PL/SQL, который не может содержать команд управления транзакциями, таких как COMMIT, ROLLBACK и SAVEPOINT.

Для того чтобы задать корреляционные имена, т.е. чтобы видеть старые и новые значения колонок при обновлении, нужно воспользоваться предложением REFERENCING OLD AS имя_таблицы_старых_значений NEW AS имя_таблицы_новых_значений. Определяемые имена являются псевдонимами для обновляемой таблицы и должны быть различны. На эти имена можно ссылаться в теле триггера. По умолчанию корреляционные имена есть OLD и NEW для старого и нового значения строки.

С каждым триггером можно связать его тип. Тип триггера определяется следующими условиями:

· когда запускается триггер (т.е. какое у него время действия);

· сколько раз запускается триггер.

Таким образом, тип триггера определяется всевозможными комбинациями ключевых слов BEFORE, AFTER и FOR EACH ROW, что дает четыре основных типа триггера, как показано в таблице 12.3 ниже.

В качестве примера создадим в нашей учебной базе данных триггер EMP_PERMIT_CHANGES. Этот триггер гарантирует, что изменение записей о сотрудниках можно делать только в рабочее время.

Пример

CREATE TRIGGER emp_permit_changes 

   BEFORE 

   DELETE OR INSERT OR UPDATE 

   ON employee 

   DECLARE 

      dummy           INTEGER; 

   BEGIN 

/* Если сегодня суббота или воскресенье, то ошибка. */ 

      IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR 

         TO_CHAR(SYSDATE, 'DY') = 'SUN') 

         THEN raise_application_error( -20501, 

      'Можно

изменять

таблицу employee только

в

рабочие

дни'); 

      END IF; 

/* Если праздник, то тоже ошибка */ 

      SELECT COUNT(*) 

         INTO dummy 

         FROM company_holidays 

         WHERE day = TRUNC(SYSDATE); 

      IF dummy > 0 

         THEN raise_application_error( -20501, 

         'Нельзя

изменять

таблицу employee по

праздникам'); 

      END IF; 

/* Если текущее время меньше 8:00 часов утра или 5:00 часов вечера, 

   то

ошибка */ 

      IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR 

         TO_CHAR(SYSDATE, 'HH24') >= 17) 

         THEN raise_application_error( -20502, 

      'Нельзя изменять таблицу employee в нерабочие часы'); 

      END IF; 

   END; 

Для работы этого триггера проектировщик базы данных должен предусмотреть таблицу company_holidays, которая будет содержать список праздничных дней в организации. Триггер будет запускаться до выполнения (BEFORE) любых операции модификации данных DELETE, INSERT или UPDATE над таблицей EMPLOYEE.

Триггер выполняет следующие операции:

· Если текущий день является субботой или воскресеньем, то выдается сообщение об ошибке и никаких изменений в таблице не производится.

· Триггер сравнивает текущую дату с датами праздников, разрешенных в организации. Если текущая дата - праздник, то выдается сообщение об ошибке и никаких изменений в таблице не производится.

· Если текущее время не находится в интервале от 8 до 17 часов, то выдается сообщение об ошибке и никаких изменений в таблице не производится.

В качестве второго примера создадим строчный триггер SALARY_CHECK для таблицы EMPLOYEE, который будет гарантировать, что зарплата сотрудника будет находиться в пределах должностного оклада, когда добавляется новый сотрудник или у существующего меняется зарплата или должность.

Пример

CREATE TRIGGER salary_check 

   BEFORE 

   INSERT OR UPDATE OF sal, job ON employee 

   FOR EACH ROW 

   WHEN (new.job <> 'PRESIDENT') 

   DECLARE 

      minsal                         NUMBER; 

      maxsal                                    NUMBER; 

   BEGIN 

/* Получить минимальные и максимальные ставки зарплаты для данной 

   должности из таблицы SAL_GUIDE  */ 

      SELECT minsal, maxsal 

         INTO minsal, maxsal 

         FROM sal_guide 

         WHERE job = :new.job; 

/* Если зарплата сотрудника находится вне пределов этого интервала, 

   то

генерировать

ошибку. */ 

      IF (:new.sal < minsal OR :new.sal > maxsal) 

      THEN raise_application_error( -20601, 

         'Зарплата ' || :new.sal || ' выходит

за

пределы

ставки

         по должности ' || :new.job || ' для сотрудника ' || :new.ename ); 

      END IF; 

   END; 

Триггер является строчным триггером с временем действия до начала операции. Этот триггер запускается до вставки или обновления каждой строки таблицы EMPLOYEE, которые изменяют значения колонок SAL или JOB. Триггер SALARY_CHECK имеет ограничение: он предотвращает контроль зарплаты директора организации. Для каждой новой или обновляемой записи триггер делает следующее:

· выдает запрос к таблице salary_guide, чтобы получить минимальную и максимальную ставки зарплаты для указанной должности;

· если зарплата выходит за пределы полученного интервала, то выдается ошибка и операция прекращается.

Рассмотрим вопрос о том, как решение проектировщика базы данных о создании триггера может оказывать влияние на структуру базы данных. Если проектировщик базы данных принимает решение создать триггер второго примера в базе данных, он должен добавить в физическую схему базы данных команду создания таблицы salary_guide, например как показано ниже.

CREATE TABLE salary_guide (

job char(20) NOT NULL,

min_sal dec(15,2),

max_sal dec(15,2) );

Если эта таблица будет заполнена с помощью команды

INSERT INTO salary_guide VALUES(:1,:2,:3)

\

Менеджер,2500000,10000000

Разработчик,1500000,2000000

Проектировщик,1000000,2000000

Кодировщик,500000,1000000

Тестировщик,500000,750000

Бухгалтер,1000000,1500000

Референт,500000,1000000

/

то нижеследующий оператор INSERT будет отвергнут:

INSERT INTO EMPLOYEE VALUES

(2003, 'Прохоров', 'Андрей', 20, 'Менеджер', 

  09-05-66, 08-15-95, 12000000, 0, 0, 1 );

Приведем пример создания триггера для операции удаления в таблицах, связанных отношением "родитель-потомок". Предположим, что таблица JOB_TAB является родительской для таблицы EMPLOYEE. При удалении строк из таблицы JOB_TAB триггер будет проверять наличие зависимых строк в таблице EMPLOYEE и, при наличии последних, блокировать выполнение удаления с кодом ошибки 20000, определенной пользователем.

CREATE TRIGGER JOB_DEL 

BEFORE 

DELETE ON JOB_TAB

   FOR EACH ROW 

   BEGIN 

      Select job from employee where employee.job=Job

      IF NO_DATA_FOUND

      THEN raise_application_error(-20000, 'Строка

не

может

быть

удалена'); 

END IF'

      END;

Данный триггер для операции удаления не допустит нарушения ссылочной целостности. Заметим, что ссылочная целостность не была определена средствами СУБД в данном примере.

Триггеры в базе данных могут находиться в двух состояниях - активном и пассивном. В активном состоянии триггер всегда запускается, когда возникает событие, его инициирующее. В пассивном состоянии триггер не запускается, когда возникает связанное с ним событие. Когда триггер создается, СУБД переводит его в активное состояние автоматически. Триггер можно перевести в пассивное состояние и обратно командой SQL ALTER TABLE с опциями DISABLE и ENABLE или с предложениями DISABLE и ENABLE.

Для удаления триггера используется команда SQL DROP, как показано в примере ниже.

DROP TRIGGER JOB_DEL;

Проектировщик базы данных принимает решения о создании триггеров, проектирует их код и создает их в базе данных. Фактически, триггер базы данных - это хранимая процедура, которая связана с таблицей базы данных. Она автоматически запускается, когда над таблицей выполняется команда, контролируемая триггером. При создании триггеров проектировщик должен четко представлять цели создания триггера, поскольку они могут оказывать влияние и на структуру, и на производительность базы данных. Триггеры могут быть созданы, в частности, для решения таких задач, как:

· поддержка специального аудита и мониторинга таблиц базы данных на определенные события;

· автоматическое генерирование значений производных колонок в таблице;

· для организации сложных мер обеспечения безопасности доступа к данным;

· для поддержки бизнес-правил и ограничений предметной области;

· для поддержки репликации асинхронных таблиц.