Как найти пакет в sql

Sometimes the column you are looking for may be part of the name of many other things that you are not interested in.

For example I was recently looking for a column called «BQR», which also forms part of many other columns such as «BQR_OWNER», «PROP_BQR», etc.

So I would like to have the checkbox that word processors have to indicate «Whole words only».

Unfortunately LIKE has no such functionality, but REGEXP_LIKE can help.

SELECT *
  FROM user_source
 WHERE regexp_like(text, '(s|.|,|^)bqr(s|,|$)');

This is the regular expression to find this column and exclude the other columns with «BQR» as part of the name:

(s|.|,|^)bqr(s|,|$)

The regular expression matches white-space (s), or (|) period (.), or (|) comma (,), or (|) start-of-line (^), followed by «bqr», followed by white-space, comma or end-of-line ($).

В этом учебном пособии вы узнаете, как создавать пакеты и тела пакетов в Oracle PL/SQL с синтаксисом и примерами.

Описание

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

• Что такое пакет Oracle PL/SQL?
• Синтаксис
• Пример пакета PL/SQL
• Преимущества пакетов Oracle PL/SQL
• Понимание спецификации пакета.
• Понимание тела пакета.
• Некоторые примеры функций пакета.
• Частные и публичные предметы в пакетах.
• Перегрузка подпрограмм пакета.
• Как пакет STANDARD определяет среду Oracle PL/SQL
• Руководство по написанию пакетов

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

Как показано на рисунке, вы можете думать о спецификации как о рабочем интерфейсе, а о теле — как о «черном ящике». Вы можете отлаживать, улучшать или изменять тело пакета без изменения интерфейса (спецификации) пакета.

Для создания пакетов используйте оператор CREATE PACKAGE.

Синтаксис

Синтаксис CREATE PACKAGE в Oracle PL/SQL:

collection_type_definition — определение типа коллекции
record_type_definition — определение типа записи
subtype_definition — определение подтипа
collection_declaration — объявление коллекции
constant_declaration — объявление константы
exception_declaration — объявление исключения
object_declaration — объявление объекта
record_declaration — объявление записи
variable_declaration — объявление переменной
cursor_spec — спецификация курсора
function_spec — спецификация функции
procedure_spec — спецификация процедуры
call_spec — спецификация вызова

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

Тело пакета содержит детали реализации и приватные объявления, которые скрыты от вашего приложения. За декларативной частью тела пакета следует необязательная часть инициализации, которая обычно содержит операторы, которые инициализируют переменные пакета.

Пример пакета Oracle PL/SQL

В приведенном ниже примере, вы определяете тип запись, курсор и две процедуры по трудоустройству. Обратите внимание, что процедура hire_employee использует последовательность базы данных empno_seq и функцию SYSDATE для вставки нового номера сотрудника и дату приема на работу соответственно.

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

Преимущества пакетов Oracle PL/SQL

Модульность

Пакеты позволяют инкапсулировать логически связанные типы, элементы и подпрограммы в именованный модуль PL/SQL. Каждый пакет прост для понимания, а интерфейсы между пакетами просты, понятны и хорошо определены. Это помогает разработке приложений.

Более простой дизайн приложений

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

Сокрытие информации

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

Добавленная функциональность

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

Лучшая производительность

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

Понимание спецификации пакета.

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

В спецификации перечислены элементы пакета, доступные приложениям. Вся информация, необходимая вашему приложению для использования элементов, содержится в спецификации. Например, следующее объявление показывает, что функция с именем fac принимает один аргумент типа INTEGER и возвращает значение типа INTEGER:

Это вся информация, необходимая для вызова функции. Вам не нужно рассматривать его базовую реализацию (например, итеративную или рекурсивную).

Только подпрограммы и курсоры имеют базовую реализацию. Таким образом, если спецификация объявляет только типы, константы, переменные, исключения и спецификации вызовов, то тело пакета не требуется. Рассмотрим следующий пакет без тела:

Пакет trans_data не нуждается в теле, потому что типы, константы, переменные и исключения не имеют базовой реализации. Такие пакеты позволяют вам определять глобальные переменные — используемые подпрограммами и триггерами базы данных — которые сохраняются в течение сеанса.

Ссылка на содержание пакета

Чтобы ссылаться на типы, элементы, подпрограммы и спецификации вызовов, объявленные в спецификации пакета, используйте точечную нотацию следующим образом:

package_name.type_name

package_name.item_name

package_name.subprogram_name

package_name.call_spec_name

Вы можете ссылаться на содержимое пакета с помощью триггеров базы данных, хранимых подпрограмм, прикладных программ 3GL и различных инструментов Oracle. Например, вы можете вызвать процедуру пакета hire_employee из PL/SQL Developer следующим образом:

Ограничения

Вы не можете ссылаться на удаленные переменные пакета прямо или косвенно. Например, вы не можете вызвать следующую процедуру удаленно, потому что она ссылается на переменную пакета, когда параметр инициализируется:

Кроме того, внутри пакета вы не можете ссылаться на переменные хоста.

Понимание тела пакета

Тело пакета реализует спецификацию пакета. То есть тело пакета содержит реализацию каждого курсора и подпрограммы, объявленных в спецификации пакета. Помните, что подпрограммы, определенные в теле пакета, доступны вне пакета, только если их спецификации также указаны в спецификации пакета.

