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

Обновлено: 04.07.2024

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

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

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

Определение спецификации пакета выполняется оператором CREATE PACKAGE , который может иметь следующее формальное описание:

Определение тела пакета выполняется оператором CREATE PACKAGE BODY , который может иметь, с некоторыми сокращениями, следующее формальное описание:

На общедоступные элементы пакета - типы, переменные и методы - можно ссылаться из триггеров, хранимых подпрограмм или OCI -приложений, используя следующий синтаксис: package_name.item_name .

Вызов пакетной процедуры из встроенного SQL может быть реализован в анонимном блоке PL/SQL, для выполнения которого используется SQL-оператор EXECUTE .

Подпрограммы

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

Язык PL/SQL позволяет создавать подпрограммы и как процедуры, и как функции PL/SQL .

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

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

Для того чтобы самостоятельную подпрограмму можно было вызвать извне она должна храниться в базе данных. Такие подпрограммы называются хранимыми процедурами или хранимыми функциями. Для их создания применяются SQL- операторы CREATE PROCEDURE и CREATE FUNCTION .

Определение процедуры может иметь следующее формальное описание:

Параметры в списке параметров определяются как:

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

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

Для определения соответствия между формальными и фактическими параметрами предусмотрены два типа нотаций:

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

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

Процедура имеет две части:

спецификацию , начинающуюся ключевым словом PROCEDURE и завершающуюся именем процедуры или списком параметров;
тело процедуры , начинающееся ключевым словом IS и завершающееся ключевым словом END .

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

Определение функции может иметь следующее формальное описание:

Параметры в списке параметров определяются как:

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

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

Рекурсивные и взаимно рекурсивные вызовы подпрограмм

Рекурсивным вызовом называется вызов подпрограммы из тела этой же подпрограммы .

При каждом рекурсивном вызове :

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

Рассмотрим в качестве примера рекурсивных вызовов создание последовательности Фибоначчи ( 1, 1, 2, 3, 5, 8, 13, 21, .. .), в которой каждый следующий элемент является суммой двух предыдущих.

Следующая функция fibonati1 реализует формирование последовательности Фибоначчи с применением рекурсии:

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

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

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

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

В этом уроке вы узнаете

Компоненты пакетов

Пакет PL / SQL состоит из двух компонентов.

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

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

Ниже приведены некоторые характеристики спецификации пакета.

Синтаксис

Приведенный выше синтаксис показывает создание спецификации пакета.

Корпус

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

Ниже приведены характеристики корпуса упаковки.

Синтаксис:

Приведенный выше синтаксис показывает создание тела пакета.

Теперь мы увидим, как ссылаться на элементы пакета в программе.

Ссылающиеся элементы пакета

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

Все общедоступные элементы пакета могут быть переданы путем вызова имени пакета, за которым следует имя элемента, разделенное точкой, т.е. «<имя_пакета>. <Имя_элемента>».

Открытая переменная пакета также может быть использована таким же образом для назначения и извлечения значений из них, то есть «<имя_пакета>. <Имя_переменной>».

Создать пакет в PL / SQL

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

Oracle предоставляет возможность инициализировать элементы пакета или выполнять какие-либо действия во время создания этого экземпляра с помощью «Инициализация пакета».

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

Синтаксис

Приведенный выше синтаксис показывает определение инициализации пакета в теле пакета.

Форвардные декларации

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

Синтаксис:

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

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

В отличие от других элементов, нужно соблюдать осторожность при использовании курсоров внутри пакета.

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

перегрузка

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

Пример 1 : В этом примере мы собираемся создать пакет для получения и установки значений информации о сотруднике в таблице «emp». Функция get_record возвращает вывод типа записи для данного номера сотрудника, а процедура set_record вставляет запись типа записи в таблицу emp.

Шаг 1) Создание спецификации пакета

Вывод:

Код Объяснение

Строка кода 1-5 : создание спецификации пакета для guru99_get_set с одной процедурой и одной функцией. Эти два в настоящее время являются открытыми элементами этого пакета.

Шаг 2) Пакет содержит тело пакета, в котором будут определены все действительные процедуры и функции. На этом шаге создается тело пакета.

Вывод:

Код Объяснение

Строка кода 7 : создание тела пакета.
Строка кода 9-16 : определение элемента set_record, который объявлен в спецификации. Это то же самое, что и определение автономной процедуры в PL / SQL.
Строка кода 17-24: определение элемента get_record. Это то же самое, что и определение автономной функции.
Строка кода 25-26: определение части инициализации пакета.

Шаг 3) Создание анонимного блока для вставки и отображения записей, ссылаясь на созданный выше пакет.

Вывод:

Объяснение кода:

Строка кода 34-37: заполнение данных для переменной типа записи в анонимном блоке для вызова элемента set_record пакета.
Строка кода 38: был сделан вызов set_record пакета guru99_get_set. Теперь пакет создается и сохраняется до конца сеанса.
Часть инициализации пакета выполняется, так как это первый вызов пакета.
Запись вставляется элементом set_record в таблицу.
Строка кода 41: вызов элемента get_record для отображения сведений о введенном сотруднике.
Пакет передается во второй раз во время вызова get_record. Но на этот раз часть инициализации не выполняется, поскольку пакет уже инициализирован в этом сеансе.
Строка кода 42-45: печать сведений о сотруднике.

