Триггерные процедуры для событий уровня схемы и БД

Создаются с естественными ограничениями на уточнения BEFORE и AFTER. 

 

Пример триггерной процедуры, которая сразу после подключения программы к схеме ADAM переключает контекст именования на схему HR:

 

CREATE OR REPLACE TRIGGER set_hr_schema

AFTER LOGON ON adam.SCHEMA

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=hr';

END;

/

 

(Теперь ADAM сможет сразу после входа в систему обращаться к объектам схемы HR по короткому имени, без префикса HR, но зато для обращения к своим собственным объектам вынужден будет использовать префикс ADAM).

 

Пример триггерной процедуры, срабатывающей всякий раз после запуска СУБД и закрепляющей в области shared pool в SGA ряд системных пакетов для повышения средней скорости обращения к их элементам (версия 8.x):

 

CREATE OR REPLACE TRIGGER pin_code_on_startup

AFTER STARTUP ON DATABASE

DECLARE repl v$option.value%TYPE;

BEGIN

DBMS_SHARED_POOL.KEEP ('sys.STANDARD', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_STANDARD', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DUTIL', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DIANA', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_SYS_SQL', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_SQL', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_UTILITY', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_DESCRIBE', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_JOB', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_OUTPUT', 'P');

DBMS_SHARED_POOL.KEEP ('sys.PIDL ', 'P');

 

SELECT value INTO repl FROM v$option WHERE parameter = 'Advanced replication';

 

IF repl = 'TRUE' THEN

DBMS_SHARED_POOL.KEEP ('sys.DBMS_DEFER', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_REPUTIL', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_SNAPSHOT', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_REFRESH', 'P');

DBMS_SHARED_POOL.KEEP ('sys.DBMS_DEFER_INTERNAL_SYS', 'P');

DBMS_SHARED_POOL.KEEP ('sys.REP$WHAT_AM_I', 'P');

END IF;

EXCEPTION WHEN OTHERS THEN NULL;

END;

/

 

 

Пакеты в PL/SQL

Пакет – способ группировки программных объектов PL/SQL. Некоторые мотивировки использования пакетов:

 

- возможность сокрытия информации

- объектный стиль программирования

- проектирование от общего к частному

- возможность независимой трансляции взаимозависимых программных единиц (пакетов)

- возможность сохранения переменных от транзакции к транзакции

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

 

Элементы, из которых состоит пакет:

 

- процедуры

- функции

- константы

- переменные

- курсоры

- имена исключительных состояний

- предложения TYPE описания типов

 

 

Общая структура пакета

Две основные компоненты пакета:

 

n внешнее описание

n тело (реализация)

 

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

 

CREATE [OR REPLACE] PACKAGE имя _пакета

{IS | AS}

[AUTHID {CURRENT_USER | DEFINER}]

[PRAGMA SERIALLY_REUSABLE;]

           [ определения общедоступных типов,

           объявления общедоступных переменных, объектов,

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

END имя_пакета;

 

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

 

CREATE [OR REPLACE] PACKAGE BODY имя _пакета

{IS | AS}

[PRAGMA SERIALLY_REUSABLE;]

           [ определения закрытых типов,

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

           определения закрытых курсоров, процедур и функций

полные определения общедоступных курсоров, процедур и функций ]

[BEGIN

           программный_код

[EXCEPTION

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

END имя_пакета;

 

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

 

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

 

Полный пример создания пакета:

 

CREATE OR REPLACE PACKAGE time_package IS

           FUNCTION gettimestamp RETURN DATE;

 

           PROCEDURE resettimestamp;

END time_package;

/

 

CREATE OR REPLACE PACKAGE BODY time_package IS

           starttimestamp DATE:= SYSDATE;         -- скрытая переменная пакета

 

           FUNCTION gettimestamp RETURN DATE IS

           BEGIN

                           RETURN starttimestamp;

           END gettimestamp;

 

           PROCEDURE resettimestamp IS

           BEGIN

                           starttimestamp:= SYSDATE;

           END resettimestamp;

END time_package;

/

 

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

 

CREATE OR REPLACE PACKAGE time_package1 IS

           starttimestamp DATE:= SYSDATE;         -- общедоступная переменная пакета

END time_package1;

/

 

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