Когда используются пакеты (package) PL/SQL на примере

Когда следует использовать пакеты PL/SQL?Итак, в прошлых моих блогах мы рассмотрели синтаксис, основные правила и нюансы построения пакетов. Теперь можно вернуться к списку ситуаций, в которых пакеты 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 Гбайт памяти своей системы — кроме всей остальной памяти, расходуемой базой данных.

 

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 7008 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3110 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Символьные функции и аргументы...
Символьные функции и аргументы... 10862 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5747 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Войдите чтобы комментировать