Файл: Лекция 14 Хранимый код. Триггеры 14 Процедурные расширения языка sql.doc

Основным недостатком хранимого кода является его непереносимость между различными СУБД. В силу этого, для масштабируемых информационных систем или систем, предназначенных для многократного тиражирования, часто используется трехзвенная архитектура системы с переносом значительной части бизнес-логики на уровень сервера приложений. Практикуется и такой вариант: серверная часть тиражируемой информационной системы разрабатывается сразу для нескольких самых распространенных СУБД (например, вариант для Oracle, PostgreSQL и Microsoft SQL Server).

Следует отметить, что хранимые процедуры и функции не следует рассматривать как альтернативу сложным SQL-запросам. По сравнению с «чистым» SQL любая процедурная альтернатива является худшим вариантом с точки зрения производительности, поскольку SQL-запросы любая СУБД обрабатывает с наивысшей эффективностью. Поэтому не нужно поддаваться соблазну и переходить на уровень процедурной логики там, где можно, пусть ценой больших мыслительных усилий, написать сложный, но эффективный запрос с использованием только средств языка SQL.

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

В Oracle традиционно основным языком хранимых процедур является язык PL/SQL, но поддерживаются и процедуры на других языках, прежде всего - на языках C++ и Java. В последнем случае хранимая процедура или функция называется внешней. В рамках нашего курса рассмотрим основной вариант – хранимая процедура на PL/SQL.

Хранимая процедура создается оператором SQL

CREATE [OR REPLACE] PROCEDURE имя[(список_параметров)]

AS

блок PL/SQL

Необязательная конструкция OR REPLACE позволяет заменять процедуру с таким же именем. Это очень удобно в процессе отладки.

Аналогично создается хранимая функция:

CREATE [OR REPLACE] FUNCTION имя[(список_параметров)]

RETURN тип_результата, возвращаемого функцией

AS

блок PL/SQL, обязательно содержащий оператор

RETURN выражение

В списке параметров должен быть описан режим использования каждого параметра: IN (только входной – используется по умолчанию), OUT (только выходной), INOUT (и входной, и выходной). Режим использования указывается после имени параметра. Типы параметров, как и типы переменных, можно указывать явно или с помощью ссылки на соответствующий столбец или таблицу.

При описании локальных переменных подпрограммы разрешено опускать ключевое слово DECLARE.

---

Удалить процедуру или функцию можно при помощи команды DROP.

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

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

Первая из процедур демонстрирует применение неявного курсора и предназначена для изменения телефона студента.

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

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

create procedure changephone(fiostud students.name_st%type,

newphone students.phone%type,

result out number)

as

oldphone students.phone%type; -- старый телефон

begin

select phone into oldphone from students where name_st=fiostud;

if oldphone!=newphone then

update students set phone=newphone where name_st=fiostud;

result:=0;

else

result:=1; -- старый и новый номера совпали

end if;

exception

when NO_DATA_FOUND then -- нет такого студента

result:=2;

when TOO_MANY_ROWS then

result:=3; -- есть однофамильцы

when OtherS then

result:=4; -- непредвиденная исключительная ситуация

end;

Выполнив команду создания данной процедурв в SQL*Plus, мы получим сообщение «Процедура создана». В случае, если в процедуре обнаружены синтаксические ошибки, выдается другое сообщение «Процедура создана с ошибками компиляции». Получить информацию об обнаруженных ошибках можно с помощью запроса к представлению словаря Oracle user_errors:

SELECT line, text FROM user_errors

Хранимая процедура запускается на выполнение по команде из клиентского приложения. Чтобы запустить ее на выполнение из SQL*Plus, необходимо поместить ее в блок PL/SQL, перед которым объявить переменную для выходного параметра:

var e number;

begin

changephone('Иванов', '555555', :e);

end;

Проверить значение переменной е можно при помощи команды:

---

PRINT e

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

CREATE Function getphone (fiostud students.name_st%type)

RETURN varchar

as

cursor c is

select phone from students

where name_st= fiostud; -- телефоны всех студентов с заданной фамилией

res varchar(50); -- строка результата