Зависимость в пакетах

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

Информация о пакете

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

Ниже в таблице приведены таблица определения данных и информация о пакете, которая доступна в таблице.

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

Резюме

Теперь мы изучили пакеты на PL / SQL, и теперь вы сможете работать следующим образом.

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

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

Спецификация пакета PL/SQL

Спецификация пакета содержит список всех доступных элементов и предоставляет разработчику информацию, необходимую для использования пакета в приложениях. Ее часто называют программным интерфейсом — API (Application Programming Interface). Чтобы узнать, как применять описанные в спецификации элементы, разработчику не нужно изучать код, находящийся в теле пакета.

При разработке спецификации пакета необходимо руководствоваться следующими правилами:

Элементы практически любого типа — числа, исключения, типы, коллекции и т. д. — могут объявляться на уровне пакета (то есть такие элементы не принадлежат конкретным процедурам или функциям этого пакета). Такие данные называются данными уровня пакетов. В общем случае объявлять переменные в спецификациях пакетов не рекомендуется, хотя объявления констант на уровне пакета вполне приемлемы. В пакете (как в спецификации, так и в теле) нельзя объявлять курсорные переменные (типа REF CURSOR ), поскольку они не могут сохранять свое значение на протяжении сеанса.
В спецификации допускается объявление типов для любых структур данных: коллекций, записей или курсорных переменных.
В спецификации можно объявлять процедуры и функции, но в ней должны быть указаны только их заголовки (часто определения процедуры или функции до ключевого слова IS или AS). Заголовок должен завершаться символом « ; » (точка с запятой).
В спецификацию пакета могут включаться явные курсоры. Они могут быть представлены в одной из двух форм: SQL-запрос либо является частью объявления курсора, либо скрывается в теле пакета (тогда в объявлении присутствует только предложение RETURN ). Эта тема подробно рассматривается в разделе «Пакетные курсоры».
Если в спецификации пакета объявляются процедуры или функции либо пакетный курсор без запроса, то тело пакета должно включать реализацию этих элементов.
Спецификация пакета может содержать условие AUTHID , определяющее, как будут разрешаться ссылки на объекты данных: в соответствии с привилегиями владельца пакета ( AUTHID DEFINER ) или того, кто его вызывает ( AUTHID CURRENTJJSER ).
После команды END в конце спецификации пакета можно разместить необязательную метку, идентифицирующую пакет:

Для демонстрации этих правил рассмотрим простую спецификацию пакета:

Как видите, пакет имеет почти такую же структуру спецификации, как раздел объявлений блока PL/SQL. Единственное отличие заключается в том, что спецификация не может содержать кода реализации.

Тело пакета

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

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

Со структурной точки зрения тело пакета очень похоже на определение процедуры. Несколько правил, специфических для тел пакетов:

Тело пакета может содержать раздел объявлений, исполняемый раздел и раздел исключения. Раздел объявлений содержит полную реализацию всех курсоров и программ, определяемых в спецификации, а также определение всех приватных элементов (не указанных в спецификации). Раздел объявлений может быть пустым — при условии, что в теле пакета присутствует инициализационный раздел.
Исполняемый раздел пакета также называется инициализационным разделом; он содержит дополнительный код, выполняемый при инициализации пакета в сеансе. Эта тема будет рассмотрена в следующем разделе.
В разделе исключений обрабатываются все исключения, инициированные в инициализационном разделе. Раздел исключений может располагаться в конце тела пакета только в том случае, если вы определили инициализационный раздел.
Тело пакета может иметь следующую структуру: только раздел объявлений; только исполняемый раздел; исполняемый раздел и раздел исключений; раздел объявлений, исполняемый раздел и раздел исключений.
Секция AUTHID не может входить в тело пакета; она должна размещаться в спецификации пакета. Все, что объявлено в спецификации, может использоваться в теле пакета.
Для тела и спецификации пакета действуют одни правила и ограничения объявления структур данных — например, невозможность объявления курсорных переменных.
За командой END тела пакета может следовать необязательная метка с именем пакета: END my_package ;

Ниже приведена моя реализация тела favorites_pkg :

Другие примеры тел пакетов приведены в разделе «Когда используются пакеты».

Инициализация пакетов

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

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

Oracle выполняет все эти действия только один раз за сеанс и только тогда, когда возникнет непосредственная необходимость в этой информации.

Пакет может быть повторно инициализирован в ходе сеанса, если он был перекомпилирован с момента последнего использования или был выполнен сброс состояния всего сеанса, на что указывает ошибка ORA-04068.

Инициализационный раздел пакета составляют все операторы, находящиеся между ключевым словом BEGIN (вне определений процедур и функций) и ключевым словом END , завершающим тело пакета. Например, инициализационный раздел пакета favorites_pkg может выглядеть так:

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

