Создание пакетов PL/SQL
Процедуры, функции и глобальные переменные, объединенные общим функциональным замыслом, часто оформляются в виде специального объекта базы данных - пакета. Прием оформления родственных программ в пакет хорошо известен из программистской практики. Особенностью PL/SQL является раздельная компиляция и хранение интерфейсной и исполнительной частей пакета. Таким образом, пакет (package) есть объект базы данных, который группирует логически связанные типы, объекты, процедуры и функции PL/SQL. Пакет состоит из двух частей: спецификации пакета и тела пакета, хотя тело пакета иногда опускается. Спецификация пакета есть интерфейс для вашего приложения. В ней хранится описание процедур, функций, глобальных переменных, констант и курсоров, которые доступны из внешних приложений. Тело пакета полностью определяет курсоры, процедуры и функции, являясь, таким образом, реализацией спецификации. В теле пакета могут также быть определены переменные, курсоры, процедуры и функции. Они являются локальн ыми, т.е. не доступными из внешних приложений.
Синтаксис определения пакета в базе данных состоит также из двух частей. Оператор создания интерфейсной части имеет следующий синтаксис:
CREATE [OR REPLACE] PACKAGE [имя схемы].имя AS Определения типов и объектов Спецификации процедур и функций END [имя];
Ключевое слово OR REPLACE указывает на безусловное замещение предыдущего кода спецификации пакета. Если оно не указано, а пакет определен в базе данных, то замещения старого значения спецификации пакета не происходит и возвращается сообщение об ошибке.
Спецификация пакета начинается с объявления констант и переменных, при этом ключевое слово DECLARE не используется. Рассмотрим пример создания спецификации пакета.
Пример
CREATE OR REPLACE PACKAGE paket1 AS A1 CONSTANT number:= 1.3; PROCEDURE Pr1; FUNCTION F01 (x1 real) RETURN real; END; /
Оператор создания тела пакета имеет следующий синтаксис:
CREATE [OR REPLACE] PACKAGE BODY [имя схемы].имя AS Объявления локальных типов и переменных Тела процедур и функций [BEGIN команды инициализации END [имя];
Ключевое слово OR REPLACE указывает на безусловное замещение предыдущего кода тела пакета. Если оно не указано, а пакет определен в базе данных, то замещения старого значения тела пакета не происходит и возвращается сообщение об ошибке.
Рассмотрим пример создания тела пакета для спецификации, определенной в предыдущем примере.
Пример. Пусть функция пакета F01 умножает аргумент на заданное число, а процедура Pr1 фиксирует факт обращения к функции пакета в таблице базы данных. Предполагается, что таблица T01 уже существует.
CREATE OR REPLACE PACKAGE BODY paket1 AS Cnt number:=0; FUNCTION F01(x1 real) RETURN real IS BEGIN Pr1; RETURN x1*A1; END; PROCEDURE Pr1 IS BEGIN Cnt:=Cnt+1; INSERT INTO T01 VALUES(Cnt,SYSDATE); COMMIT; END; END; /
Отметим, что инициализация локальных переменных, таких как переменная Cnt примера, происходит при запуске сервера СУБД Oracle.
Константа или переменная, определенная в спецификации пакета, может быть доступна из пользовательской программы, при условии, если такой доступ разрешен администратором базы данных. Чтобы обратиться к глобальной переменной или константе пакета, нужно использовать квалифицируемое имя, указав в качестве префикса к имени переменной или константы имя пакета. Аналогично происходит обращение к процедурам или функциям пакета, определенных в спецификации пакета. Например,
package_name.type_name package_name.object_name package_name.subprogram_name
Чтобы уничтожить пакет для освобождения ресурсов сервера, используется команда SQL DROP в следующем формате:
DROP PACKAGE [BODY] [имя схемы].имя пакета;
Необязательное ключевое слово BODY указывает, что уничтожается только тело пакета, в противном случае удаляется и спецификация, и тело пакета.
Что дает проектировщику базы данных использование пакетов? Пакеты обладают рядом преимуществ, к которым принято относить следующие:
- Модульность. Пакеты позволяют проектировщику базы данных инкапсулировать логически связанные типы данных, объекты, процедуры и функции в поименованный модуль PL/SQL.
Использование пакетов позволяет проектировщику базы данных сделать приложения проще, яснее и хорошо определенными. Поскольку каждый пакет в отдельности проще для понимания, связи между пакетами через хорошо определенные интерфейсы яснее и проще. - Упрощение проектирования приложений базы данных. Когда проектировщик базы данных проектирует приложение, ему в случае использования пакетов необходимо сначала спроектировать только интерфейсную часть пакетов, т.е. определить спецификации пакета, а только потом разрабатывать тело пакета. Поскольку компиляция спецификации и тела пакета происходит раздельно, то нет необходимости полностью определять тело пакета до тех пор, пока проектировщик базы данных не завершит проектирования приложения базы данных.
- Скрытие информации. При разработке пакета проектировщик базы данных может определить, какие типы, объекты, процедуры и функции будут доступны внешним приложениям, а какие будут локальными в пакете, т.е. скрытыми от внешних программ. Этот прием упрощает сопровождение и расширение приложений базы данных в дальнейшем. В частности, скрытие деталей реализации пакета от пользователей защищает целостность пакета.
- Дополнительная функциональность. Глобальные переменные и курсоры пакета сохраняются в течение сессии работы пользователя с базой данных. Поэтому они могут разделяться всеми процедурами и функциями, которые могут выполняться в программной среде сессии. Они также позволяют сопровождать некоторые данные транзакции без сохранения их в базе данных.
- Улучшение производительности базы данных. Когда элементы пакета вызываются в первый раз, пакет полностью загружается в оперативную память сервера. Следовательно, последующие вызовы процедур и функций пакета будут происходить без обращения к диску, что сократит число операций ввода/вывода.
Рассмотрим для нашей учебной базы данных пример принятия решений проектировщиком базы данных о создании пакета.
Предположим, что в организации высокая степень текучести кадров. Пользователь базы данных, который работает с персоналом, проводит одну сессию работы с базой данных в начале недели.
В рамках этой сессии он фиксирует в базе данных новых сотрудников, которые приступают к работе с понедельника (бизнес-правило организации - принимать на работу только с понедельника), и удаляет информацию о сотрудниках из базы данных (бизнес-правило организации - не сохранять информацию об уволенном сотруднике в базе данных), которые ушли из организации. Тогда проектировщик базы данных может рассмотреть создание пакета, содержащего две процедуры: одну для занесения информации о сотруднике в базу данных, а другую - для удаления информации о сотруднике из базы данных. Код пакета может быть таким, как показано ниже.
CREATE PACKAGE emp_actions AS TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL); CURSOR desc_salary RETURN EmpRecTyp; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions;
CREATE PACKAGE BODY emp_actions AS CURSOR desc_salary RETURN EmpRecTyp IS SELECT empno, sal FROM employee ORDER BY sal DESC; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN INSERT INTO employee VALUES (empno, ename, job, mgr, SYSDATE, sal, comm, deptno); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM employee WHERE empno = emp_id; END fire_employee; END emp_actions;
