Строковые и операторные триггеры

Процедуры и функции PL/SQL

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

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

Синтаксис объявления ПРОЦЕДУРЫ:

CREATE OR REPLACE PROCEDURE [имя процедуры] IS or AS

- Зона объявления переменных

BEGIN

- Выполняемый раздел

EXCEPTION

- Раздел исключительных  ситуаций

END [имя процедуры]

DROP PROCEDURE имя – удаление  процедуры

Запуск процедуры:

• SET SERVEROUTPUT ON EXEC TESTPRGTWO;
• SET SERVEROUTPUT ON

DECLARE

BEGIN

TESTPRGTWO;

END;

Пример процедуры с параметрами:

CREATE OR REPLACE PROCEDURE TESTPRM(NUM IN NUMBER) IS

COMP VARCHAR2(50);

BEGIN

SELECT Name INTO COMP

FROM artist

WHERE Artist.ArtistID = NUM;

DBMS^OUTPUT.enable;

DBMS_OUTPUT.put_line(in_COMP);

END TESTPRM;

IN в данном случае определяет, что параметр является входным.  Что так же справедливо и  по умолчанию.

NUM является формальным  параметром функции (formal parameters). А вот входное значение функции является фактическим параметром (actual parameters). Формальный параметр NUM, является только вместилищем для фактически передаваемого параметра и все операции производятся с формальным параметром.

 

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

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

IN OUT — этот вид представляет  собой комбинацию видов IN и  OUT. Значение фактического параметра  передается в процедуру при  ее вызове. Внутри процедуры формальный  параметр может быть считан  и в него может быть записано  значение. При завершении процедуры  и возврате управления в вызывающую  среду, содержимое формального  параметра присваивается фактическому  параметру.

Тело процедуры содержит исполняемый код и располагается  между ключевыми словами BEGIN и EXCEPTION. Далее идет блок EXCEPTION и END, в котором  располагается обработчик исключительной ситуации. Раздел объявлений располагается  между операторами CREATE и IS или AS:

Накладывать ограничения  на формальные параметры функций в PL/SQL — ЗАПРЕЩЕНО!

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

имя_параметра [тип] := | DEFAULT исходное_значение

Функция это то же, что  и процедура, она может принимать  параметры по всем тем же правилам, что и процедуры, и кроме всего  она может возвращать значения. В  функции можно применять параметры с типом OUT.

Определение функции таково:

CREATE [OR REPLACE] FUNCTION – имя функции

(аргумент [IN] [OUT] [IN OUT] тип, … ) AS [IS]

тело процедуры

RETURN (возвращаемое значение)

После того, как команда CREATE OR REPLACE создает процедуру или функцию, она сразу сохраняется в БД, в скомпилированной форме, которая  называется р-кодом (p-code). В р-коде содержатся все обработанные ссылки подпрограммы, а исходный текст преобразован, в вид удобный для чтения системой поддержки PL/SQL.

 

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

Удаление процедуры или  функции:

DROP PROCEDURE имя_процедуры

DROP FUNCTION имя_функции

Вывод информации о наличии  процедур:

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS /

Вывод текста хранимой процедуры  или функции:

SELECT * FROM USER_SOURCE WHERE NAME = ‘имя процедуры’ /

Показать ошибки:

SHOW ERRORS

Команда SHOW ERRORS, считывает  данные из системного представления USER_ERRORS.

Строковые и операторные  триггеры

Триггер БД является именованным  блоком PL/SQL и после компиляции хранится в соответствующих словарях данных БД. Триггер срабатывает при определенном событии, а именно при запуске  операций DML — INSERT, UPDATE, DELETE.

Системные триггеры срабатывают  на события самой БД.

Применение триггеров:

• Для реализации сложных ограничений целостности данных, которые невозможно осуществить через описательные ограничения, устанавливаемые при создании таблиц.
• Для организации всевозможных видов аудита. Например, слежения за изменениями в какой-либо важной таблице БД.
• Для автоматического оповещения других модулей о том, что делать в случае изменения информации содержащейся в таблице БД.
• Для реализации так называемых «бизнес правил».
• Для организации каскадных воздействий на таблицы БД.

Виды триггеров:

• Строковые (FOR EACH ROW)
• Операторные
• BEFORE – предваряющий триггер – до момента сработки одного из операторов DML — INSERT, UPDATE, DELETE.
• AFTER – завершающий триггер – после момента сработки одного из операторов DML — INSERT, UPDATE, DELETE
• INSTEAD OF – замещающий триггер

Прекращение работы триггера:

ALTER TRIGGER TESTTRG DISABLE

Удаление триггера:

DROP TRIGGER TESTTRG

Порядок срабатывания триггеров:

• Операторный предваряющий триггер BEFORE
• Для каждой строки:
• строковый предваряющий триггер BEFORE
• строковый завершающий триггер AFTER
• Операторный завершающий триггер AFTER.

Псевдозаписи триггера

Псевдозаписи триггера:

• :old
• :new

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

С ними нельзя работать полноценно как с записями, так как их нельзя присвоить записям.

Псевдозаписи работают только в строковых триггерах.

В условии псевдозаписи записываются БЕЗ ДВОЕТОЧИЯ.

• INSERT :new
• UPDATE :new, :old
• DELETE :old

Триггерные предикаты

Предикат – выражение, о котором можно сказать является оно истинным или ложным:

• INSERTING (если произошла вставка(INSERT) принимает значение true, иначе false)
• UPDATING (если произошло обновление(UPDATE) принимает значение true, иначе false)
• DELETING (если произошло удаление(DELETE) принимает значение true, иначе false)