Чтобы сопоставить спецификации и тела подпрограмм, PL/SQL выполняет сравнение их заголовков по определениям. Таким образом, за исключением пробелов, заголовки должны соответствовать слово в слово. В противном случае PL/SQL вызывает исключение, как показано в следующем примере:

Тело пакета также может содержать частные объявления, которые определяют типы и элементы, необходимые для внутренней работы пакета. Область этих объявлений является локальной для тела пакета. Поэтому объявленные типы и элементы недоступны, кроме как внутри тела пакета. В отличие от спецификации пакета, декларативная часть тела пакета может содержать тела подпрограммы.

За декларативной частью тела пакета следует дополнительная часть инициализации, которая обычно содержит операторы, которые инициализируют некоторые переменные, ранее объявленные в пакете.

Часть инициализации пакета играет второстепенную роль, потому что, в отличие от подпрограмм, пакет не может быть вызван или передан параметрами. В результате часть инициализации пакета запускается только один раз, при первом обращении к пакету.

Помните, если спецификация пакета объявляет только типы, константы, переменные, исключения и спецификации вызовов, тело пакета не требуется. Однако тело все еще можно использовать для инициализации элементов, объявленных в спецификации пакета.

Некоторые примеры функций пакета

Рассмотрим пакет с именем emp_actions ниже. Спецификация пакета объявляет следующие типы, элементы и подпрограммы:

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

Помните, что часть инициализации пакета запускается только один раз, когда вы впервые ссылаетесь на пакет. Итак, в последнем примере в таблицу базы данных emp_audit вставляется только одна строка. Аналогично, переменная number_hired инициализируется только один раз.

Каждый раз, когда вызывается процедура hire_employee, переменная number_hired обновляется. Тем не менее, число, сохраняемое в number_hired, зависит от конкретной сессии. То есть количество отражает количество новых сотрудников, обработанных одним пользователем, а не количество, обработанное всеми пользователями.

Пример пакета банковских операций

Следующий пример — это ваш пакет некоторых типичных банковских операций. Предположим, что дебетовые и кредитные транзакции вводятся в нерабочее время через банкоматы, а затем применяются к счетам на следующее утро.

В этом примере пакета часть инициализации не используется.

Приватные и публичные элементы в пакетах

Посмотрите еще раз на пакет emp_actions. Тело пакета объявляет переменную с именем number_hired, которая инициализируется 0 — нулем. В отличие от элементов, объявленных в спецификации emp_actions, элементы, объявленные в теле, ограничены для использования в пакете. Поэтому код PL/SQL вне пакета не может ссылаться на переменную number_hired. Такие элементы называются приватными.

Однако элементы, объявленные в спецификации emp_actions, такие как исключение invalid_salary, видны вне пакета. Поэтому любой код PL/SQL может ссылаться на исключение invalid_salary. Такие элементы называются публичными.

Когда вы должны поддерживать элементы в течение сеанса или между транзакциями, поместите их в декларативную часть тела пакета. Например, значение number_hired сохраняется между вызовами hire_employee в одном и том же сеансе. Значение теряется при завершении сеанса.

Если вы также должны сделать элементы публичными, поместите их в спецификацию пакета. Например, константа minimum_balance, объявленная в спецификации пакета bank_transactions, доступна для общего пользования.

Перегрузка подпрограмм пакета

PL/SQL позволяет двум или более подпрограммам пакета иметь одинаковое имя. Эта опция полезна, когда вы хотите, чтобы подпрограмма принимала аналогичные наборы параметров, которые имеют разные типы данных. Например, следующий пакет определяет две процедуры с именем journalize:

Первая процедура принимает trans_date как символьную строку, в то время как вторая процедура принимает ее как число (юлианский день). Каждая процедура обрабатывает данные соответствующим образом.

Как пакет STANDARD определяет среду Oracle PL/SQL

Пакет с именем STANDARD определяет среду PL/SQL. Спецификация пакета глобально объявляет типы, исключения и подпрограммы, которые автоматически доступны для программ PL/SQL. Например, пакет STANDARD объявляет функцию ABS, которая возвращает абсолютное значение своего аргумента, следующим образом:

Содержимое пакета STANDARD непосредственно видно приложениям. Вам не нужно указывать ссылки на его содержимое, добавляя префикс имени пакета. Например, вы можете вызывать ABS из триггера базы данных, хранимой подпрограммы, инструмента Oracle или приложения 3GL следующим образом:

Если вы повторно объявите ABS в программе PL/SQL, ваша локальная декларация переопределяет глобальную декларацию. Однако вы все равно можете вызвать встроенную функцию, указав ссылку на ABS следующим образом:

Большинство встроенных функций перегружены. Например, пакет STANDARD содержит следующие объявления:

PL/SQL разрешает вызов TO_CHAR путем сопоставления числа и типов данных формальных и фактических параметров.

Руководство по написанию пакетов

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

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

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

Оригинал статьи.

Пакеты