ph students.phone%type; -- переменная для команды fetch

BEGIN

open c;

res:='';

loop – цикл для извлечения данных из курсора

fetch c into ph;

EXIT WHEN NOT c%found;

res:=res||ph||' ';

end loop;

RETURN res ;

END;

Если использовать цикл по курсору, тело функции получится короче:

BEGIN

res:='';

for crec in c loop

res:=res||crec.phone||' ';

end loop;

RETURN res ;

END;

После создания функции проверить ее работоспособность можно совсем просто:

SELECT getphone('Иванов') FROM dual

14.4. Триггеры

Триггеры – особый вид хранимых процедур, которые запускаются автоматически при наступлении определенных событий в базе данных.

Особенности триггеров

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

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

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

---

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

• 
  уникальное имя триггера (задание параметров не требуется, поскольку триггер – процедура без параметров);
  
• 
  активизирующее действие - команда, которая вызывает запуск триггера, такими командами являются INSERT, DELETE, UPDATE;
  
• 
  время активизации - выполнение триггера до (BEFORE) или после (AFTER) выполнения активизирующего действия;
  
• 
  область действия - выполнение триггера либо один раз для каждого оператора модификации таблицы, либо для каждой строки (в последнем случае следует добавить фразу FOR EACH ROW);
  
• 
  условие активизации - необязательное дополнительное условие, которое должно выполняться для запуска триггера (фраза WHEN);
  
• 
  тело триггера – действия, выполняемые триггером (блок PL/SQL).
  

На каждое событие может быть создано и несколько триггеров. Однотипные триггеры выполняются в порядке их создания.

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

Команды SQL для работы с триггерами

Триггер создается при помощи команды SQL:

CREATE [OR REPLACE] TRIGGER имя_триггера

время_активизации активизирующая_команда ON имя_таблицы

[FOR EACH ROW]

[WHEN дополнительное условие запуска триггера]

AS

Блок PL/SQL

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

В теле триггера в Oracle можно использовать две предопределенные переменные, которые обозначают ту строку, которая в данный момент подвергается модификации:

:new – новое значение строки, применяется для команд INSERT и UPDATE

:old – старое значение строки (до модификации), применяется для команд DELETE и UPDATE

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

DROP TRIGGER имя_триггера

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

---

Для временного отключения триггера в Oracle можно применить команду:

ALTER TRIGGER имя_триггера DISABLE

Чтобы снова включить существующий триггер, используют команду:

ALTER TRIGGER имя_триггера ENABLE

Примеры триггеров

1. Триггер на вставку нового студента

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

Вместо этого имеется специальный объект SEQUENCE, который предназначен для формирования последовательных целых чисел. Для обращения к значениям последовательности в выражении SQL используются псевдостолбцы currval и nextval. Currval возвращает текущее значение. NextVal инкрементирует текущее значение и возвращает результат, при этом он становится текущим значением. Триггер на вставку берет из последовательности очередное значение и помещает его в новую строку, используя предопределенную переменную :new.

Например, создадим последовательность для формирования кодов студентов:

CREATE SEQUENCE stud_seq

Теперь создадим триггер на вставку новой строки в таблицу students:

create trigger st_keys

before insert on students

for each row

begin

select stud_seq.nextval into :new.cod_st from dual;

end;
Аналогичный триггер можно написать и на таблицу subjects, поскольку при добавлении нового предмета его код должен формироваться также автоматически.

2. Триггеры на удаление студента

Триггер на удаление должен предусмотреть перенос удаляемых данных в архивную базу данных. При создании таблиц нашей базы данных вместе с удалением студента было предусмотрено и каскадное удаление оценок студента, поэтому мы можем написать аналогичные триггеры BEFORE DELETE на таблицы students и marks, используя возможность каскадирования триггеров. При удалении студента одна единственная команда удаления, полученная сервером, например:

DELETE FROM students WHERE cod_st=125

вызовет выполнение двух команд удаления (из таблиц marks и students) вместе с двумя триггерами, сохраняющими удаляемые данные в архиве.

Предположим, что уже созданы таблицы archive_students и archive_marks. Создадим триггер на удаление из таблицы students:

---