Иван Задворьев - Язык PL/SQL

присваивание переменным и константам значений, указанных в их объявлениях;

выполнение кода секции инициализации пакета, представляющей собой анонимный блок в самом конце тела пакета.

Обычно в секции инициализации как раз и происходит размещение в пакетных переменных часто используемых в коде PL/SQL данных, например, справочников.

Справочники хранятся в таблицах базы данных и для часто выполняющихся операций кодирования и раскодирования (получения по термину кода и наоборот) каждый раз приходится выполнять SQL-запросы к этим таблицам. Это приводит к заметным затратам системных ресурсов, в том числе из-за частых переключений контекста: … – SQL – PL/SQL – SQL – PL/SQL – ….

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

Именно в этом и заключается одно из отмеченных выше достоинств использования пакетов – повышение производительности приложений за счет кэширования постоянно использующихся в приложении данных.

Приведем пример кэширования справочника кодов валют.

CREATE TABLE crcy_dictionary (crcy_code INTEGER,crcy_name VARCHAR2(100));

INSERT INTO crcy_dictionary VALUES(840,'Доллар США');

INSERT INTO crcy_dictionary VALUES(643,'Российский рубль');

INSERT INTO crcy_dictionary VALUES(978,'Евро');

INSERT INTO crcy_dictionary VALUES(986,'Бразильский реал');

CREATE OR REPLACE PACKAGE pack_dict_decode AS

FUNCTION get_сrcy_name(p_crcy_code in INTEGER)

RETURN crcy_dictionary.crcy_name%TYPE;

END;

CREATE OR REPLACE PACKAGE BODY pack_dict_decode AS

TYPE t_crcy_dict IS

TABLE OF crcy_dictionary.crcy_name%TYPE INDEX BY PLS_INTEGER;

CURSOR c_crcy_dictionary IS SELECT * FROM crcy_dictionary;

g_crcy_dict t_crcy_dict;

FUNCTION get_сrcy_name(p_crcy_code in INTEGER)

RETURN crcy_dictionary.crcy_name%TYPE IS

ret crcy_dictionary.crcy_name%TYPE;

BEGIN

IF g_crcy_dict.EXISTS(p_crcy_code) THEN

ret := g_crcy_dict(p_crcy_code);

ELSE

ret := 'Не определен';

END IF;

RETURN ret;

END;

PROCEDURE crcy_dict_ini IS

TYPE t_crcy_dict_row_tab IS TABLE OF crcy_dictionary%ROWTYPE;

l_crcy_dict_row_tab t_crcy_dict_row_tab;

BEGIN

OPEN c_crcy_dictionary;

FETCH c_crcy_dictionary BULK COLLECT INTO l_crcy_dict_row_tab;

CLOSE c_crcy_dictionary;

FOR i IN 1..l_crcy_dict_row_tab.COUNT LOOP

g_crcy_dict(l_crcy_dict_row_tab(i).crcy_code) :=

l_crcy_dict_row_tab(i).crcy_name;

END LOOP;

END;

– секция инициализации пакета

BEGIN

crcy_dict_ini();

END;

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE('643:'||pack_dict_decode.get_сrcy_name(643));

3 DBMS_OUTPUT.PUT_LINE('771:'||pack_dict_decode.get_сrcy_name(771));

4 END;

5 /

643: Российский рубль

771: Не определен

PL/SQL procedure successfully completed.

Сброс состояния пакетов

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

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

Для сброса состояния пакетов предназначена процедура RESET_PACKAGE встроенного пакета DBMS_SESSION.

Пусть пакет package1 имеет пакетную переменную v1.

SQL> BEGIN

2 package1.v1:='A';

3 DBMS_OUTPUT.PUT_LINE(package1.v1);

4 END;

5 /

A

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_SESSION.RESET_PACKAGE;

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE(NVL(package1.v1,'v1 имеет значение NULL'));

3 END;

4 /

v1 имеет значение NULL

PL/SQL procedure successfully completed.

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

Удаление и изменение пакетов

Для удаления спецификации пакета и его тела используются следующие DDL-команды:

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

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

Удалим спецификацию нашего пакета package1:

SQL> DROP PACKAGE package1;

Package dropped.

Напомним, что при удалении спецификации пакета автоматически удаляется его тело. Кроме создания и удаления, спецификации и тела пакетов можно изменять DDL-командами

ALTER PACKAGE имя_пакета COMPILE [DEBUG]

ALTER PACKAGE BODY имя_пакета COMPILE [DEBUG]

При выполнении DDL-команд ALTER происходит перекомпиляция байт-кодов по хранящемся в базе данных исходным текстам.

Триггер базы данных – это хранимая в базе данных программа, которая автоматически запускается при наступлении событий, указанных при создании триггера. В книгах на английском языке часто встречается выражение «triggers fires», то есть триггеры «зажигаются».

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