В данной части будут рассмотрены пакеты — основная сущность
при разработке на PL/SQL. Пакеты используются для группировки
функционала в именованные модули с возможностью разделения
интерфейса и реализации. На самом деле, мы уже сталкивались
с ними, когда рассматривали вывод на экран с использованием
dbms_output.put_line. Dbms_output — это пакет, а put_line —
процедура, объявленная в данном пакете.

Стуктура пакета

Пакеты как правило состоят из спецификации и тела.
Можно создать пакет без тела, только со спецификацией, такой
вариант использования тоже будет рассмотрен.

Спецификация пакета — это то, к чему можно обращаться при
работе с пакетом. В спецификации могут быть объявлены
типы, переменные, константы, сигнатуры процедур и функций.

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

Создание пакета

Общий синтаксис создания спецификации пакета выглядит
так:

create package pck_utils as
-- Specification code
end pck_utils;

Команда выше создаст пакет с названием pck_utils. Если
пакет с таким именем уже существует, будет выброшена ошибка,
и новый пакет не создастся. Чтобы заменить уже существующий
пакет, используется команда create or replace. На практике чаще
всего используют именно её:

create or replace package pck_utils as
-- Specification code
end pck_utils;

Тело пакета создаётся следующим образом:

create or replace package body pck_utils as
-- Specification code
end pck_utils;

Давайте создадим пакет и наполним его каким-нибудь функционалом:

create or replace package pck_date_utils as

-- Возвращает максимальную дату, 
-- используемую в системе
function maxdate return date;

-- Возвращает минимальную дату, 
-- используемую в системе
function mindate return date;

-- Добавляет указанное количество недель к
-- указанной дате. Для того, чтобы отнять
-- недели, нужно передать отрицательное число
function add_weeks(
    pdate date,
    pweeks number
) return date;

end pck_date_utils;

Это была спецификация пакета. Теперь создадим тело:

create or replace package body pck_date_utils as

function maxdate return date is
begin
    return to_date('4000.01.01', 'yyyy.mm.dd');
end;

function mindate return date is
begin
    return to_date('1800.01.01', 'yyyy.mm.dd');
end;

function add_weeks(
    pdate date,
    pweeks number
) return date
is
begin
    return pdate + (7 * pweeks);
end;

end pck_date_utils;

Теперь мы можем обращаться ко всему, что объявлено
в спецификации пакета в нашем коде. Обращение к содержимому
пакета осуществляется в виде имя_пакета.объект(под объектом
понимается всё, что объявлено в спецификации):

begin
    dbms_output.put_line(to_char(pck_date_utils.mindate, 'yyyy.mm.dd'));
    dbms_output.put_line(to_char(pck_date_utils.maxdate, 'yyyy.mm.dd'));
    dbms_output.put_line(to_char(pck_date_utils.add_weeks(sysdate, 3), 'yyyy.mm.dd'));
end;

Вывод:

1800.01.01
4000.01.01
2022.03.30

Удаление пакета

Удаление производится командой drop package:

drop package pck_date_utils

Компиляция пакета

В учебнике мы предполагаем, что текст пакета должен храниться в файлах,
как SQL скрипты. Спецификацию и тело, как правило, хранят в разных файлах,
с расширениями “.sql”. Можно использовать любые другие расширения, например
“.pks” для спецификации и “.pkb” для тела — подобные расширения также часто
используются. Когда нужно изменить пакет, код в этих файлах меняется,
после чего эти скрипты перезапускаются.

Если нужно произвести перекомпиляцию пакета, который уже создан,
без его изменения, можно воспользоваться командой alter package:

alter package pck_utils compile package;

alter package pck_utils compile specification;

alter package pck_utils compile body;

Изменение пакета

Изменение пакета производится путём перекомпиляции
его спецификации или тела(используя create or replace).

Если спецификация пакета не изменяется, а только её реализация,
то её пересоздавать не нужно. Если же меняется и спецификация,
то придётся перекомпилировать и спецификацию, и тело.

Пакеты без тела

Можно создать пакет, который не будет иметь тела.
Как правило, это пакеты, которые содержат публичные
константы, типы или переменные:

create or replace package pck_user_gl as

-- Статусы пользователей
active constant number := 0;
deleted constant number := 1;
paused constant number := 2;

end pck_user_gl;

В данном пакете содержатся константы для
описания статусов пользователей.
Теперь мы можем обращаться к статусам, объявленным в пакете:

-- Выведет три строки:
-- 0
-- 2
-- 1
begin
    dbms_output.put_line(pck_user_gl.active);
    dbms_output.put_line(pck_user_gl.paused);
    dbms_output.put_line(pck_user_gl.deleted);
end;

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

Перегрузка процедур и функций

Внутри пакета можно объявить несколько функций
или процедур с одним и тем же именем, но с разной
сигнатурой. Подобная возможность в языках программирования
называется перегрузкой(overloading). Простейший пример
перегруженных функций(процедур) — это dbms_output.put_line.
Мы можем вызывать данную процедуру как со строками, так и с
датами или числами. Создадим свой пакет для вывода информации
на экран, только с более коротким именем процедуры, для удобной
работы:

create or replace package pck_output as

procedure print(v varchar2);
procedure print(v number);
procedure print(v date);

end pck_output;

В спецификации мы объявили три разных сигнатуры — несмотря на то,
что имена у них одинаковые, они отличаются типами принимаемых аргументов.
Теперь создадим тело пакета:

