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

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

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

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

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

Алгоритмический язык хранимых процедур и триггеров содержит в своей основе обычный 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 не нужно, это все равно приведет к ошибке. В конце лекции будут представлены три примера рабочих процедур.

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

Заголовок

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

CREATE PROCEDURE Proc1

CREATE PROCEDURE Proc2
(perem1 Integer, perem2 Float, perem3 Date)

CREATE PROCEDURE Proc3
(vhod_param1 Integer)
RETURNS (vihod_param1 Double, vihod_param2 Varchar(10))

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.

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

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

Оператор присваивания

Оператор присваивания имеет вид

<переменная/выходной параметр> = <выражение>

и служит для присвоения локальной переменной или выходному параметру какого-либо значения. Здесь есть несколько правил. Во-первых, переменная или выходной параметр должны иметь совместимый тип данных с выражением. Во-вторых, перед именем переменной или выходного параметра двоеточие не ставится. В-третьих, в InterBase выражение может быть либо строковым, либо арифметическим. В первом случае выражение может содержать оператор конкатенации (объединения) строк " || ", во втором случае - четыре арифметических оператора +, -, * и /. Помимо этого, выражение может содержать значения однотипных столбцов таблиц, или результат работы другой процедуры.

Условный оператор IF… THEN … ELSE

В отличие от Delphi, в InterBase условное выражение оператора IF обязательно нужно помещать в круглые скобки, кроме того, перед ELSE точка с запятой не опускается:

IF (<условное_выражение>) THEN <оператор_1>; [ELSE <оператор_2>]

Как обычно, если <условное_выражение> возвращает истину, то выполняется <оператор_1>, 
	в противном случае выполняется <оператор_2>. Пример:

IF (KOLVO>5 AND KOLVO<10) THEN …;

Заметьте, что приоритет операций сравнения выше, чем логических операций AND, OR и NOT, поэтому при использовании более чем одного условия нет необходимости заключать каждое из них в отдельные скобки. Альтернативный вариант ELSE не является обязательным и может быть опущен.

Оператор SELECT

Хранимая процедура может содержать оператор SELECT для вывода одного или нескольких значений и присвоения этих значений локальным переменным или выходным параметрам. Пример:

SELECT * FROM TABLE_FIRMA
INTO :fam, :imya, :otch

Таблица TABLE_FIRMA содержит три текстовых поля, содержащие фамилию, имя и отчество сотрудника. В примере берется первая запись таблицы, и значения ее полей присваиваются локальным переменным (или выходным параметрам) fam, imya и otch. Однако более типичным является применение этого оператора с условием выборки, возвращающим лишь одно значение:

SELECT MAX(KOLVO) FROM SKLAD
INTO :p_kolvo

Цикл FOR SELECT и SUSPEND

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

FOR SELECT <условие_выборки> 
  INTO <список_переменных/параметров> DO <оператор>

Здесь <условие_выборки> - любое условие оператора SELECT.

<список_переменных/параметров> - Список локальных переменных или выходных параметров, чей тип данных соответствует типу данных, полученных командой SELECT.

<оператор> - выполняемый оператор цикла. Обычно этим оператором бывает оператор SUSPEND, который помещает полученную запись в буфер (кэш), и требует получения следующей записи, и так до тех пор, пока не закончится цикл. Такая конструкция позволяет получать не одну запись, а набор записей, который возвращается в виде виртуальной таблицы. Такие процедуры называются процедурами выборки, и вызываются как обычные таблицы.

Оператор SUSPEND применяется только в хранимых процедурах выборки, в триггерах он недопустим. В выполняемых процедурах пользоваться этим оператором синтаксически не запрещено, однако делать этого не стоит - все последующие после SUSPEND операторы не будут выполнены. Вместо этого в выполняемых процедурах обычно применяют явную команду досрочного выхода EXIT. Пример:

FOR SELECT TOVAR, KOLVO FROM TABLE SDELKI
INTO :param_st, :param_int
DO SUSPEND;

В данном примере выходным параметрам param_st и param_int присваиваются значения полей Tovar и Kolvo первой записи, после чего вызывается оператор SUSPEND и процедура приостанавливается. Данные передаются в вызывающую программу, после чего процедура таким же образом обрабатывает вторую запись. И так до конца таблицы. Для вызывающей программы все выглядит так, будто вызывалась таблица, а не хранимая процедура. Однако зачастую процедуры выборки выполняются намного быстрее, чем такой же запрос из клиентского приложения, ведь процедура - это скомпилированная подпрограмма, которая выполняется на стороне сервера.

