Хранимые процедуры

Хранимые процедуры и триггеры, о которых уже неоднократно упоминалось в предыдущих лекциях, являются одними из самых мощных средств InterBase для реализации бизнес-логики на стороне сервера. Использование этих инструментов приводит к:

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

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

Хранимые процедуры (Stored Procedures)

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

• выполняемые процедуры, которые либо вообще не возвращают результатов, а только выполняют какие-то действия, либо возвращают только один набор выходных параметров. Такие процедуры вызываются командой EXECUTE PROCEDURE.
• процедуры выборки, которые предназначены для создания многострочных выходных данных, такие процедуры вызываются командой SELECT и используются, как виртуальные таблицы.

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

SET TERM <новый_терминатор><старый_терминатор>

CREATE PROCEDURE Имя_Процедуры
   [(<входной_параметр> <тип_данных> 
[,<входной_параметр> <тип_данных> […]])]
[RETURNS
   (<выходной_параметр> <тип_данных> 
[,<выходной_параметр> <тип_данных> […]])]
AS
   <тело_процедуры>

   <тело_процедуры> = 
[DECLARE [VARIABLE] <переменная><тип_данных>; […]]
BEGIN
   <составной оператор>
END<терминатор>

SET TERM <старый_терминатор><новый_терминатор>

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

Терминаторы

Терминаторами называются символы окончания SQL оператора. Установка терминаторов не относится напрямую к синтаксису хранимых процедур или триггеров, однако попытка создания процедуры без переопределения терминатора, скорее всего, приведет к ошибке. Дело в том, что внутри создаваемой процедуры неоднократно может встречаться символ ";", который по умолчанию является символом конца оператора в языке SQL. В этом случае утилита IBConsole решит, что оператор закончен, и попытается его выполнить. Но процедура еще не будет прочитана до конца, что и приведет к ошибке. Выход: переопределить терминатор. Делается это довольно просто:

SET TERM <новый_терминатор> <старый_терминатор>.

В качестве нового символа окончания вы можете использовать любой редкий символ, например "^" или "&". Затем в теле процедуры может сколько угодно раз встречаться символ ";", SQL при этом не воспримет его как окончание оператора. Завершающую END процедуры следует закрыть установленным вами терминатором, в этом случае процедура будет прочитана IBConsole до конца и выполнена без ошибок. А напоследок вы вновь переопределяете терминатор, устанавливая стандартный символ ";". Например:

SET TERM ^;
CREATE PROCEDURE ……^
SET TERM ;^

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

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

Заголовок

Заголовок процедуры состоит из следующих разделов:

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

   CREATE PROCEDURE Proc1
2. Входные параметры - необязательный элемент. Входные параметры, как и в процедурах Delphi, служат для передачи в процедуру каких-то значений из внешнего приложения, другой процедуры или триггера. При этом типы данных этих параметров могут быть любыми, определенными в SQL, кроме массивов. Параметры объявляются в виде списка " параметр тип ", несколько параметров разделяются запятой. Имена входных параметров процедуры не обязаны соответствовать именам параметров вызывающего приложения, но типы данных должны совпадать. Пример:

   CREATE PROCEDURE Proc2
   (perem1 Integer, perem2 Float, perem3 Date)
3. Выходные параметры - необязательный элемент. Выходные параметры служат для возврата в вызывающее приложение списка результирующих значений. Объявление выходных параметров (если они есть), начинается ключевым словом RETURNS, после которого в скобках параметры перечисляются в виде списка " параметр тип ". Пример:

   CREATE PROCEDURE Proc3
   (vhod_param1 Integer)
   RETURNS (vihod_param1 Double, vihod_param2 Varchar(10))
4. Ключевое слово AS - обязательный элемент, указывающий на окончание заголовка процедуры. Пример:

   CREATE PROCEDURE Proc4
   RETURNS (param char(50))
   AS

Тело процедуры

Хранимые процедуры, как и процедуры в Delphi, могут иметь локальные переменные, или не иметь их. Если локальных переменных нет, тело процедуры представляет собой только составной оператор, заключенный в скобки BEGIN … END. Причем эти скобки обязательны, даже если в процедуре всего только один оператор.

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

SET TERM ^;

CREATE PROCEDURE MyProc (param1 Integer)
RETURNS (param2 Varchar(20), param3 Double Precision)
AS
DECLARE VARIABLE perem1 Varchar(10);
DECLARE VARIABLE perem2 Date;
DECLARE VARIABLE perem3 Integer;
BEGIN
   … 
END^

SET TERM ;^

В приведенном примере мы вначале переопределяем терминатор, после чего приступаем непосредственно к описанию процедуры. В процедуре имеется один входящий, и два выходящих параметра, а также объявлены три локальные переменные. Заметим, что ключевое слово DECLARE обязательно, а вот слово VARIABLE можно опустить. Если вы желаете, чтобы ваша база данных была совместима с ранними версиями InterBase, то VARIABLE лучше указывать. Завершается процедура новым терминатором "^", после чего мы переопределяем его на стандартный символ ";".

Блок кода процедуры

Блок кода процедуры начинается ключевым словом BEGIN, и оканчивается ключевым словом END. Блок кода может состоять из одного или нескольких операторов, а также содержать вложенные блоки кода BEGIN … END.

В блоке кода процедуры могут встречаться:

• операторы присваивания, которые присваивают значения локальным переменным, входным или выходным параметрам (в отличие от оператора ":=" в Delphi, в SQL это просто знак равно "=");
• операторы SELECT для выборки данных из таблиц. Результаты выборки могут присваиваться переменным или параметрам;
• циклы, такие как FOR и WHILE ;
• управляющие структуры IF ;
• операторы EXECUTE PROCEDURE для вызова другой хранимой процедуры;
• комментарии, заключенные в скобки /* … */ ;
• символы сравнения >=, >, <=, <, <>, =, !< (не меньше), !> (не больше), != (не равно);
• команды модификации таблиц, такие как INSERT, UPDATE или DELETE ;
• и др.

Важно! Если в блоке кода локальные переменные используются внутри SQL-оператора (например, SELECT ), перед их именами следует ставить двоеточие. В других операторах этого делать не нужно.