create or replace package body pck_output as

procedure print(v varchar2) is
begin
    dbms_output.put_line(v);
end;

procedure print(v number) is
begin
    dbms_output.put_line(v);
end;

procedure print(v date) is
begin
    dbms_output.put_line(v);
end;

end pck_output;

Скомпилируем пакет и проверим, как он работает:

-- Выведет строки:
-- 19-MAR-22
-- 34.23
-- Hello, World
declare
    v1 date := sysdate;
    v2 number := 34.23;
    v3 varchar2(20) := 'Hello, World';
begin
    pck_output.print(v1);
    pck_output.print(v2);
    pck_output.print(v3);
end;

Функции можно перегружать, если они отличаются

• Количеством аргументов
• Типами аргументов

Имена аргументов при этом не важны — не получится создать две процедуры вида:

procedure proc1(name varchar2);
procedure proc1(username varchar2);

Но зато получится создать такие процедуры:

procedure proc1(name varchar2);

-- Отличается от предыдущей количеством аргументов
procedure proc1(name varchar2, trim: boolean);

-- отличается от предыдущей типами аргументов
procedure proc1(name varchar2, trim: number);

Сессии и состояния

Каждая сессия в БД работает со своей копией пакета в памяти, что
означает, что состояние переменных пакета локально для сессии,
использующей его. Рассмотрим простой пример:

create or replace package pck_test as

current_number number := 10;

end pck_test;

Пакет содержит одну переменную current_number
со значением по-умолчанию, равным 10. Предположим, с пакетом будут
работать две сессии, А и Б:

Сессия А:

begin
    dbms_output.put_line(pck_test.current_number);
end;

Сессия Б:

begin
    dbms_output.put_line(pck_test.current_number);
end;

Результат будет одинаковым в двух сессиях:

10

Теперь изменим значение переменной в первой сессии:

-- Сессия А
begin
    pck_test.current_number := 20;
end;

После чего выведем содержимое переменной в двух сессиях:

-- Сессия А:
begin
    dbms_output.put_line(pck_test.current_number);
end;

-- Сессия Б:
begin
    dbms_output.put_line(pck_test.current_number);
end;

И получим следующий результат:

Сессия А:

20

Сессия Б:

10

Как видно, изменения переменной, произведённые в первой сессии,
не повлияли на значение той же переменной пакета во второй сессии.

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

Сброс состояния означает, что изменения, произведённые с переменными
или курсорами, будут утеряны, и оракл выбросит ошибку с сообщением о том,
что состояние пакета было сброшено. Способы уменьшения вероятности сброса состояния
будут рассмотрены в отдельной части, посвящённой продвинутой работе с пакетами.

Порядок загрузки пакета в память

Помимо создания экземпляра пакета в памяти при первоначальном
обращении к нему, Oracle производит его инициализацию, состоящую
из следующих шагов:

• Присваивание первоначальных значений публичным константам
• Присваивание первоначальных значений публичным переменным
• Запуск блока инициализации

Блок инициализации

Блок инициализации добавляется в конце тела пакета
между ключевым словом begin и конструкцией end package_name, и как правило используется
для присваивания начальных значений переменным пакета. Сам блок является необязательным.
Рассмотрим пример пакета с блоком инициализации:

-- Спецификация пакета
create or replace package pck_init as

init_val number;

procedure say_hello;

end pck_init;
/

-- Тело пакета
create or replace package body pck_init as

procedure say_hello
is
begin
    dbms_output.put_line('Привет, Мир!');
end;

--Секция инициализации
begin
    dbms_output.put_line('Инициализация пакета');
    init_val := 23;
end pck_init;

Теперь вызовем процедуру say_hello несколько раз подряд:

begin
    pck_init.say_hello();
    pck_init.say_hello();
    pck_init.say_hello();
end;

Результат:

Инициализация пакета
Привет, Мир!
Привет, Мир!
Привет, Мир!

Как видно, при первом обращении к пакету был вызван блок
инициализации, причём до выполнения процедуры say_hello.
При последующих обращениях к пакету инициализация не
производится. Инициализация выполняется при любом первичном
обращении к пакету, это не обязательно должна быть процедура
или функция:

begin
    -- Выведет две строки:
    -- Инициализация пакета
    -- 23
    dbms_output.put_line(pck_init.init_val);
end;

Считается правилом хорошего тона производить инициалиацию всех
переменных именно в блоке инициализации, а не при объявлении переменных.
Одна из причин — тот факт, что ошибки, которые могут возникнуть при инициализации переменных,
можно отловить только здесь. Исключения будут рассмотрены позже, но для быстрого ознакомления
приведём пример:

create or replace package pck_test is

min_age number(2) := 123;
default_name varchar2(3 char) := 'User';

end pck_test;
/

При создании пакета не будет выдани никаких сообщений об ошибке.
Но если мы попробуем вывести на экран значение default_name:

begin
    dbms_output.put_line(pck_test.default_name);
end;

То получим ошибку(Во время выполнения!) ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512.
А при использовании блока инициализации ошибку можно отловить:

create or replace package pck_test is

min_age number(2);
default_name varchar2(3 char);

