Разработка серверного кода

Создание хранимых процедур и функций

Хранимая процедура или функция есть объект реляционной базы данных, который является поименнованным набором операторов SQL и, в случае СУБД Oracle, набором операторов PL/SQL, который может быть скомпилирован и необязательно сохранен в базе данных. Если процедура сохраняется в базе данных, то она называется хранимой процедурой или функцией. Описание хранимых процедур и функций хранится в словаре данных реляционной базы данных.

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

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

Хранимые процедуры и функции, как объекты базы данных, создаются командой CREATE и уничтожаются командой DROP. Команда создания хранимой процедуры имеет следующий синтаксис:

CREATE [OR REPLACE] PROCEDURE [имя схемы].имя процедуры
[имя [(параметр [, параметр, ...])] {IS|AS}
программа на PL/SQL;

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

При описании переменных хранимой процедуры ключевое слово DECLARE не используется. Блок описания переменных начинается сразу после ключевого слова IS или AS.

Пример.

PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS
BEGIN
   INSERT INTO dept
      VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
END create_dept;

Исполнение созданной процедуры может быть выполнено оператором EXEC PL/SQL, как показано ниже:

EXEC ;

Команда создания хранимой процедуры имеет следующий синтаксис:

CREATE [OR REPLACE] FUNCTION [имя схемы].имя функции
[имя [(параметр [, параметр, ...])] 
RETURN тип данных
{IS|AS}
программа на PL/SQL;

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

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

CREATE OR REPLACE FUNCTION emp_cnts (data1 IN date, data2 IN date)
RETURN Integer
IS
I_count number:=0;
BEGIN
SELECT COUNT(*) INTO i_count FRON employee
WHERE hiredate BETWEEN date1 AND date2;
RETURN i_count;
END;

Вызвать созданную хранимую функцию можно следующим образом:

EXEC DBMS_OUTPUT.PUT_LINE(emp_cnts('01-may-02', ''01-jul-02'));

Для уничтожения хранимой процедуры или функции в базе данных используется команда DROP в формате DROP [имя схемы].имя процедуры;

Или DROP [имя схемы].имя функции;

Особенности использования процедур и функций в СУБД 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 возможностями процедурной обработки;
• обеспечение модульности, т.е. позволяют использовать поэтапный подход обработки данных из базы данных на основе хорошо определенной бизнес-логики;
• создание серверного кода базы данных с целью увеличения производительности за счет сокращения сетевого трафика и накладных расходов на компиляцию кода.