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++, работающих на серверах приложений или прямо на серверах баз данных. В то же время сделать так, чтобы при наступлении событий с данными гарантированно происходили одни и те же сопровождающие действия, можно только с помощью триггеров.
Дело в том, что с базой данных могут работать несколько приложений, в которых сопровождающие действия с данными, вообще говоря, могут быть реализованы по-разному. Кроме того, изменения в данных могут вноситься и выполнением предложений SQL в SQL*Plus или Quest SQL Navigator, при этом нет никакой гарантии, что необходимые сопровождающие действия будут выполнены правильно и будут выполнены вообще. Триггеры же «вешаются» на операции с данными и работают как часы, вне зависимости от того, кто и из какого приложения эти операции выполнил.
Назначение триггеров
Триггеры используются для решения следующих задач:
реализация серверной бизнес-логики в рамках концепции активных баз данных;
реализация динамических ограничений целостности;
ведение журналов аудита действий с данными;
автоматизация администрирования баз данных.
Триггерыважнейший механизм для так называемых активных баз данных, которые являются не пассивными системами хранения, а активно реагируют на изменения в данных путем генерации различных событий и их обработки.
В литературе приводятся самые разные примеры таких событий и реагирования на них. Это может быть простая серверная бизнес-логика, когда после добавления данных о платеже триггер на это событие увеличивает на внесенную сумму баланс соответствующего лицевого счета. Это может быть и реализация весьма изощренной бизнес-логики вида «Если клиент два раза подряд не дозвонился в наш call-центр, то в качестве компенсации для повышения лояльности следует на его лицевой счет начислить 100 бонусных баллов и отправить ему сообщение об этом в мессенджере WhatsApp». В этом случае реализация бизнес-логики осуществляется в триггере на добавление строк в таблицу логов звонков в call-центр.
Помимо реализации бизнес-логики в рамках концепции активных баз данных, триггеры используются для выполнения всевозможных проверок допустимости действий над данными в таблицах (сюда относится и реализация динамических ограничений целостности), проверок правомерности создания объектов баз данных, предоставления привилегий и т. п.
Отношение к триггерам в среде специалистов по обработке данных двоякое и к тому же меняется со временем. Есть те, кто на дух не переносит триггеры, и не готов даже обсуждать возможность их использования в своих проектах. Как правило, это разработчики приложений, требующие, чтобы за все аспекты работы с данными отвечали клиентские приложения, в том числе и за активную реакцию на события, которые происходят с данными. Некоторые авторитетные специалисты в области технологий Oracle на основе опыта своей работы с течением времени пришли к выводу, что триггеры, как правило, используются неправильно и являются одним из основных источников ошибок. По их мнению, триггеры вызывают побочные эффекты, о триггерах забывают, что приводит к ненужным неожиданностям, триггеры реализуют ограничения целостности, и в то же время часто не включаются в состав объектов, для которых формируются DDL-команды при извлечении схемы базы данных и т. д.
В марте 2007 года Томас Кайт написал в своем блоге.
Things I don't like:
generic implementations that are not necessary;
triggers;
WHEN OTHERS (not followed by RAISE!!);
triggers;
not using bind variables;
triggers.
Мы просто оставим это здесь без перевода и комментариев:
I hate triggers, i hate autonomous transactions, i hate WHEN OTHERS. If we removed those three things from PL/SQLwe would solve 90% of all application bugs, i think No kidding
Moral to this story however is:
avoid triggers unless you absolutely need them (and you hardly ever do);
do nothing, that doesn't rollback in themeverunless you can live with the side effects (triggers can always fire more than once!);
autonomous transactions in triggers are pure evil.
При этом Т. Кайт пишет, что в начале своей Oracle-карьеры считал триггеры хорошим инструментом и активно их использовал. И не он один поступал таким образом. Как следствие, триггеры есть в многих системах, которые еще десятилетия будут эксплуатироваться. Поэтому любой специалист по технологиям Oracle должен уметь разбираться в этой теме.
Виды событий для срабатывания триггеров
Долгое время имевшиеся в базах данных Oracle триггеры срабатывали только на добавление, удаление или изменение данных в таблицах. Постепенно перечень видов событий, на которые можно «навесить» триггер, расширялся и в версии Oracle 12c имеется три вида таких событий:
выполнение DML-предложений SQL добавления, изменения и удаления данных таблиц в таблицахINSERT, UPDATE, DELETE;
выполнение DDL-команд (команд создания, изменения и удаления объектов базы данныхCREATE, ALTER, DROP и некоторых других);
события уровня базы данных (запуск и остановка базы данных, возникновение системных ошибок и т. п.).
Для реализации серверной бизнес-логики и динамических ограничений целостности обычно используются триггеры, срабатывающие на выполнение предложений INSERT, UPDATE, DELETE. Триггеры для остальных двух видов событий, как правило, используются администраторами баз данных для решения задач администрирования.