end pck_test;
/

create or replace package body pck_test is

begin
    min_age := 123;
    default_name := 'User';

exception
    when value_error then
        min_age := 18;
        default_name := 'NIL';
end pck_test;
/

В этот раз мы имеем возможность отловить все ошибки на стадии инициализации
пакета и предпринять нужные меры. Посмотрим, как это работает:

-- Выведет две строки:
-- NIL
-- 18
begin
    dbms_output.put_line(pck_test.default_name);
end;

Ещё о функциях в пакетах

Forward declaration

Порядок приватных функций/процедур в теле пакета имеет значение.
Если функция A использует функцию B, то функция B к тому моменту
должна быть объявлена, то есть находиться выше в коде тела:

create or replace package pck_test as

procedure print_hello;

end pck_test;

create or replace package body pck_test as

procedure print_hello is
begin
    dbms_output.put_line(get_hello_message);
end;

function get_hello_message return varchar2
is
begin
    return 'Hello, World!';
end;

end pck_test;

Здесь процедура print_hello выводит на экран текст сообщения, который
возвращает функция get_hello_message, но сама процедура объявлена раньше, чем функция.
При компиляции тела мы получим ошибку PLS-00313: 'GET_HELLO_MESSAGE' not declared in this scope — она
не объявлена на момент своего вызова.

Решить эту проблему можно несколькими способами. Во-первых, можно поместить функцию get_hello_message
выше процедуры:

create or replace package body pck_test as

function get_hello_message return varchar2
is
begin
    return 'Hello, World!';
end;

procedure print_hello is
begin
    dbms_output.put_line(get_hello_message);
end;

end pck_test;

Во-вторых, можно добавить функцию get_hello_message в спецификацию пакета,
и тогда порядок внутри тела не будет ни на что влиять:

create or replace package pck_test as

procedure print_hello;

function get_hello_message return varchar2;

end pck_test;

Но объявлять всё в спецификации — тоже не выход; Некоторые функции не должны
быть доступны для вызова всеми желающими. В таком случае можно использовать
так называемую Forward declaration — отделить описание функций от их реализации:

create or replace package body pck_test as

-- Объявляем функцию, но не указываем её реализацию
function get_hello_message return varchar2;

procedure print_hello is
begin
    -- Ошибки не будет, функция get_hello_message
    -- уже объявлена
    dbms_output.put_line(get_hello_message);
end;

-- Реализация функции get_hello_message
function get_hello_message return varchar2
is
begin
    return 'Hello, World!';
end;

end pck_test;

Вызов функций в SQL запросах

Все функции, которые используются в SQL
запросах, должны быть созданы на уровне схемы, то есть
либо быть созданными как отдельные функции(create function),
либо быть объявленными в спецификации пакета. Рассмотрим на примере:

create or replace package pck_test as

procedure print_hello;

end pck_test;

create or replace package body pck_test as

function get_hello_message return varchar2
is
begin
    return 'Hello, World!';
end;

procedure print_hello is
    l_msg varchar2(50);
begin
    select get_hello_message() into l_msg
    from dual;
    
    dbms_output.put_line(l_msg);
end;

end pck_test;

При компиляции тела пакета мы получим ошибку PLS-00231: function 'GET_HELLO_MESSAGE' may not be used in SQL.
Это потому, что мы вызываем функцию из SQL, но данная функция не объявлена в спецификации пакета. Если
мы добавим её сигнатуру в спецификацию, то все будет работать:

create or replace package pck_test as

procedure print_hello;

function get_hello_message return varchar2;

end pck_test;

begin
    -- Выведет "Hello, World!"
    pck_test.print_hello;
end;

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

Для чего нужны пакеты в PL/SQL?

Пакеты — очень важная составная часть языка PL/SQL, краеугольный камень любого сложного проекта. Чтобы это понять, необходимо рассмотреть основные преимущества пакетов:

•  Упрощение сопровождения и расширения приложений. По мере того как все большая часть кодовой базы перемещается в режим сопровождения, качество при­ложений PL/SQL определяется не только их производительностью, но и простотой сопровождения. С этой точки зрения пакеты играют исключительно важную роль, поскольку они обеспечивают инкапсуляцию кода (в частности, они позволяют скрыть команды SQL за интерфейсом процедур), дают возможность определять кон­станты для литералов и «волшебных» чисел, и группировать логически связанные функции. Пакетный подход к проектированию и реализации сокращает количество потенциальных сбоев в приложениях.
•  Повышение производительности приложений. Во многих ситуациях использова­ние пакетов повышает производительность и эффективность работы приложений. Определение постоянных структур данных уровня пакета позволяет кэшировать статические значения из базы данных. Это дает возможность избежать повторных запросов, а следовательно, значительно ускорить получение результата. Кроме того, подсистема управления памятью Oracle оптимизирована для доступа к откомпили­рованному коду пакетов.
•  Исправление недостатков приложений или встроенных элементов. Некоторые из существующих программных компонентов Oracle имеют недостатки; в частности, не лучшим образом реализованы важнейшие функции встроенных пакетов UTL_FILE и DBMS_OUTPUT. Мириться с ними не обязательно; можно разработать собственный пакет на базе существующего, исправив как можно больше проблем. Например, сценарий do.pkg, предоставляет замену для встроенной функции DBMS_OUTPUT.PUT_LINE с добавлением перегрузки для типа XMLType. Подобного результата можно достичь и с помощью отдельных функций и процедур PL/SQL, но решение с пакетами более предпочтительно.
•  Снижение необходимости в перекомпиляции кода. Пакет обычно состоит из двух элементов: спецификации и тела. Внешние программы (не определенные в пакете) могут вызывать только программы, перечисленные в спецификации. Изменение и перекомпиляция тела пакета не отражается на работе этих внешних программ. Снижение необходимости в перекомпиляции кода является важнейшим фактором администрирования больших объемов программного кода приложений. Концепция пакетов очень проста. Единственная сложность заключается в том, чтобы научиться эффективно применять в приложениях их богатые возможности. В этой статье мы начнем с рассмотрения простого пакета; вы увидите, что основные преимущества пакетов проявляются даже в тривиальном коде. Затем будет рассмотрен специальный синтаксис, используемый при определении пакетов.

