Лекции по "Систе́ма управле́ния ба́зами да́нных "

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^

SETTERM ;^

COMMIT;

 

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

SELECT * FROM Firma_Select;

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

 
Рис. 19.1.  Результат действий процедуры выборки Firma_Select

Как видите, и первая и вторая процедуры выполнили свою задачу. Мы получили такой же набор данных, как из обычной таблицы. В качестве полей здесь выступают выходные параметры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, то получим следующий результат: