Основы проектирования реляционных баз данных

         

Особенности использования процедур и функций в СУБД Oracle


В этом подразделе рассмотрим некоторые особенности использования процедур и функций в PL/SQL:

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

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

DECLARE x1 INTEGER; x2 REAL; PROCEDURE proc1 (p1 INTEGER, p2 REAL) IS BEGIN ... END;

Процедуру Proc1 можно вызвать следующими эквивалентными способами:

BEGIN ... proc1 (x1, x2); -- передача параметров по позиции proc1 (p2 => x2, p1 => x1); -- передача параметров по имени proc1 (p1 => x1, p2 => x2); -- передача параметров по имени proc1 (x1, p2 => x2); -- передача параметров и по -- позиции, и по имени END;

При передаче параметра по позиции компилятор PL/SQL последовательно связывает первый фактический параметр с первым формальным параметром, второй фактический параметр - со вторым формальным параметром и так далее.

При передаче параметра по имени стрелка, называемая оператором связывания (association operator), связывает формальный параметр слева от стрелки с фактическим параметром справа от стрелки, причем порядок следования таких пар не имеет значения.

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

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

Пример. Для нашей учебной базы данных разработаем процедуру создания нового отдела таблице DEPARTAMENT.

PROCEDURE create_dept (new_dname CHAR DEFAULT 'Новый', new_loc CHAR DEFAULT 'Москва') IS BEGIN INSERT INTO departament VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;


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

create_dept; create_dept('Маркетинг'); create_dept('Маркетинг', Черноголовка);

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

Для оптимизации выполнения процедур и функций компилятор PL/SQL может выбирать различные методы передачи параметров (по значению или по ссылке) для различных параметров в одном и том же вызове процедуры или функции. Когда компилятор выбирает метод передачи параметра по значению, то его значение копируется в процедуре или функции. Когда компилятор выбирает метод передачи параметра по ссылке, то адрес фактического параметра передается в процедуру или функцию. Может создаться ситуация когда переменная в процедуре или функции будет иметь два имени - так называемая проблема алиасных имен. В этом случае результат будет неопределенным. Это происходит, когда глобальная переменная передается по адресу, как в примере ниже.

Пример

DECLARE rent REAL; PROCEDURE raise_rent (increase IN OUT REAL) IS BEGIN rent := rent + increase; -- в случае передачи параметра по адресу -- одна и та же переменная будет иметь два имени. END raise_rent; BEGIN ... raise_rent(rent); -- indeterminate

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

Пример. Рассмотрим процедуру реверсирования строки.

DECLARE str VARCHAR2(10); PROCEDURE reverse (in_str VARCHAR2, out_str OUT VARCHAR2) IS BEGIN ... END reverse; ... BEGIN str := 'abcd'; reverse(str, str); -- Не определен

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

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

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

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



Содержание раздела