15. Процедуры, функции, пакеты.

Процедуры (или хранимые процедуры) – это определенный набор инструкций, написанных на языке PL/SQL, и операторов DML. Вызов процедуры приводит к выполнению содержащихся в ней инструкций. Процедура хранится в базе данных, поэтому она и называется хранимой.

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

Создание процедуры:

Создается процедура при помощи оператора CREATE PROCEDURE или CREATE OR REPLACE PROCEDURE.

Входные и выходные параметры:

Между именем входного параметра и его типом можно указывать два специальных слова – IN и/или OUT. Указание IN будет означать, что в данной переменной процедуре было передано входное значение. Указание OUT означает, то эту переменную можно использовать для возврата какого-то значения. Можно также указывать одновременно IN и OUT. Если ничего не указано, то подразумевается IN.

Вызов процедур:

Для вызова процедур используется оператор execute или exec. Например:

execute increase_prices(5);

или

execute increase_prices;

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

Вызов процедуры можно производить не только из среды SQL Plus, но и, естественно, внутри других процедур, функций или триггеров.

Удаление процедур:

Для удаления процедуры необходимо выполнить оператор DROP PROCEDURE имя_процедуры.

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

В секции объявлений объявлена одна переменная max_price типа NUMBER, которая используется для хранения максимальной цены, которую возвращает запрос. Для возврата значения из функции в вызвавшее ее SQL предложение используется оператор RETURN.

Вызов функций:

Как уже было отмечено выше, функции, в отличие от процедур, не могут вызываться при помощи оператора execute, они всегда являются частью более сложного SQL-оператора. Например:

Exec increase_prices(get_max_price/100);

Удаление функций:

Удаление функция производится посредством оператора DROP FUNCTION имя_функции;

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

Пакеты

Пакеты (packages) нужны для совместного хранения полезных функций, процедур, типов записей и курсоров.

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

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

Вызов процедур и функций, входящих в состав пакета

Для обращения к процедурам, переменным и функциям пакета используется оператор

exec имя_пакета.объект_пакета.

Создание спецификации пакета:

CREATE PACKAGE имя_пакета IS [объявления_переменных_и_типов] [спецификации_курсоров] [спецификации_функций_и_процедур] END [имя_пакета]; /

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

Переменные, присутствующие в спецификации пакета, называются переменными пакета (package variables). Они инициализируются только один раз - при первом обращении к нему. Когда производится вызов какой-либо составляющей пакета, ORACLE загружает пакет в память, где он и остается все то время, пока пользователь соединен с базой данных. При наличии нескольких сеансов переменные пакета и их значения становятся разделяемыми; следует также заметить, что обращение к объектам пакета в последующих сеансах происходит быстрее, поскольку пакет уже присутствует в памяти сервера.

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

CREATE OR REPLACE PACKAGE BODY имя_пакета IS [локальные_переменные] [полные_спецификации_курсоров_пакета] [полные_спецификации функций_и_процедур_пакета] BEGIN [выполняемые_операторы] [EXCEPTION] [обработчики_исключений] END [имя_пакета]; /

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

Пример создания и использования пакета

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

create or replace package manager is

-- переменная, содержащая текущее количество менеджеров

count number;

-- прототип функции, возвращающей информацию

-- о лучшем менеджере фирмы

function best RETURN managers%ROWTYPE;

-- прототип процедуры, удаляющей менеджера

procedure del(manr_id number);

END manager;

/

create or replace package body manager is

-- функция возвращает менеджера, получившего наибольшее

-- количество прибыли

function best RETURN managers%ROWTYPE

AS

m managers%ROWTYPE;

BEGIN

select * INTO m

FROM managers WHERE manager_id=1;

RETURN m;

END;

-- процедура удаления менеджера

procedure del(man_id number)

AS

mcount number;

BEGIN

select manager_id INTO mcount FROM managers WHERE manager_id=man_id;

delete from outgoing where manager_id=man_id;

delete from incoming where manager_id=man_id;

delete from managers where manager_id=man_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Raise_Application_Error (-20001, `Such manager is not found`);

END;

-- функция возвращает текущее количество менеджеров

-- (функция видна только из методов пакета)

function mCount

RETURN number

AS

m number;

BEGIN

select count(*) into m from managers;

RETURN m;

END;

-- при инициализации пакета в переменную count помещается

-- текущее число менеджеров

BEGIN

count:=mCount;

END;

/