Язык PL/SQL

[спецификация локальных элементов пакета]

блоки PL/SQL реализации процедур и функций, объявленных в спецификации

блоки PL/SQL локальных процедур и функций

[BEGIN секция инициализации пакета]

END;

В спецификации пакета находится описание следующих программных элементов, доступных из других программ PL/SQL (то есть элементов, видимых извне):

пользовательские типы данных;

пользовательские исключения;

процедуры и функции;

переменные;

константы;

курсоры.

Эти программные элементы называются глобальными пакетными переменными, глобальными пакетными курсорами и т. п.

Для процедур и функций в спецификации пакета присутствуют только заголовки – названия процедур и функций и описания их параметров. В спецификации пакета нет блоков PL/SQL, реализующих логику процедур и функций, вся она находится в теле пакета. Можно считать, что спецификация пакета является интерфейсной частью – аналогом заголовочных файлов (header files), имеющихся, например, в языке программирования C++.

В теле пакета могут быть объявлены все те же виды программных элементов, что и в спецификации пакета с той лишь разницей, что они не будут доступны из других программ PL/SQL (не видны извне тела пакета). Эти элементы называются локальными пакетными переменными, локальными пакетными процедурами и т. п.

Локальные программные элементы предназначены исключительно для использования только процедурами и функциями самого пакета. Тем самым в PL/SQL реализовано сокрытие, то есть принцип проектирования программного обеспечения, заключающийся в разграничении доступа различных программ к внутренним компонентам друг друга. Подчеркнем, разграничивается доступ именно к внутренним компонентам.

Покажем области видимости объявленных в пакетах переменных и программ:

CREATE OR REPLACE PACKAGE pkg1 AS

– g_var1 – глобальная пакетная переменная

– видна и в теле пакета и снаружи (причем может изменяться снаружи)

g_var1 INTEGER;

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

PROCEDURE proc1;

END;

CREATE OR REPLACE PACKAGE BODY pkg1 AS

– локальная переменная, видна внутри тела пакета, снаружи не видна

l_var2 DATE;

– локальная функция, видна внутри тела пакета, снаружи не видна

FUNCTION function1 RETURN VARCHAR2 IS

BEGIN

RETURN TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS');

END;

– реализация в теле логики процедуры proc1, объявленной в спецификации

PROCEDURE proc1 IS

– l_p_var3 – локальная переменная процедуры proc1

– видна только внутри процедуры proc1

l_p_var3 VARCHAR2(2000);

BEGIN

l_p_var3 := function1||' '||to_char(l_var2)||to_char(g_var1);

END;

END;

Достоинства использования пакетов

Сформулируем достоинства использования пакетов PL/SQL при разработке серверной бизнес-логики:

упрощение сопровождения и расширения программ PL/SQL, так как пакеты обеспечивают инкапсуляцию кода и позволяют группировать логически связанные процедуры и функции;

разграничение доступа различных пакетов к внутренним компонентам друг друга;

сохранение данных сессии пользователя в глобальных пакетных переменных, в том числе повышение производительности приложений за счет кэширования постоянно использующихся в программах PL/SQL данных, например, справочников (данные кэшируются в коллекциях-глобальных переменных пакетов);

исключение жестко кодируемых литералов (hard-coded literals).

Хороший стиль программирования на PL/SQL предусматривает даже для небольших проектов наличие спецификаций пакетов, в которых объявлены

все пользовательские типы данных;

все константы и переменные, которые инициализируются жестко кодируемыми литералами, в том числе магическими числами;

все SQL-запросы в виде объявлений явных курсоров;

все пользовательские исключения.

Настоятельно рекомендуется все объявления программных элементов такого рода всех программ PL/SQL, реализующих серверную бизнес-логику системы, собрать в одной или нескольких спецификациях пакетов, а не «размазывать» объявления типов, исключений, констант и т. п. по всему коду или переписывать одну и ту же команду SELECT INTO в нескольких местах. Иногда даже создают отдельные спецификации пакетов только для объявлений типов, переменных, исключений и курсоров без объявлений процедур и функций. Для таких спецификаций изначально не планируется создавать тела пакетов.

Ни в коем случае не следует расставлять по всему исходному коду PL/SQL жестко кодируемые литералы. Например, если в коде в сорока местах для вычисления сумм «чистыми» использовать выражения вида (…)*0.87, то когда ставка подоходного налога перестанет быть равной 13%, надо будет найти все сорок мест и заменить 0.87 на новое значение. А самое интересное начнется, если почти везде по коду поменять значение литерала на новое, а где-то забыть и оставить старое. Чтобы не заниматься всем этим, правильно один раз объявить в спецификации пакета константу

g_c_tax_percent INTEGER := 13;

и во всем коде в дальнейшем использовать только ее. Если потребуется внести изменения, то новое число нужно будет указать только в одном месте – в значении константы.

Отношения между спецификацией и телом пакета

Отношения между спецификацией и телом пакета описываются следующим образом:

сначала создается спецификация пакета, затем его тело;

тело пакета не может существовать без спецификации и даже не создастся DDL-командой CREATE PACKAGE BODY с выдачей сообщения об ошибке;

спецификация пакета без тела существовать может, на объявленные в ней глобальные пакетные процедуры и функции можно ссылаться из других программ PL/SQL (ошибка обращения к такому бестелесному пакету возникнет только на этапе выполнения);

при перекомпиляции спецификации пакета автоматически перекомпилируется его тело, при перекомпиляции тела пакета его спецификация не перекомпилируется;

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

Эти отношения между спецификацией и телом пакета имеют следующие положительные аспекты, которые особенно полезны при большом объеме кода на PL/SQL в крупных проектах:

возможность создания на этапе прототипирования «заглушек» – пакетов без тел, реализация объявленных в их спецификациях процедур и функций будет осуществлена позже;

при перекомпиляции тела пакета с зависящими от его спецификации другими программами на PL/SQL ничего не происходит.

Перегружаемые программы

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

– статус клиента на текущую дату

FUNCTION getClientStatus(p_client_id IN INTEGER) RETURN VARCHAR2

– статус клиента на дату-параметр

FUNCTION getClientStatus(p_client_id IN INTEGER,

p_status_date IN DATE) RETURN VARCHAR2

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

FUNCTION getClientStatus(p_client_id IN INTEGER) IS

BEGIN

RETURN getClientStatus(p_client_id => p_client_id,

p_status_date => SYSDATE);

END;

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

print(p_person t_person), print(p_passport t_passport), …

Создание пакетов

Спецификацию пакета принято начинать с объявления пользовательских типов данных (подтипов имеющихся в языке скалярных типов, записей PL/SQL, коллекций), потом обычно объявляются курсоры, затем константы и переменные (в том числе и пользовательских типов, объявленных в спецификации выше). После всех этих объявлений помещают заголовки глобальных пакетных процедур и функций.

Создадим спецификации двух пакетов. В спецификации package1 сделаем объявления, которые потом будем использовать в теле пакета package2.

CREATE TABLE tab1(at1 NUMBER, at2 DATE);

SQL> CREATE OR REPLACE PACKAGE package1 AS

2

3 at1_treshold CONSTANT NUMBER := 10;

4

5 at1_treshold_excess EXCEPTION;

6

7 CURSOR c_tab1_count IS SELECT COUNT(*) c FROM tab1;

8

9 END;

10 /

Package created.

SQL> CREATE OR REPLACE PACKAGE package2 AS

2

3 PROCEDURE tab1_insert(p_at1 IN tab1.at1%TYPE,p_at2 IN tab1.at2%TYPE);

4

5 FUNCTION tab1_count RETURN INTEGER;

6

7 END;

8 /

Package created.

Реализуем в теле пакета package2 процедуру tab1_insert добавления строк в таблицу tab1 и функцию tab1_count, возвращающую число строк в таблице.

SQL> CREATE OR REPLACE PACKAGE BODY package2 AS

2

3 PROCEDURE check_at1(p_at1 IN tab1.at1%TYPE) IS

4 BEGIN

5 IF p_at1 > package1.at1_treshold THEN

