Данные пакета PL/SQL состоят из переменных и констант, определенных на уровне пакета, а не в конкретной его функции или процедуре. Их областью видимости является не отдельная программа, а весь пакет. Структуры данных пакета существуют (и сохраняют свои значения) на протяжении всего сеанса, а не только во время выполнения программы. Если данные пакета объявлены в его теле, они сохраняются в течение сеанса, но доступны только элементам пакета (то есть являются приватными).
Если данные пакета объявлены в его спецификации, они также сохраняются в течение всего сеанса, но их чтение и изменение разрешено любой пользовательской программе, обладающей привилегией EXECUTE для пакета. Общие данные пакета похожи на глобальные переменные Oracle Forms (а их использование сопряжено с таким же риском).
Если пакетная процедура открывает курсор, он остается открытым и доступным в ходе всего сеанса. Нет необходимости объявлять курсор в каждой программе. Один модуль может его открыть, а другой — выполнить выборку данных. Переменные пакета могут использоваться для передачи данных между транзакциями, поскольку они привязаны не к транзакции, а к сеансу.
Глобальные данные в сеансе Oracle
В среде PL/SQL структуры данных пакета функционируют как глобальные. Однако следует помнить, что они доступны только в пределах одного сеанса или подключения к базе данных Oracle и не могут совместно использоваться несколькими сеансами. Если доступ к данным нужно обеспечить для нескольких сеансов Oracle, используйте пакет DBMS_PIPE (его описание имеется в документации Oracle Built-In Packages).
Будьте осторожны с предположением о том, что разные части приложения всегда работают с Oracle через одно подключение. В некоторых случаях среда, из которой выполняется компонент приложения, устанавливает для него новое подключение. При этом данные пакета, записанные первым подключением, будут недоступны для второго.
Допустим, приложение Oracle Forms сохранило значение в пакетной структуре данных. Когда форма вызывает хранимую процедуру, эта процедура может обращаться к тем же пакетным переменным и значениям, что и форма, потому что они используют одно подключение к базе данных. Но допустим, форма генерирует отчет с использованием Oracle Reports. По умолчанию Oracle Reports создает для отчета отдельное подключение к базе данных с тем же именем пользователя и паролем. Даже если отчет обратится к тому же пакету и структурам данных, что и форма, значения, хранимые в структурах данных, доступных форме и отчету, будут разными, поскольку сеанс отчета имеет свой экземпляр пакета и всех его структур.
По аналогии с двумя типами структур данных в пакетах (общедоступные и приватные) также существуют два типа глобальных данных пакетов: глобальные общедоступные данные и глобальные приватные данные. В следующих трех разделах статьи рассматриваются различные способы использования данных пакетов.
Глобальные общедоступные данные
Любая структура данных, объявленная в спецификации пакета, является глобальной общедоступной структурой данных; это означает, что к ней может обратиться любая
программа за пределами пакета. Например, вы можете определить коллекцию PL/ SQL в спецификации пакета и использовать ее для ведения списка работников, заслуживших повышение. Вы также можете создать пакет с константами, которые должны использоваться во всех программах. Другие разработчики будут ссылаться на пакетные константы вместо использования фиксированных значений в программах. Глобальные общедоступные структуры данных также могут изменяться, если только они не были объявлены с ключевым словом CONSTANT.
Глобальные данные обычно считаются источником повышенной опасности в программировании. Их очень удобно объявлять, они прекрасно подходят для того, чтобы вся информация была доступна в любой момент времени — однако зависимость от глобальных структур данных приводит к созданию неструктурированного кода с множеством побочных эффектов.
Вспомните, что спецификация модуля должна сообщать полную информацию, необходимую для вызова и использования этого модуля. Однако по спецификации пакета невозможно определить, выполняет ли пакет чтение и/или запись в глобальные структуры данных. По этой причине вы не можете быть уверены в том, что происходит в приложении и какая программа изменяет те или иные данные.
Передачу данных модулям и из них всегда рекомендуется осуществлять через параметры. В этом случае зависимость от структур данных документируется в спецификации и может учитываться разработчиками. С другой стороны, именованные глобальные структуры данных должны создаваться для информации, действительно глобальной по отношению к приложению — например, констант и параметров конфигурации.
Такие данные следует разместить в централизованном пакете. Однако учтите, что при такой архитектуре в приложении возникает «единая точка перекомпиляции»: каждый раз, когда вы вносите изменение в пакет и перекомпилируете спецификацию, многие программы приложения теряют работоспособность.
Пакетные курсоры
Одним из самых интересных типов пакетных данных является явный курсор PL/SQL. Его можно объявить в теле либо в спецификации пакета. Состояние курсора (открыт или закрыт), а также указатель на его набор данных сохраняются в течение всего сеанса. Это означает, что открыть пакетный курсор можно в одной программе, выбрать из него данные — в другой, а закрыть — в третьей. Такая гибкость курсоров предоставляет большие возможности, но в то же время она может стать источником проблем. Сначала мы рассмотрим некоторые тонкости объявления пакетных курсоров, а затем перейдем к открытию, выборке данных и закрытию таких курсоров.
Объявление пакетных курсоров
Явный курсор в спецификации пакета можно объявлять двумя способами:
- Полностью (заголовок курсора и запрос). Именно так объявляются курсоры в локальных блоках
PL/SQL. - Частично (только заголовок курсора). В этом случае запрос определяется в теле пакета, поэтому реализация курсора скрыта от использующего пакет разработчика. При использовании второго способа в объявление нужно добавить секцию
RETURN, указывающую, какие данные будут возвращены при выборке из курсора. На самом деле эти данные определяются инструкциейSELECT, которая присутствует только в теле, но не в спецификации
В секции RETURN можно задать одну из следующих структур данных:
- О запись, объявленная на основе таблицы базы данных с использованием атрибута
%rowtype;
- О запись, определенная программистом.
Объявление курсора в теле пакета осуществляется так же, как в локальном блоке PL/ SQL. Следующий пример спецификации пакета демонстрирует оба подхода:
PACKAGE book_info
IS
CURSOR byauthor_cur (
author_in IN books.author%TYPE
)
IS
SELECT *
FROM books
WHERE author = author_in;
CURSOR bytitle_cur (
title_filter_in IN books.title%TYPE
) RETURN books%ROWTYPE;
TYPE author_summary_rt IS RECORD (
author books.author%TYPE,
total_page_count PLS_INTEGER,
total_book_count PLS_INTEGER);
CURSOR summary_cur (
author_in IN books.author%TYPE
) RETURN author_summary_rt;
END book_info;Логика программы описана в следующей таблице.
| Строки | Описание |
| 3-9 | Типичное определение явного курсора, полностью заданное в спецификации пакета |
| 11-13 | Определение курсора без запроса. Спецификация указывает, что открыв курсор и выбрав из него данные, пользователь получит одну строку из таблицы books под действием заданного фильтра |
| 15-18 | Определение нового типа записи для хранения информации об авторе |
| 20-22 | Объявление курсора, возвращающего сводную информацию о заданном авторе (всего три значения) |
Рассмотрим тело пакета и выясним, какой код необходимо написать для работы с каждым из этих курсоров:
PACKAGE BODY book_info
IS
CURSOR bytitle_cur (
title_filter_in IN books.title%TYPE
) RETURN books%ROWTYPE
IS
SELECT *
FROM books
WHERE title LIKE UPPER (title_filter_in);
CURSOR summary_cur (
author_in IN books.author%TYPE
) RETURN author_summary_rt
IS
SELECT author, SUM (page_count), COUNT (*)
FROM books
WHERE author = author_in;
END book_info;Поскольку у нас имеются два курсора с секциями RETURN, их определения нужно завершить в теле пакета. Список выбираемых запросом элементов в теле пакета должен соответствовать (по количеству элементов и их типам данных) секции RETURN в спецификации пакета. Если это условие не соблюдается, то при компиляции пакета вы получите одно из следующих сообщений об ошибке:
PLS-00323: subprogram or cursor '<cursor>' is declared in a
package specification and must be defined in the package body
PLS-00400: different number of columns between cursor SELECT
statement and return value
Работа с пакетными курсорами
Теперь давайте посмотрим, как пользоваться пакетными курсорами. Прежде всего для открытия, выборки данных и закрытия вам не придется изучать новый синтаксис — нужно только задать имя пакета перед именем курсора. Например, чтобы запросить информацию о книгах по PL/SQL, можно выполнить такой блок кода:
DECLARE
onebook book_info.bytitle_cur%ROWTYPE;
BEGIN
OPEN book_info.bytitle_cur ('%PL/SQL%');
LOOP
EXIT WHEN book_info.bytitle_cur%NOTFOUND;
FETCH book_info.bytitle_cur INTO onebook;
book_info.display (onebook);
END LOOP;
CLOSE book_info.bytitle_cur;
END;
Как видите, на основе пакетного курсора точно так же можно объявить переменную с использованием %ROWTYPE и проверить атрибуты. Ничего нового!
Однако и в этом простом фрагменте кода есть скрытый нюанс. Поскольку курсор объявлен в спецификации пакета, его область видимости не ограничивается конкретным блоком PL/SQL. Предположим, мы выполняем следующий код:
BEGIN -- Только открываем курсор...
OPEN book_info.bytitle_cur ('%PEACE%');
END;
Если затем в том же сеансе выполнить приведенный анонимный блок в приведенном выше цикле LOOP, то Oracle выдаст сообщение об ошибке (ORA-06511).
Дело в том, что блок, выполненный первым, не закрыл курсор, и по завершении его работы курсор остался открытым.
При работе с пакетными курсорами необходимо всегда соблюдать следующие правила:
- Никогда не рассчитывайте на то, что курсор закрыт (и готов к открытию).
- Никогда не рассчитывайте на то, что курсор открыт (и готов к закрытию).
- Всегда явно закрывайте курсор после завершения работы с ним. Эту логику также необходимо включить в обработчики исключений; убедитесь в том, что курсор закрывается на всех путях выхода из программы.
Если пренебречь этими правилами, то ваши приложения будут работать нестабильно, а в процессе их функционирования могут появиться неожиданные необработанные исключения. Поэтому лучше написать процедуры, которые открывают и закрывают курсоры и учитывают все возможные их состояния. Этот подход реализован в следующем пакете:
PACKAGE personnel
IS
CURSOR emps_for_dept (
department_id_in_in IN employees.department_id%TYPE)
IS
SELECT * FROM employees
WHERE department_id = department_id_in;
PROCEDURE open_emps_for_dept(
department_id_in IN employees.department_id%TYPE,
close_if_open IN BOOLEAN := TRUE
);
PROCEDURE close_emps_for_dept;
END personnel;
Как видите, вместе с курсором объявлены две сопутствующие процедуры для его открытия и закрытия. Если нам, скажем, потребуется перебрать в цикле строки курсора, это можно сделать так:
DECLARE
one_emp personnel.emps_for_dept%ROWTYPE;
BEGIN
personnel.open_emps_for_dept (1055);
LOOP
EXIT WHEN personnel.emps_for_dept%NOTFOUND;
FETCH personnel.emps_for_dept INTO one_emp;
END LOOP;
personnel.close_emps_for_dept;
END;
В этом фрагменте не используются явные вызовы OPEN и CLOSE. Вместо них вызываются соответствующие процедуры, скрывающие особенности работы с пакетными курсорами.
Можно взглянуть на ситуацию под другим углом:
...Вместо того чтобы работать с пакетными курсорами, можно добиться точно такого же эффекта посредством инкапсуляции логики и данных в представлениях и опубликовать их для разработчиков. В этом случае разработчики будут нести ответственность за сопровождение своих курсоров; дело в том, что обеспечить нормальное сопровождение с инструментарием, существующим для общедоступных пакетных курсоров, невозможно. А именно, насколько мне известно, невозможно гарантировать использование процедур открытия/закрытия, но курсор всегда будет оставаться видимым для разработчика, который открывает/закрывает его напрямую; следовательно, такая конструкция остается уязвимой. Проблема усугубляется тем, что использование общедоступных пакетных курсоров и процедур открытия/ закрытия может породить в группе ложное чувство безопасности и защищенности».
Пакетное создание курсоров и предоставление доступа к ним всем разработчикам, участвующим в проекте, приносит большую пользу. Проектирование оптимальных структур данных приложения — непростая и кропотливая работа. Эти структуры — и хранящиеся в них данные — используются в программах PL/SQL, а работа с ними почти всегда осуществляется через курсоры. Если вы не определите свои курсоры в пакетах и не предоставите их «в готовом виде» всем разработчикам, то каждый будет писать собственную реализацию курсора, а это создаст массу проблем с производительностью и сопровождением кода. Пакетные курсоры являются лишь одним из примеров инкапсуляции доступа к структурам данных.
Повторно инициализируемые пакеты
По умолчанию пакетные данные сохраняются в течение всего сеанса (или до перекомпиляции пакета). Это исключительно удобное свойство пакетов, но и у него имеются определенные недостатки:
- Постоянство глобально доступных (общих и приватных) структур данных сопровождается нежелательными побочными эффектами. В частности, можно случайно оставить пакетный курсор открытым, а в другой программе попытаться открыть его без предварительной проверки, что приведет к ошибке.
- Если данные хранятся в структурах уровня пакетов, то программа может занять слишком большой объем памяти, не освобождая ее.
Для оптимизации использования памяти при работе с пакетами можно использовать директиву SERIALLY_REUSABLE. Она указывает Oracle, что пакет является повторно инициализируемым, то есть его состояние (состояние переменных, открытых пакетных курсоров и т. п.) нужно сохранять не на протяжении сеанса, а на время одного вызова пакетной программы.
Рассмотрим действие этой директивы на примере пакета book_info. В нем имеются две отдельные программы: для заполнения списка книг и для вывода этого списка.
PACKAGE book_info
IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE fill_list;
PROCEDURE show_list;
END;
Как видно из приведенного ниже тела пакета, список объявляется как приватный глобальный ассоциативный массив:
PACKAGE BODY book_info
IS
PRAGMA SERIALLY_REUSABLE;
TYPE book_list_t
IS
TABLE OF books%ROWTYPE
INDEX BY PLS_INTEGER;
my_books book_list_t;
PROCEDURE fill_list
IS
BEGIN
FOR rec IN (SELECT *
FROM books
WHERE author LIKE '%FEUERSTEIN%')
LOOP
my_books (my_books.COUNT + 1) := rec;
END LOOP;
END fill_list;
PROCEDURE show_list
IS
BEGIN
IF my_books.COUNT = 0
THEN
DBMS_OUTPUT.PUT_LINE ('** Книг нет...');
ELSE
FOR indx IN 1 .. my_books.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (my_books (indx).title);
END LOOP;
END IF;
END show_list;
END;
Чтобы увидеть, как работает эта директива, заполним список и выведем его на экран. В первом варианте оба шага выполняются в одном блоке:
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE (
3 'Заполнение и вывод в одном блоке:'
4 );
5 book_info.fill_list;
6 book_info.show_list;
7 END;
8 /
Заполнение и вывод в одном блоке:
Oracle PL/SQL Programming
Oracle PL/SQL Best Practices
Oracle PL/SQL Built-in PackagesВо второй версии заполнение и вывод списка производятся в разных блоках. В результате коллекция окажется пустой:
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('Заполнение в первом блоке');
3 book_info.fill_list;
4 END;
5 /
Заполнение в первом блоке
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('Вывод во втором блоке:');
3 book_info.show_list;
4 END;
5 /
Вывод во втором блоке:
** Нет книг...Работая с повторно инициализируемыми пакетами, необходимо учитывать некоторые особенности:
- Глобальная память для такого пакета выделяется в системной глобальной области (SGA) базы данных Oracle, а не в пользовательской глобальной области (UGA), что позволяет повторно применять рабочую область пакета. При каждом новом обращении к пакету его общие переменные инициализируются значениями по умолчанию или значением NULL, а его инициализационный раздел выполняется повторно.
- Максимальное количество рабочих областей, необходимых для повторно инициализируемого пакета, равно количеству одновременно работающих с этим пакетом пользователей. Увеличение объема используемой памяти в SGA компенсируется уменьшением объема памяти, задействованного в UGA. Когда нужно предоставить память из SGA другим запросам, Oracle освобождает неиспользуемые области памяти.