Выполнение сложной логики инициализации

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

Логика, необходимая для назначения значений по умолчанию, может быть слишком сложной для использования в конструкциях значений по умолчанию.
Если при присваивании значения по умолчанию инициируется исключение, оно не может быть перехвачено в границах пакета; это исключение передается наружу необработанным. Эта тема более подробно рассматривается далее в разделе «Ошибки при инициализации».

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

Кэширование статической сеансовой информации

Другая причина для включения инициализационного раздела в пакет — кэширование статической информации, то есть остающейся неизменной на протяжении сеанса. Если значения данных не изменяются, зачем мириться с лишними затратами на запросы или повторное вычисление этих данных?

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

При работе с кэшированными пакетными данными приходится учитывать важный компромисс между затратами памяти и вычислительных мощностей. Кэшируя данные в пакетных переменных, можно улучшить время выборки данных. Для этого данные размещаются «ближе» к пользователю, в области PGA каждого сеанса. При 1000 сеансах в системе существует 1000 копий кэшированных данных. Кэширование снижает нагрузку на процессор, но увеличивает затраты памяти — причем иногда весьма значительно.

За дополнительной информацией по этой теме обращайтесь к разделу «Кэширование статических данных сеанса для ускорения работы приложения».

Предотвращение побочных эффектов при инициализации

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

Если ваши требования к инициализации отличны от представленных нами, рассмотрите альтернативу для инициализационного раздела — например, сгруппируйте стартовые команды в процедуре приложения. Присвойте процедуре содержательное имя (например, init_environment); затем в нужной точке процесса инициализации вызовите процедуру init_environment для настройки сеанса.

Ошибки при инициализации

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

Допустим, я подключаюсь к SQL*Plus и пытаюсь выполнить функцию valerr.get (первый раз в этом сеансе). Вот что я увижу:

Иначе говоря, попытка объявления переменной v для присваивания значения «ABC» приводит к исключению VALUE_ERROR . Раздел исключений в конце пакета не перехватывает ошибку; он может перехватывать только те ошибки, которые инициируются в самом инициализационном разделе. Таким образом, исключение остается необработанным. Однако следует заметить, что при повторном вызове этой функции в сеансе ошибка уже не выдается:

Как интересно! Строка «Before I show you v. » вообще не выводится; более того, эта команда не выполняется. Ошибка происходит при первом вызове пакетной функции, но не при втором и всех последующих вызовах. Перед нами одна из классических «невоспроизводимых ошибок», а в мире PL/SQL это типичная причина подобных проблем: сбой в ходе инициализации пакета.

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

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

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

1- Введение

PL/SQL (Procedural Language/Structured Query Language) это процедурно-ориентированный язык программирования использующийся для Oracle SQL. Является расширением Oracle SQL.

PL/SQL включает компоненты процедурно-ориентированного языка включая условие и цикл. Он позволяет объявлять константы и переменные, процедуры и функции, виды данных и переменные видов данных, и trigger. Он может обрабатывать исключения (ошибки времени запуска) Массив так же поддерживается для использования коллекций в PL/SQL. От версии 8 и далее он включает объектно-ориентированные функции. Может создать такие единицы PL/SQL как процедуры, функции, пакеты, виды данных, triggers, которые хранятся в базе данных для переиспользования приложением, чтобы взаимодействовать с приложениями Oracle.

Примечание: В следующих изображениях я использую инструмент PL/SQL Developer версии 8.x, но нет отличия если вы используете PL/SQL Developer версии 10.x или другую версию.

2- Что нужно чтобы начать с PL/SQL?

Чтобы иметь быстрый доступ к PL/SQL вам нужен инструмент программирования. По моему опыту работы, вы можете использовать PL/SQL Developer, это визуальный инструмент для работы с Oracle и программирования PL/SQL.

Вы можете посмотреть инструкцию установки и конфигурации PL/SQL по ссылке:

3- Обзор PL/SQL

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

Каждая команда SQL заканчивается точкой с запятой (;)
Команды "языка определения данных" (Data Definition Language - DDL) не используются в PL/SQL
Команда SELECT.. INTO возврщает много строк создающих exception ( > 1 строки).
Команда SELECT .. INTO не возвращает строки создающие exception
Команды "языка манипулирования данными" (Data Manipulation Language - DML) может подействовать на многие строки данных.
Использовать оператор := чтобы дать значение переменной.

PL/SQL организован по блокам команд. Один блок команды может содержать подблок команд внутри.

4- Базовые команды PL/SQL

Здесь я представляю обзор команд PL/SQL. Вы поймете больше через примеры в следующих частях.

4.1- Команда If-elsif-else

4.2- Не предопределенный цикл (LOOP)

4.3- Предопределенный цикл (FOR LOOP)

4.4- Цикл while (WHILE)

5- Начать с PL/SQL используя PL/SQL Developer

Для начала вам нужно открыть PL/SQL Developer, и войти как пользователь learningsql:

В PL/SQL Developer создать новое окно SQL:

Кликните на иконку

Читайте также: