Работа с Oracle 5: Пакеты



В этой статье я решил показать пример использования пакетов. Я выбрал данную тему не случайно, так как это оказался довольно таки актуальный вопрос, ну если конечно ты реально занимаешься программированием, а именно, созданием систем на платформе Oracle. Но даже если ты этого не делаешь, то занявшись данным видом деятельности ты столкнёшься с одной небольшой проблемой - компиляция процедур и функций хранимых на сервере.

Дело в том, что хранимые на сервере процедуры и функции (и некоторые другие объекты Oracle) периодически требуют перекомпиляции, происходит это потому что в случае изменения структуры объекта, например решил ты подправить некоторую процедуру в следствие наличия ошибки в ней (о как загнул), остальные объекты зависящие от данного (в другой процедуре используется редактируемая тобой процедура) примут состояние INVALID и прежде чем их использовать необходимо будет перекомпилировать все зависимые объекты.

Говоря по простому, отредактировав одну процедуру нужно будет её, и все зависящие от неё процедуры и функции (ну возможно и не все) откомпилировать. Данная проблема проявит себя в приложении сообщением об ошибке (текст я точно не помню). Соответственно приложение откажется работать пока ты не откомпилируешь все необходимые объекты.
А теперь представь, что работаешь ты над большим проектом (в одиночку, как это чаще всего в жизни и происходит), в котором участвует порядка сотни процедур, функций и всякой другой требухи, и вдруг ты отлавливаешь ошибку в одной из процедур (ну или функций), ты её редактируешь, и вот тут все процедуры и функции использующие данную переходят в состояние INVALID, и их соответственно необходимо перекомпилировать.

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

Так вот, избежать данных проблем можно двумя способами:
1. Перекомпиляция всех объектов конкретной схемы с помощью процедуры из встроенного пакета DBMS_UTILITY выполнением следующей команды:
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema_name)
где schema_name - это имя схемы, объекты которой необходимо перекомпилировать.
2. Второй способ предусматривает использование так называемых пакетов, о которых речь пойдёт дальше.

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

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

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

Кстати, хочу привести ещё один аргумент в пользу пакетов - все встроенные функции, процедуры и т.д. сервера Oracle представлены в виде логически разделённых пакетов. Ну надеюсь ситуация более менее ясная и мы можем приступать к практике.

В нашем примере мы будем использовать таблицу EMP учебной схемы SCOTT, которая автоматически создаётся при создании базы данных. Структура данной таблицы такова:

анная таблица отображает данные о сотрудниках фирмы, и содержит следующие столбцы:

EMPNO - ключевое поле
ENAME - фамилия сотрудника
JOB - должность
MGR - фиг его знает что за столбец, но нам и не важно :)
HIREDATE - дата рождения
SAL - оклад
COMM - комиссионные
DEPTNO - номер отдела, тоже нам не интересен

Задача буде такая: создать процедуру изменяющую оклад на указанное значение и запихать эту процедуру в пакет, вызвав её соответственно из этого пакета.
Во-первых напишем процедуру, что бы освежить в памяти синтаксис написания процедур (повторение мать учения):

CREATE OR REPLACE
PROCEDURE Sal
(psal_old in emp.sal%type, psal_new in emp.sal%type)
AS
BEGIN
UPDATE Emp
Set Sal=psal_new
Where Sal=psal_old;
END Sal;

Данная процедура изменяет указанный оклад (столбец SAL таблицы EMP) на выбранное значение. Psal_old и Psal_new - входящие параметры процедуры (in) соответственно изменяемый оклад и размер нового оклада. Структура %type указывает на то что тип данных этих переменных соответствует типу данных указанного столбца таблицы (emp.sal).

Теперь необходимо создать пакет и "запихнуть" в него процедуру. Для начала создадим заголовок пакета (спецификация):

CREATE OR REPLACE PACKAGE Employees
IS
PROCEDURE Sal(psal_old in emp.sal%type, psal_new in emp.sal%type);
END Employees;

Ну здесь я думаю всё понятно, мы создали заголовок и указали какая процедура входит в пакет, в будущем его возможно расширять благодаря конструкции CREATE OR REPLACE (создать или заменить).

Теперь создадим тело пакета:

CREATE OR REPLACE PACKAGE BODY Employees
IS
PROCEDURE Sal(psal_old in emp.sal%type, psal_new in emp.sal%type)
AS
BEGIN
UPDATE Emp
Set Sal=psal_new
Where Sal=psal_old;
END Sal;
END Employees;

Здесь всё аналогично заголовку, особенность только в том что процедура объявляется просто PROCEDURE без CREATE. Ну и в процедуре соответственно происходит обновление таблицы EMP (UPDATE), и замена всех значений столбца Sal (psal_old) на указанное значение переменной psal_new. Всё, операции с базой закончены можно приступать к проектированию приложения. Здесь отмечу только то что для написания программы я использовал Delphi 7.0, базы данных - сервер Oracle версии 9.2

Как обычно, для выполнения написанного ранее кода PL/SQL используем утилиту SQL Plus:

осле выполнения данного кода запускаем Delphi. Создаём новое приложение. Как и раньше, для реализации наших идей воспользуемся технологией BDE корпорации Borland как наиболее доступной (входит по умолчанию). Хотя честно говоря это не лучший вариант, сам я сейчас потихоньку перехожу на Direct Oracle Access - отличный набор компонентов для доступа к базам данных Oracle из Delphi или C++Builder (смотрите здесь: ]]>http://www.allroundautomations.com]]>). О недостатках BDE я сейчас упоминать не буду, дабы не отвлекать от основного вопроса, но в следующих статьях надо будет обязательно поговорить о способах поставки приложения и выбора способа реализации.

Итак, создали приложение, переходим на закладку BDE и добавляем на форму компонент DataBase. Делаем двойной щелчок на компоненте и открывается следующее окно:

Здесь в поле Name пишем имя базы которое будем указывать в других компонентах, выбираем Driver name - ORACLE, убираем галки Login promt (отменяет запрос пользователя и пароля при соединении с базой, во время проектирования нам это не надо), Keep inactive connection (сохраняет постоянное соединение с бд). Нажимаем кнопку Defaults и удаляем все ненужные строки кроме указанных на рисунке, соответственно указав имя сервера (вашей бд, у меня manuf), имя пользователя - SCOTT, пароль - TIGER (по умолчанию).

Затем добавляем на форму компонент Table, в свойстве DatabaseName выбираем имя указанное в свойстве Name компонента Database, у меня manuf. Выбираем имя таблицы - свойство TableName - EMP. Затем делаем двойной щелчок по Table'у - открывается редактор полей, нажимаем правую кнопку мышки и выбираем Add all fields.

После добавления всех полей установим свойство для полей DisplayLabel:

ENAME - Сотрудник
JOB - Должность
HIREDATE - Дата рождения
SAL - Оклад
COMM - Комиссионные

Соответственно ненужные поля уберём установив свойство Visible в False для EMPNO, MGR, DEPTNO. Да, ещё необходимо для поля EMPNO в свойстве ProviderFlags установить True для подсвойства pflnKey, указав таким образом программе что это ключевое поле.

Затем добавляем на форму DataSource с закладки Data Access, установив в свойстве DataSet - Table1. После этого можно добавить на форму DBGrid для отображения данных таблицы и в свойстве грида DataSource указать DataSource1. Сразу хочу заметить что я не менял имена добавляемых компонентов - Table, DBGrid и т.д. и оставлял их по умолчанию, но желательно давать им осмысленные имена, так как при разрастании проекта будет сложно ориентироваться в компонентах, какой из них с какой таблицей связан. Это так называемые правила хорошего тона :).

Сразу пропишем программное подключение к базе и отключение от неё - в событии формы OnCreate пропишем следующий код:

procedure TForm1.FormCreate(Sender: TObject);
begin
Database1.Connected:=true;
Table1.Open;
end;

в событии OnDestroy:

procedure TForm1.FormDestroy(Sender: TObject);
begin
Database1.Connected:=false;
end;

Таким образом мы открываем соединение с базой при запуске приложения и закрываем его при закрытии приложения, дабы не расходовать ресурсы компьютера.
Теперь добавим на форму навигатор для навигации по таблице и редактирования записей, добавим две метки (Label), два Edita и кнопку. Вот как всё это примерно будет выглядеть:

Ну кончено вы можете оформить всё это по другому, ваше право.
Далее нам необходимо заставить по нажатию кнопки выполняться процедуру из созданного нами пакета, передав при этом ей необходимые параметры. Тут есть небольшая проблема - в стандартном наборе компонентов BDE нет компонента для связи с пакетом (для сравнения в Direct Oracle Access он есть), а компонент StoredProc который и применяется для связи с процедурами и функциями хранимыми на сервере не видит пакетов, соответственно не видит и процедур входящих в эти пакеты.
Выходом из ситуации будет использование компонента Query, так как он позволяет выполнять любой SQL или PL/SQL код на сервере, что нам в принципе и надо.

Итак, добавляем Query на форму, в свойстве DatabaseName аналогично Table'у указываем нашу базу (manuf), а в свойстве SQL пишем следующее:

begin
Employees.Sal(:old_sal,:new_sal);
end;

Здесь мы выполняем PL/SQL код - выполнение процедуры Sal входящей в пакет Employees, при этом указываются входящие параметры процедуры - :old_sal и :new_sal
Далее необходимо зайти в свойство Params компонента Query и для каждого параметра свойство DataType установить в ftInteger иначе программа будет ругаться.
Затем остаётся только для процедуры нажатия кнопки прописать следующий код:

Query1.ParamByName('old_sal').Value:=Edit1.Text;
Query1.ParamByName('new_sal').Value:=Edit2.Text;
Query1.ExecSQL;
Table1.Refresh;

Здесь мы передаём в качестве параметров процедуры значения Edit'ов (старый и новый оклады), и заставяем Query выполнить код прописанный в свойстве SQL. Затем обновляем таблицу чтобы не делать это вручную и сразу видеть результаты наших действий.
Ну вот вроде и всё, можете откомпилировать проект и запустить его, должно работать :)

На этом мы закончим наше знакомство с пакетами, этого примера хватит чтобы понять как использовать пакеты при проектировании приложений.

Written by: Ronin (master_t@inbox.ru)