Разработка серверного кода

Создание пакетов 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;