6 RAISE package1.at1_treshold_excess;

7 END IF;

8 END;

9

10 PROCEDURE tab1_insert(p_at1 IN tab1.at1%TYPE,

11 p_at2 IN tab1.at2%TYPE) IS

12 BEGIN

13 check_at1(p_at1);

14 INSERT INTO tab1 VALUES(p_at1,p_at2);

15 END;

16

17 FUNCTION tab1_count RETURN INTEGER IS

18 result INTEGER;

19 BEGIN

20 OPEN package1.c_tab1_count;

21 FETCH package1.c_tab1_count INTO result;

22 CLOSE package1.c_tab1_count;

23 RETURN result;

24 END;

25

26 END;

27 /

Package body created.

В теле пакета package2 используются константа, пользовательское исключение и явный курсор, объявленные в спецификации пакета package1.

Для обращения к глобальным пакетным программным элементам нужно указывать перед их именами имя пакета.

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE(package1.at1_treshold);

3 END;

4 /

10

PL/SQL procedure successfully completed.

Локальные процедуры, функции, переменные и другие программные конструкции не видны вне тела пакета (снаружи).

Приведем соответствующий пример:

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE(package2.check_at1(15));

3 END;

4 /

DBMS_OUTPUT.PUT_LINE(package2.check_at1(15));

*

ERROR at line 2:

ORA-06550: line 2, column 33:

PLS-00302: component 'CHECK_AT1' must be declared

ORA-06550: line 2, column 3:

PL/SQL: Statement ignored

Вызовем функцию созданного пакета:

SQL> BEGIN

2 package2.tab1_insert(1,'A');

3 DBMS_OUTPUT.PUT_LINE('Rows in tab1 – '||package2.tab1_count());

4 END;

5 /

Rows in tab1 – 1

PL/SQL procedure successfully completed.

SQL> BEGIN

2 package2.tab1_insert(20,'A');

3 END;

4 /

BEGIN

*

ERROR at line 1:

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at "U1.PACKAGE2", line 6

ORA-06512: at "U1.PACKAGE2", line 13

ORA-06512: at line 2

При втором вызове было инициировано пользовательское исключение at1_treshold_excess, объявленное в спецификации пакета package1.

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

Состоянием пакета (package state) называется совокупность текущих значений его глобальных и локальных переменных и констант, а также текущих состояний курсоров (курсор открыт или закрыт, а также значения атрибутов курсора, отражающие в том числе сколько строк было считано из открытого курсора к настоящему времени).

Состояния пакетов сохраняется в течение всей жизни сессии пользователя. Очень важно то, что состояния пакетов у каждой сессии пользователя свое. Нельзя «подсмотреть» или «подправить» значения пакетных переменных или считать строку курсора из состояния пакета другой сессии. Во многих приложениях на этом построены специфические системы управления доступом.

Пакетные переменные используются для решения следующих задач:

кэширование постоянно использующихся в программах PL/SQL данных, например, справочников;

обмен данным между программами PL/SQL в сессии.

В определенном смысле состояние пакетов – это аналог реестра Windows, в который программы для Windows записывают свои параметры в виде пар «параметр-значение». Эти значения параметров из реестра Windows обычно используются во время работы программы, сохраняются при завершении ее работы и считываются программой при следующем ее запуске. В этом сравнении отличие пакетов Oracle от реестра Windows в том, что состояние пакетов для завершившихся сессий пользователя теряется, то есть их можно рассматривать как «временный реестр» (реестр на время сессии). Состояние пакета используется им же при повторном вызове его процедур и функций во время одной сессии или используется другими программами 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++, работающих на серверах приложений или прямо на серверах баз данных. В то же время сделать так, чтобы при наступлении событий с данными гарантированно происходили одни и те же сопровождающие действия, можно только с помощью триггеров.

Дело в том, что с базой данных могут работать несколько приложений, в которых сопровождающие действия с данными, вообще говоря, могут быть реализованы по-разному. Кроме того, изменения в данных могут вноситься и выполнением предложений 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/SQL – we 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 them – ever – unless 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 имеется три вида таких событий: