Итак, в прошлых моих блогах мы рассмотрели синтаксис, основные правила и нюансы построения пакетов. Теперь можно вернуться к списку ситуаций, в которых пакеты PL/SQL следует применять, и рассмотреть их более подробно.
- Инкапсуляция (сокрытие) операций с данными. Вместо того чтобы заставлять разработчиков самих писать команды SQL (что усложняет сопровождение и снижает эффективность кода), предоставьте программный интерфейс (API) к этим командам SQL. Такой интерфейс называется табличным, или транзакционным.
- Исключение жесткого кодирования литералов. Чтобы избежать жесткого кодирования одних и тех же значений в нескольких программах, нужно определить все литеральные значения как константы и объединить их в пакеты. Можно также объявить константы в процедурах и функциях, но преимущество пакетных констант заключается в их общедоступности.
- Устранение недостатков встроенных функций. Некоторые встроенные средства Oracle (например,
UTL_FILE
иDBMS_OUTPUT
) далеки от совершенства. На их базе можно создать собственный пакет, в котором значительная часть недостатков исправлена. - Группировка логически связанных функций. Если у вас имеется десяток процедур и функций, относящихся к реализации некоторой части приложения, сгруппируйте их в пакет — это упростит управление и сопровождение кода.
- Кэширование статических данных сеанса для ускорения работы приложения. Для кэширования данных, не изменяющихся в течение сеанса, пользуйтесь постоянными структурами данных пакетов.
Ниже каждая из этих причин рассматривается более подробно.
Инкапсуляция доступа к данным
Вместо того чтобы заставлять разработчиков самостоятельно писать команды SQL, предоставьте интерфейс к этим командам. Это одна из самых важных причин для построения пакетов, но она применяется относительно редко.
При таком подходе разработчики PL/SQL вместо команд SQL обычно включают в свои приложения заранее определенный, протестированный и оптимизированный код, который выполняет всю работу за них; например, процедуру add (перегруженную для поддержки записей), которая выдает команду INSERT
и следует стандартным правилам обработки ошибок, функцию для выборки одной записи по первичному ключу и разнообразные курсоры для обработки стандартных запросов к структуре данных (которой может быть одна таблица или «бизнес-сущность» из нескольких таблиц).
Если вы выберете этот путь, разработчикам не нужно будет разбираться, как объединить три или шесть нормализованных таблиц для получения нужного набора данных. Они просто выбирают нужный курсор, а анализ данных оставляется кому-то другому. Им не нужно думать, что делать, если при попытке вставки строка уже существует — процедура уже содержит всю необходимую логику.
Вероятно, самое серьезное преимущество такого подхода заключается в том, что при изменении структуры данных проблемы с обновлением кода приложения сводятся к минимуму и централизуются. Человек, хорошо знающий таблицу или объектный тип, вносит необходимые изменения в одном пакете, а эти изменения затем более или менее автоматически распространяются на всех программы, зависящие от этого пакета.
Инкапсуляция данных — весьма обширная и непростая тема. Пример пакета инкапсуляции таблицы (для таблицы employees) содержится в файлах employee_tp.pks
, employee_qp.*, employee_cp.*, department_tp.pks и department_qp.
* на сайте книги (эти файлы были сгенерированы программой Quest CodeGen Utility
, которую можно загрузить на сайте ToadWorld).
Давайте посмотрим, как использование пакетов отражается на коде. Файл givebonusi. sp
на сайте книги содержит процедуру, которая начисляет одинаковые премии всем работникам заданного отдела, — но только при условии, что стаж работника в компании составляет не менее 6 месяцев. Ниже приведены части программы give_bonus
с кодом SQL
(полная реализация содержится в файле givebonusi.sp
):
PROCEDURE give_bonus (
dept_in IN employees.department_id%TYPE,
bonus_in IN NUMBER)
/*
|| Начисление премии каждому работнику заданного отдела,
|| но только при условии, что он проработал в компании
|| не менее 6 месяцев
*/
IS
l_name VARCHAR2(50);
CURSOR by_dept_cur
IS
SELECT * FROM employees
WHERE department_id = dept_in;
fdbk INTEGER;
BEGIN
/* Выборка всей информации по заданному отделу. */
SELECT department_name INTO l_name
FROM departments
WHERE department_id = dept_in;
/* Проверка идентификатора отдела. */
IF l_name IS NULL THEN
DBMS_OUTPUT.PUT_LINE (
'Invalid department ID specified: ' || dept_in);
ELSE
/* Вывод заголовка. */
DBMS_OUTPUT.PUT_LINE (
'Applying Bonuses of ' || bonus_in ||
' to the ' || l_name || ' Department');
END IF;
/* Для каждого работника заданного отдела... */
FOR rec IN by_dept_cur
LOOP
IF employee_rp.eligible_for_bonus (rec)
THEN
/* Обновление столбца. */
UPDATE employees
SET salary = rec.salary + bonus_in
WHERE employee_id = rec.employee_id;
END IF;
END LOOP;
END;
Сравните с альтернативным решением, полный код которого содержится в файле givebonus2.sp
:
PROCEDURE give_bonus (
dept_in IN employee_tp.department_id_t
, bonus_in IN employee_tp.bonus_t
)
IS
l_department department_tp.department_rt;
l_employees employee_tp.employee_tc;
l_rows_updated PLS_INTEGER;
BEGIN
l_department := department_tp.onerow (dept_in);
l_employees := employee_qp.ar_fk_emp_department (dept_in);
FOR l_index IN 1 .. l_employees.COUNT
LOOP
IF employee_rp.eligible_for_bonus (rec)
THEN
employee_cp.upd_onecol_pky
(colname_in => 'salary'
, new_value_in => l_employees (l_index).salary
+ bonus_in
, employee_id_in => l_employees (l_index).employee_id
, rows_out => l_rows_updated
);
END IF;
END LOOP;
... Дополнительная обработка имени и других элементов...
END;
В следующей таблице объясняются изменения, внесенные во второй версии.
Строки | Описание |
2-7 | Объявления, основанные на таблицах, уже не используют %TYPE и %ROWTYPE . Вместо нихпредоставляется «пакет типов» с объявлениями SUBTYPE , которые в свою очередь базируются на %TYPE и %ROWTYPE . При таком подходе коду приложения не нужно предоставлятьпрямой доступ к нижележащим таблицам (невозможный в инкапсулированной среде) |
10 | SELECT INTO заменяется вызовом функции, возвращающей «одну строку» информации дляпервичного ключа |
11 | Вызов функции, получающей все строки работников для внешнего ключа (идентификатор отдела). Функция использует BULK COLLECT и возвращает коллекцию записей, демонстрируя, как инкапсулированный код упрощает использование новых возможностей PL/SQL |
13-25 | Цикл FOR с курсором заменяется циклом FOR со счетчиком, перебирающим содержимоеколлекции |
17-23 | Динамический SQL используется для обновления одного столбца с заданным первичнымключом |
В целом команды SQL были исключены из программы и заменены вызовами процедур и функций, предназначенных для многократного использования. В моем приложении такое решение оптимизирует SQL и способствует более эффективному написанию более надежного кода. Построение (или генерирование) таких пакетов ни в коей мере не является тривиальным делом, я и понимаю, что большинство читателей вряд ли захотят или смогут использовать «чистый» инкапсулированный подход. Однако многие преимущества инкапсуляции данных могут использоваться и без полной переработки стиля программирования. Как минимум я рекомендую:
- Скрыть все однострочные запросы за функциональным интерфейсом. Тем самым вы обеспечите гарантированную обработку ошибок и сможете выбрать оптимальную реализацию (например, неявные или явные курсоры).
- Определить, с какими таблицами разработчики чаще всего взаимодействуют напрямую, и построить для них прослойку программного кода.
- Создать пакетные программы для сложных транзакций. Если операция «добавление нового заказа» включает вставку двух строк, обновление шести строк и т. д., обязательно встройте эту логику в процедуру, которая скрывает все сложности. Не рассчитывайте на то, что разработчики сами во всем разберутся (и все правильно запрограммируют в разных местах!).
Исключение жесткого кодирования литералов
Практически в каждом приложении используются всевозможные «волшебные значения» — литералы, имеющие особый смысл для системы. Например, это могут быть коды типов или граничные значения для проверки данных. Конечно, пользователи скажут вам, что волшебные значения никогда не изменяются. «В моем балансе всегда будет ровно 25 позиций», — говорит один. «Родительская компания всегда будет называться ATLAS HQ», — клянется другой. Не верьте клятвам и никогда не фиксируйте их в своих программах. Возьмем следующие команды IF:
IF footing_difference BETWEEN 1 and 100
THEN
adjust_line_item;
END IF;
IF cust_status = 'C'
THEN
reopen_customer;
END IF;
Тот, кто пишет подобный код, сам напрашивается на неприятности. Ваша жизнь намного упростится, если вы создадите пакет с именованными константами:
PACKAGE config_pkg
IS
closed_status CONSTANT VARCHAR2(1) := 'C';
open_status CONSTANT VARCHAR2(1) := 'O';
active_status CONSTANT VARCHAR2(1) := 'A';
inactive_status CONSTANT VARCHAR2(1) := 'I';
min_difference CONSTANT PLS_INTEGER := 1;
max_difference CONSTANT PLS_INTEGER := 100;
earliest_date CONSTANT DATE := SYSDATE;
latest_date CONSTANT DATE := ADD_MONTHS (SYSDATE, 120);
END config_pkg;
С таким пакетом две предшествующие команды IF принимают следующий вид:
IF footing_difference
BETWEEN config_pkg.min_difference and config_pkg.max_difference
THEN
adjust_line_item;
END IF;
IF cust_status = config_pkg.closed_status THEN
reopen_customer;
END IF;
Если в будущем какое-либо из «волшебных значений» изменится, достаточно изменить соответствующую константу в конфигурационном пакете. Вносить изменения в остальных модулях не нужно. Практически в каждом приложении из тех, которые я рецензировал (и некоторые из тех, что я написал), ошибочно включались жестко закодированные «волшебные значения». В каждом случае разработчику приходилось вносить множественные изменения в программы — как в фазе разработки, так и в фазе сопровождения. Иногда это создает проблемы, иногда оборачивается сущим кошмаром; я не могу выразить словами, насколько важно консолидировать все «волшебные значения» в одном или нескольких пакетах.
Другой пример такого пакета приведен в файле utl_file_constants.pkg
. Этот пакет выбирает несколько иной путь: все значения скрываются в теле пакета. Спецификация пакета состоит только из функций, возвращающих значения. Если вдруг понадобится изменить какое-нибудь значение, перекомпилировать спецификацию пакета не придется — как и зависимые программы.
Наконец, если вам доведется выбирать литеральные значения, которые планируется скрыть за константами, старайтесь использовать невероятные значения, которые вряд ли будут использоваться как литералы. Предположим, процедура должна возвращать индикатор состояния: успех или неудача? Типичные значения таких флагов — 0 и 1, S и F и т. д. Тем не менее у таких значений есть один недостаток: они коротки и интуитивно понятны, поэтому у недисциплинированного программиста возникает соблазн «смухлевать» и напрямую использовать литералы в коде. Возьмем следующий пример:
PACKAGE do_stuff
IS
c_success CONSTANT PLS_INTEGER := 0; c_failure CONSTANT PLS_INTEGER := 1;
PROCEDURE big_stuff (stuff_key_in IN PLS_INTEGER, status_out OUT PLS_INTEGER);
END do_stuff;
Скорее всего, с таким определением вы столкнетесь с использованием big_stuff
следующего вида:
do_stuff.big_stuff (l_stuff_key, l_status);
IF l_status = 0
THEN
DBMS_OUTPUT.PUT_LINE ('Все нормально!');
END IF
С другой стороны, если спецификация пакета выглядит так:
PACKAGE do_stuff
IS
/* Произвольные литеральные значения! */
c_success CONSTANT PLS_INTEGER := -90845367;
c_failure CONSTANT PLS_INTEGER := 55338292;
PROCEDURE big_stuff (stuff_key_in IN PLS_INTEGER, status_out OUT PLS_INTEGER);
END do_stuff;
вы никогда не увидите такой код:
do_stuff.big_staff (l_stuff_key, l_status);
IF l_status = -90845367
THEN
DBMS_OUTPUT.PUT_LINE ('Все нормально!');
END IF;
Он выглядит просто неприлично.
Устранение недостатков встроенных функций
Некоторые стандартные пакеты Oracle — такие, как UTL_FILE
или DBMS_OUTPUT
, — либо содержат неприятные ошибки, либо отражают неудачные решения, принятые проектировщиками. У всех нас есть «больные мозоли», и они не всегда связаны с тем, какие вспомогательные средства создает для нас база данных Oracle. Как насчет «гениального» консультанта, который явился в ваш городок в прошлом году? Вы все еще пытаетесь разгрести код, оставшийся после него? Даже если заменить этот код невозможно, вы по крайней мере можете построить свой пакет на основе чужих пакетов, неудачно спроектированных структур данных и т. д. и по возможности исправить чужие ошибки.
Вместо того чтобы заполнять страницы книги примерами, я перечислю файлы нескольких пакетов, размещенных на сайте книги. Эти примеры демонстрируют использование пакетов, а также ряд других полезных возможностей. Я рекомендую просмотреть файлы *.pkg на сайте — вы найдете в них код, который может пригодиться в ваших приложениях. С чего стоит начать?
-
filepath.pkg
— добавляет поддержку путей в UTL_FILE, чтобы поиск заданного файла мог проводиться по нескольким каталогам. xfile.pkg
иJFile.java
(илиsf_file.pks/pkb
иsf_file.java
) — расширяет область примененияUTL_FILE
. Пакет на базе класса Java способен выполнять многие задачи, не поддерживаемыеUTL_FILE.
Пакет xfile также предоставляет стопроцентную поддержку интерфейсаUTL_FILE
. Это означает, что вы можете провести глобальный поиск с заменой «UTL_FILE
» на «xfile
» — и все будет работать как прежде!-
sf_out.pks/pkb
,bpl.sp, do.pkg
— замена функциональности выводаDBMS_OUTPUT
, которая поможет обойти недостатки проектирования (невозможность выводаBOOLEAN
или — до выхода Oracle Database 10g — строк длиной более 255 байт).
Группировка логически связанных функций
Если ваша программа содержит десяток процедур и функций, связанных с конкретной функциональностью или аспектом вашего приложения, разместите их в пакете, чтобы упростить управление этим кодом (и его поиск). Это особенно важно при программировании бизнес-правил приложения, в реализации которых следует соблюдать некоторые важные правила:
- Не пытайтесь жестко кодировать эти правила (обычно в нескольких местах) в компонентах приложения.
- Не распределяйте реализацию правил по нескольким автономным программам, которыми будет трудно управлять.
Прежде чем браться за построение приложения, сконструируйте набор пакетов, инкапсулирующих все его правила. Иногда эти правила являются частью большего пакета — например, пакета инкапсуляции таблиц. В других случаях можно создать пакет, который не содержит ничего, кроме ключевых правил, как в следующем примере:
PACKAGE customer_rules
IS
FUNCTION min_balance RETURN PLS_INTEGER;
FUNCTION eligible_for_discount
(customer_in IN customer%ROWTYPE)
RETURN BOOLEAN;
FUNCTION eligible_for_discount
(customer_id_in IN customer.customer_id%TYPE)
RETURN BOOLEAN;
END customer_rules;
Функция eligible_for_discount
скрыта в пакете, чтобы ею было удобнее управлять. Я также использую перегрузку, чтобы предоставить два разных интерфейса к формуле: первый получает первичный ключ и проверяет клиента по базе данных, а второй применяет свою логику к информации клиента, уже загруженной в запись %ROWTYPE
. Почему я так поступил? Потому что если оператор уже запросил информацию клиента из базы данных, он может воспользоваться перегруженной версией для %ROWTYPE
и избежать второго запроса.
Конечно, не вся «логически связанная функциональность» имеет отношение к бизнесправилам. Допустим, мне нужно расширить возможности встроенных функций PL/ SQL для работы со строками. Вместо того чтобы создавать 12 разных функций, я создаю пакет, размещаю все функции в этом пакете и сообщаю другим разработчикам, как обратиться к этой функциональности.
Кэширование статических данных сеанса для ускорения работы приложения
Используйте данные пакетов для улучшения времени отклика приложения посредством кэширования статических данных (без повторных запросов). Это можно сделать на нескольких уровнях. Для каждого варианта в следующем списке я привожу несколько полезных примеров кода, доступных на сайте книги.
- Кэширование одного значения — например, имени текущего пользователя (полученного функцией user). Примеры:
thisuser.pkg
иthisuser.tst
. - Кэширование одной строки или информационного набора — например, параметров конфигурации заданного пользователя. Примеры:
init.pkg
иinit.tst
. - Кэширование списка значений — например, содержимого статической таблицы подстановки. Примеры:
emplu.pkg
иemplu.tst
. - Использование файлов .tst для сравнения быстродействия с кэшированием и без него.
Кэширование на базе пакетов — всего лишь одна из разновидностей кэширования, доступных для разработчиков PL/SQL.
Если вы решите использовать кэширование уровня пакета, помните, что данные кэшируются по отдельности для каждого сеанса, обращающегося к пакету (в глобальной области PGA базы данных Oracle). Таким образом, если кэш занимает 2 Мбайт и в системе существует 1000 одновременно подключенных сеансов, вы расходуете 2 Гбайт памяти своей системы — кроме всей остальной памяти, расходуемой базой данных.