Следует отметить, что применение этого цикла не ограничивается только оператором SUSPEND. Вы можете установить там любой оператор, или несколько операторов, поместив их в скобки BEGIN … END. Например, в теле цикла вы можете проверять значения полей на какое-то условие, и если условие не верно, исправить запись.

Цикл WHILE … DO

Этот цикл аналогичен тому, что вы используете в Delphi:

WHILE (<условие_цикла>) DO
   <оператор>

Как видно из синтаксиса, условие цикла должно быть заключено в круглые скобки. Оператор может быть составным, помещенным между BEGIN и END. Кроме того, в теле оператора может встречаться команда EXIT, служащая для принудительного завершения работы процедуры. В триггере оператор EXIT не применяется.

Операторы INSERT, UPDATE, DELETE

В хранимых процедурах и триггерах могут встречаться стандартные SQL -операторы модификации данных INSERT, UPDATE, и DELETE, которые соответственно, позволяют вставить новую запись, исправить или удалить существующую запись. В отличие от SQL, в качестве параметров в этих операторах вместо названия полей могут использоваться локальные переменные. Чтобы отличить названия полей от имен переменных, последние должны предваряться двоеточием. Подробнее операторы модификации данных мы будем изучать в лекции № 21. Пример процедуры с оператором INSERT смотрите в конце лекции.

Оператор EXECUTE PROCEDURE

Из хранимой процедуры или триггера можно вызвать другую хранимую процедуру. Триггер вызвать нельзя. Синтаксис вызова хранимой процедуры:

EXECUTE PROCEDURE <имя_процедуры> [<список_параметров>]
[RETURNING_VALUES :<список_переменных>]

Здесь <имя_процедуры> - имя вызываемой процедуры

<список_параметров> - один или несколько передаваемых в процедуру параметров (необязательно, если процедура не требует параметров). Если параметров несколько, они разделяются запятыми.

<список_переменных> - одна или несколько локальных переменных или выходных параметров, в которые помещаются результаты работы вызываемой процедуры (необязательно, если процедура не возвращает значения). Перед именем каждой переменной (выходного параметра) обязательно ставится двоеточие, переменные (выходные параметры) разделяются запятыми.

Исключения

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

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

CREATE EXCEPTION <имя><'сообщение'>;

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

CREATE EXCEPTION no_del_null 'Cannot divide by zero!'

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

CREATE EXCEPTION no_del_null 'На ноль делить нельзя!'

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

IF (delitel = 0) THEN BEGIN
   EXCEPTION no_del_null;
   Resultat = 0
END

В данном примере Resultat - выходной параметр процедуры, вызвавшей исключение, а delitel - входной параметр, который мы проверяем на значение 0.

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

Исключения могут быть изменены командой

ALTER EXCEPTION <имя_исключения> <"новый_текст">

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

Исключения могут быть удалены командой

DROP EXCEPTION <имя_исключения>

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

События и оператор POST_EVENT

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

POST_EVENT "Имя_события"

Пример:

POST_EVENT "Ups_Sorry"

Имя события может быть строкой или текстовой переменной, содержащей имя события.

Клиентская программа должна зарегистрировать на сервере те события, которые ее интересуют, чтобы получать их. Сделать это в клиентском приложении проще всего с помощью компонента TIBEventAlert, который находится на вкладке Samples Палитры компонентов, либо с помощью компонента TIBEvents, если вы для работы с БД пользуетесь компонентами с вкладки InterBase.

Суть работы с этими компонентами проста:

Вначале в свойстве Database вы выбираете компонент базы данных, например TDatabase или TIBDatabase, в зависимости от того, каким механизмом доступа к БД вы пользуетесь. Компонент базы данных должен быть подключен к серверу (подробнее об этом в следующих лекциях).

Далее вы дважды щелкаете по свойству Events, которое имеет тип TStrings, и в открывшемся списке вписываете интересующие вас события.

Затем вы переводите свойство Registered в True.

Потом требуется перейти на вкладку Events инспектора объектов и сгенерировать событие OnEventAlert, в котором можете написать какое-либо сообщение или действие. Параметр EventName будет содержать имя случившегося события. Например:

If EventAlert = 'Ups_Sorry' then
   ShowMessage('Извините, но кто то удалил вашу запись!');

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

Изменения и удаления хранимых процедур

Изменение существующей процедуры делается командой ALTER PROCEDURE. Синтаксис этой команды ничем не отличается от синтаксиса команды CREATE PROCEDURE. Это "мягкий" способ изменения процедуры, который обычно применяют для добавления новых входных или выходных параметров. Более надежным способом считается удаление старой процедуры и создание новой, с таким же именем.

Удаление процедуры производится командой

DROP PROCEDURE <имя_процедуры>
<имя_процедуры> - это просто имя существующей процедуры без всяких параметров.

Пример:

DROP PROCEDURE MyProc;

Разумеется, изменять или удалять процедуру может только администратор SYSDBA или пользователь, создавший эту процедуру. Причем при изменении или удалении, процедура не должна находиться в использовании.

Примеры создания и вызова хранимых процедур

Далее следуют примеры процедур, которые необходимо выполнять в Interactive SQL. Убедитесь, что сервис InterBase включен, загрузите IBConsole, войдите в базу данных First и вызовите окно Interactive SQL. Выполним следующий пример:

/* Переопредилим терминатор: */
SET TERM ^;
/* Создаем процедуру, которая будет добавлять новые записи
   в таблицу Table_Firma: */
CREATE PROCEDURE Firma_Insert
  (F VARCHAR(20), I VARCHAR(20), O VARCHAR(20))
AS
BEGIN
  INSERT INTO TABLE_FIRMA(FAMILIYA, IMYA, OTCHESTVO)
         VALUES(:F, :I, :O);
END^
SET TERM ;^
COMMIT;

/* Сразу же применим полученную процедуру для ввода значений: */
EXECUTE PROCEDURE Firma_Insert
  ('Иванов', 'Иван', 'Иванович');
EXECUTE PROCEDURE Firma_Insert
  ('Петров', 'Петр', 'Петрович');
EXECUTE PROCEDURE Firma_Insert
  ('Николаев', 'Николай', 'Николаевич');
COMMIT;

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

Следующий пример создаст процедуру выборки:

/* Переопределяем терминатор: */
SET TERM ^;
/* Создаем процедуру: */
CREATE PROCEDURE Firma_Select
RETURNS (F VARCHAR(20), I VARCHAR(20), O VARCHAR(20))
AS
BEGIN
  /* С помощью цикла получаем все строки таблицы: */
  FOR SELECT * FROM TABLE_FIRMA
  INTO :F, :I, :O
  DO SUSPEND;
END^
SET TERM ;^
COMMIT;

/* Вызовем полученную процедуру командой SELECT: */
SELECT * FROM Firma_Select;

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

Как видите, и первая и вторая процедуры выполнили свою задачу. Мы получили такой же набор данных, как из обычной таблицы. В качестве полей здесь выступают выходные параметры F, I и O.

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

SET TERM ^;
CREATE PROCEDURE PrimerProc(I INTEGER)
RETURNS (K INTEGER, V VARCHAR(25))
AS
BEGIN
  K = 0;
  WHILE (K < I) DO
  BEGIN
    K = K + 1;
    V = 'Строка № ' || K;
    SUSPEND;
  END
END^
SET TERM ;^
COMMIT;

/* Вызываем полученную процедуру: */
SELECT * FROM PrimerProc(5);

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

Далее мы обнуляем целую переменную и вызываем цикл WHILE, который будет выполняться до тех пор, пока переменная K будет меньше входящего параметра. В цикле мы вначале прибавляем к этой переменной единицу, после чего формируем строку типа "Строка № 1". При этом мы используем знак конкатенации (объединения) строк "||", а в качестве второй подстроки подставляем целое число, которое хранится в переменной K. Происходит неявное преобразование типов данных. Только не забывайте, целое число можно преобразовать в строку, но не наоборот! Далее мы вызываем оператор SUSPEND, который помещает полученную строку в набор данных. Цикл будет продолжаться столько раз, сколько мы укажем во входящем параметре процедуры. Когда мы вызовем эту процедуру оператором SELECT, то получим следующий результат:

Теперь, открыв в дереве серверов IBConsole базу данных First, и выделив подраздел Stored Procedures, вы увидите три созданных хранимых процедуры, которые в дальнейшем можно вызывать неоднократно.