Иван Задворьев - Язык 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));