Прежде чем приступать к рассмотрению преимуществ пакетов и описанию синтак­сиса их определения, необходимо сделать одно важное замечание. Всегда стройте приложение на основе пакетов; избегайте отдельных процедур и функций. Даже если вам сейчас кажется, что для реализации определенной возможности доста­точно одной процедуры или функции, в будущем к ней почти наверняка добавятся еще несколько. Когда вы поймете, что их лучше объединить в пакет, придется ис­кать все вызовы процедур и функций и добавлять к ним префикс с именем пакета. Используйте пакеты с самого начала, избавьте себя от будущих проблем!

Демонстрация возможностей пакетов PL/SQL

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

PROCEDURE process_employee (
   employee_id_in IN employees.employee_id%TYPE)
IS
   l_fullname VARCHAR2(100);
BEGIN
   SELECT last_name || ',' || first_name 
   INTO l_fullname 
   FROM employees
   WHERE employee_id = employee_id_in;
END;

Однако этот вроде бы тривиальный код обладает рядом скрытых недостатков:

•  Длина переменной l_fullname жестко закодирована. Поскольку полное имя — про­изводное значение, которое строится конкатенацией содержимого двух столбцов, лучше так не делать. Если длина столбцов last_name и/или first_name будет увели­чена, код процедуры придется изменять.
•  Жестко закодировано правило составления полного имени. Чем это плохо? Тем, что если через какое-то время пользователь захочет получить полное имя в формате «ИМЯ ФАМИЛИЯ», вам придется производить замену во многих местах кода.
•  Наконец, этот очень распространенный запрос может встречаться в нескольких ме­стах приложения. Дублирование кода SQL затрудняет сопровождение приложения и его оптимизацию.

Приложения должны строиться таким образом, чтобы избежать жесткого кодирования подобных элементов. Определение типа данных для полного имени, представление, за­прос к базе данных и т. п. должны кодироваться один раз в строго определенном месте и быть доступны из любой точки приложения. Таким местом и является пакет. Рассмотрим следующую спецификацию пакета:

PACKAGE employee_pkg
AS
   SUBTYPE fullname_t IS	VARCHAR2 (200);

   FUNCTION fullname (
      last_in employees.last_name%TYPE,
      first_in employees.first_name%TYPE)
      RETURN fullname_t;

   FUNCTION fullname (
      employee_id_in IN employees.employee_id%TYPE)
      RETURN fullname_t;
END employee_pkg;

Фактически здесь перечисляются различные элементы, которые должны использоваться разработчиками. Важнейшие элементы кода представлены в следующей таблице.

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

DECLARE
   l_name employee_pkg.fullname_t; 
   employee_id_in employees.employee_id%TYPE := 1;
BEGIN
   l_name := employee_pkg.fullname (employee_id_in);
END;

Переменная l_name объявляется с новым типом данных, а для присваивания ей нужного значения вызывается соответствующая функция этого же пакета. Таким образом, формула построения полного имени и SQL-запрос вынесены из кода приложения в специальный «контейнер» для всей функциональности, относящейся к обработке данных о сотруд­никах. Код стал проще и лаконичнее. Если потребуется изменить формулу построения полного имени или увеличить размер его типа данных, достаточно внести соответству­ющие изменения в спецификацию или тело пакета и перекомпилировать его код.

Реализация employee_pkg выглядит так:

PACKAGE BODY employee_pkg
AS
   FUNCTION fullname (
      last_in employee.last_name%TYPE,
      first_in employee.first_name%TYPE
   )
      RETURN fullname_t
   IS
   BEGIN
      RETURN last_in || ', ' || first_in;
   END;

   FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
      RETURN fullname_t
   IS
      retval fullname_t;
   BEGIN
      SELECT fullname (last_name, first_name) INTO retval
      FROM employee
      WHERE employee_id = employee_id_in;

      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN NULL;

      WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop;
   END;
END employee_pkg;

В следующей таблице перечислены важные элементы этого кода.

Если теперь пользователь потребует изменить формат представления полного имени, нам не придется искать по всему приложению все вхождения || ', ' || — для установки нового формата достаточно модифицировать в пакете employee_pkg функцию fullname.

Основные концепции пакетов

Прежде чем переходить к подробному изучению синтаксиса и структуры пакетов, сле­дует изучить некоторые концепции пакетов:

•  Сокрытие информации. Сокрытие информации о системе или приложении обычно преследует две цели. Во-первых, возможности человека по работе со сложными системами ограничены. Исследования показали, что у среднего «мозга» при запо­минании даже семи (плюс/минус двух) элементов в группе возникают проблемы. Таким образом, пользователь (или разработчик) освобождается от необходимости вникать в ненужные подробности и может сосредоточиться на действительно важ­ных аспектах. Во-вторых, сокрытие информации препятствует доступу к закрытым сведениям. Например, разработчик может вызвать в своем приложении готовую функцию для вычисления некоторого значения, но при этом формула вычислений может быть секретной. Кроме того, в случае изменения формулы все модификации будут вноситься только в одном месте.
•  Общие и приватные элементы. Концепция общих и приватных элементов тесно связана с концепцией сокрытия информации. Общедоступный код определяется в спецификации пакета и доступен любой схеме, обладающей для этого пакета привилегией EXECUTE. Приватный код виден только в пределах пакета. Внешние про­граммы, работающие с пакетом, не видят приватный код и не могут использовать его.

Приступая к разработке пакета, вы решаете, какие из его элементов будут общими, а ка­кие — приватными. Кроме того, тело пакета можно скрыть от других схем/разработчиков. В таком случае пакет используется для сокрытия деталей реализации программ. Это особенно полезно для изоляции переменных компонентов приложения — фрагментов кода, зависящих от платформы, часто меняющихся структур данных и временных об­ходных решений.

На ранних стадиях развития программы в теле пакета также могут реализоваться в виде «заглушек» с минимальным объемом кода, необходимым для компиляции пакета. Этот прием позволяет сосредоточиться на интерфейсах программы и их взаимных связях.

• Спецификация пакета. Она содержит определения всех общедоступных элементов пакета, на которые можно ссылаться извне. Спецификация напоминает большой раздел объявлений; она не содержит блоков PL/SQL или исполняемого кода. Из хо­рошо спроектированной спецификации разработчик может получить всю необходи­мую для использования пакета информацию и ему никогда не придется заглядывать «за интерфейс» (то есть в тело пакета, содержащее реализацию его компонентов).
• Тело пакета. Здесь находится весь код, который необходим для реализации элемен­тов, определенных в спецификации пакета. Тело может содержать отсутствующие в спецификации личные элементы, на которые нельзя ссылаться извне пакета, в частности объявления переменных и определения пакетных модулей. Кроме того, в теле пакета может находиться исполняемый (инициализационный) раздел, который выполняется только один раз для инициализации пакета.
• Инициализация. Концепция инициализации хорошо известна любому програм­мисту, однако в контексте пакетов она имеет особое значение. В данном случае инициализируется не отдельная переменная, а весь пакет путем выполнения кода произвольной сложности. При этом Oracle следит за тем, чтобы пакет инициализи­ровался только один раз за сеанс.
• Постоянство в течение сеанса. Концепция постоянства (или сохраняемости) тоже хорошо знакома программистам. Когда вы подключаетесь к Oracle и выполняете программу, присваивающую значение переменной уровня пакета (то есть пере­менной, объявленной в пакете вне содержащихся в нем программ), эта переменная сохраняет значение в течение всего сеанса, даже если выполнение присвоившей его программы завершается.

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

Именно пакеты обеспечивают поддержку структур данных с сеансовым постоянством в языке PL/SQL.

Графическое представление приватности

Различия между общедоступными и приватными элементами пакета дают разработчикам PL/SQL беспрецедентные средства управления структурами данных и программами. Грэди Буч предложил визуальное средство описания этих аспектов пакета (которое было вполне естественно названо диаграммой Буча).

Взгляните на рис. 1. Обратите внимание на надписи «Внутренняя часть» и «Внешняя часть». Первая часть содержит тело пакета (внутренняя реализация пакета), а вторая — все программы, написанные вами и не являющиеся частью пакета (внешние программы).

Рис. 1. Диаграмма Буча с общедоступными и приватными элементами пакета

Несколько выводов, следующих из диаграммы Буча:

•  Внешние программы не могут пересекать границу внутренней реализации; иначе говоря, внешняя программа не может обращаться или вызывать элементы, опре­деленные в теле пакета. Это приватные элементы, невидимые за пределами пакета.
•  Элементы, определенные в спецификации пакета («Внешняя часть» на диаграмме), располагаются по обе стороны от границы между внутренней и внешней частью. Такие программы могут вызываться внешней программой (из внешней части), они доступны для приватных программ и в свою очередь могут вызывать или обращаться ко всем остальным элементам пакета.
•  Общедоступные элементы пакета предоставляют единственный путь к внутренней части. В этом отношении спецификация пакета действует как управляющий меха­низм для пакета в целом.
•  Если окажется, что объект, ранее бывший приватным (например, модуль или кур­сор), должен стать общедоступным, просто добавьте его в спецификацию и пере­компилируйте пакет. После этого объект станет доступным за пределами пакета.

В этой главе мы обсудим пакеты в PL / SQL. Пакеты – это объекты схемы, которые группируют логически связанные типы, переменные и подпрограммы PL / SQL.

