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

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

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

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

и служит для присвоения локальной переменной или выходному параметру какого-либо значения. Здесь есть несколько правил. Во-первых, переменная или выходной параметр должны иметь совместимый тип данных с выражением. Во-вторых, перед именем переменной или выходного параметра двоеточие не ставится. В-третьих, в 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, в качестве параметров в этих операторах вместо названия полей могут использоваться локальные переменные. Чтобы отличить названия полей от имен переменных, последние должны предваряться двоеточием. Подробнее операторы модификации данных мы будем изучать в "Команды модификации данных DML. Скрипты" . Пример процедуры с оператором 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 <имя_исключения>

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