Пакет будет состоять из двух обязательных частей –

• Спецификация упаковки
• Тело пакета или определение

Спецификация упаковки

Спецификация – это интерфейс к пакету. Он просто ОБЪЯВЛЯЕТ типы, переменные, константы, исключения, курсоры и подпрограммы, на которые можно ссылаться извне пакета. Другими словами, он содержит всю информацию о содержимом пакета, но исключает код для подпрограмм.

Все объекты, размещенные в спецификации, называются открытыми объектами. Любая подпрограмма, не указанная в спецификации пакета, но закодированная в теле пакета, называется закрытым объектом.

Следующий фрагмент кода показывает спецификацию пакета с одной процедурой. Вы можете иметь много глобальных переменных, определенных и несколько процедур или функций внутри пакета.

CREATE PACKAGE cust_sal AS 
   PROCEDURE find_sal(c_id customers.id%type); 
END cust_sal; 
/

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат –

Package created.

Корпус

Тело пакета содержит коды для различных методов, объявленных в спецификации пакета и других частных объявлений, которые скрыты от кода вне пакета.

Оператор CREATE PACKAGE BODY используется для создания тела пакета. В следующем фрагменте кода показано объявление тела пакета для пакета cust_sal , созданного выше. Я предположил, что у нас уже есть таблица CUSTOMERS, созданная в нашей базе данных, как упомянуто в главе PL / SQL – переменные .

CREATE OR REPLACE PACKAGE BODY cust_sal AS  
   
   PROCEDURE find_sal(c_id customers.id%TYPE) IS 
   c_sal customers.salary%TYPE; 
   BEGIN 
      SELECT salary INTO c_sal 
      FROM customers 
      WHERE id = c_id; 
      dbms_output.put_line('Salary: '|| c_sal); 
   END find_sal; 
END cust_sal; 
/

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат –

Package body created.

Использование элементов пакета

Доступ к элементам пакета (переменным, процедурам или функциям) осуществляется с использованием следующего синтаксиса:

package_name.element_name;

Учтите, что мы уже создали вышеупомянутый пакет в нашей схеме базы данных, следующая программа использует метод find_sal пакета cust_sal –

DECLARE 
   code customers.id%type := &cc_id; 
BEGIN 
   cust_sal.find_sal(code); 
END; 
/

Когда приведенный выше код выполняется в приглашении SQL, он предлагает ввести идентификатор клиента, а когда вы вводите идентификатор, он отображает соответствующую зарплату следующим образом:

Enter value for cc_id: 1 
Salary: 3000 

PL/SQL procedure successfully completed. 

пример

Следующая программа предоставляет более полный пакет. Мы будем использовать таблицу CUSTOMERS, хранящуюся в нашей базе данных, со следующими записями:

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 | 
|  2 | Khilan   |  25 | Delhi     |  3000.00 | 
|  3 | kaushik  |  23 | Kota      |  3000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9500.00 | 
|  6 | Komal    |  22 | MP        |  5500.00 | 
+----+----------+-----+-----------+----------+

Спецификация пакета

CREATE OR REPLACE PACKAGE c_package AS 
   -- Adds a customer 
   PROCEDURE addCustomer(c_id   customers.id%type, 
   c_name  customerS.No.ame%type, 
   c_age  customers.age%type, 
   c_addr customers.address%type,  
   c_sal  customers.salary%type); 
   
   -- Removes a customer 
   PROCEDURE delCustomer(c_id  customers.id%TYPE); 
   --Lists all customers 
   PROCEDURE listCustomer; 
  
END c_package; 
/

Когда приведенный выше код выполняется в приглашении SQL, он создает указанный выше пакет и отображает следующий результат:

Package created.

Создание тела пакета

CREATE OR REPLACE PACKAGE BODY c_package AS 
   PROCEDURE addCustomer(c_id  customers.id%type, 
      c_name customerS.No.ame%type, 
      c_age  customers.age%type, 
      c_addr  customers.address%type,  
      c_sal   customers.salary%type) 
   IS 
   BEGIN 
      INSERT INTO customers (id,name,age,address,salary) 
         VALUES(c_id, c_name, c_age, c_addr, c_sal); 
   END addCustomer; 
   
   PROCEDURE delCustomer(c_id   customers.id%type) IS 
   BEGIN 
      DELETE FROM customers 
      WHERE id = c_id; 
   END delCustomer;  
   
   PROCEDURE listCustomer IS 
   CURSOR c_customers is 
      SELECT  name FROM customers; 
   TYPE c_list is TABLE OF customers.Name%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
   BEGIN 
      FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter)); 
      END LOOP; 
   END listCustomer;
   
END c_package; 
/

Приведенный выше пример использует вложенную таблицу . Мы обсудим концепцию вложенной таблицы в следующей главе.

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат –

Package body created.

Использование пакета

Следующая программа использует методы, объявленные и определенные в пакете c_package .

DECLARE 
   code customers.id%type:= 8; 
BEGIN 
   c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500); 
   c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500); 
   c_package.listcustomer; 
   c_package.delcustomer(code); 
   c_package.listcustomer; 
END; 
/  

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат –