Написать эту статью побудило желание убедить некоторых коллег, которые используют тучи SQL-запросов, не игнорировать другие средства. Далее речь пойдёт о сохранённых процедурах применительно к свободной СУБД Firebird, как ИМХО самой пригодной для быстрой разработки технических систем. (Чтобы без лишних вопросов, сообщаю: как-нибудь рассмотрю MySQL vs Firebird).

Что это дает

Разработчики СУБД Firebird давно научили своё детище выполнять на стороне сервера БД любые сложные SQL-операции для вычисления и выдачи данных клиенту. Достоинства такого подхода очевидны:

– уменьшение сетевого трафика,

– ускорение выполнения клиентских программ,

– уменьшение объёма клиентских программ,

– повышение безопасности работы с БД.

Есть и неочевидное преимущество: язык PSQL, разработанный для текстов сохранённых процедур, более гибок, чем язык SQL, и имеет операторы, которых в SQL просто нет. Однако всё по порядку. Для наглядности полезнее рассмотреть какой-нибудь конкретный пример, чем разбираться с синтаксисом предмета.

Ставим задачу

Допустим, в некоей БД имеются две таблицы: ARHEN и SUPGRU:

ARHEN:

SYST SMALLINT

CHN SMALLINT

DAT INTEGER

MEASURE FLOAT

Ключевые непустые поля – SYST, CHN, INTEGER

SUPGRU:

SGN SMALLINT

SYST SMALLINT

CAN SMALLINT

SIG CHAR(2

Все поля не могут быть пустыми.

В таблице ARHEN для каждого устройства SYST для каждой даты DAT согласно номеру канала CHN записан параметр MEASURE. Таблица SUPGRU содержит бизнес-правило: каждая группа SGN может состоять из любых каналов CHN устройств SYST первой таблицы со знаком «плюс» или «минус», что указывается в поле SIG. Оговорка: канал CHN устройства SYST может входить в группу SGN только один раз. Задача: построить запрос, по которому для диапазона дат LDAT…HDAT и конкретной группы SGN была бы сгенерирована таблица с float-полем RES, соответствующим сумме полей MEASURE с учётом знака SIG.

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

Пишем текст процедуры

Удобнее всего воспользоваться каким-нибудь менеджером СУБД. Для Firebird – это IBExpert. Есть ещё и Firebird Studio, но он платный; есть ещё и Flame Robin, но очень уж незамысловатый. Остановимся на IBExpert. Подключаемся к БД, логинимся, вводим пароль, ПКМ по «Procedure», выбираем «Новая процедура». Можно обойтись и без менеджера СУБД: написать текст процедуры в блокноте, а затем воспользоваться бесплатной утилитой Isql, но это менее удобно. Можно вообще обойтись без вспомогательных средств – вставить набранный текст в текст SQL-запроса и выполнить его. Главное – текст!

Cначала пишем заголовок. Он всегда состоит из SQL-выражения CREATE PROCEDURE, за которым следует имя процедуры, в скобках указываются входные параметры и их типы, затем идёт клауза RETURNS, за которой в скобках указываются выходные параметры и их типы. В данном случае:

CREATE PROCEDURE CALC_SG_DAY (

PSGN INTEGER,

LDATE INTEGER,

HDATE INTEGER)

RETURNS ( RES FLOAT)

Завершается объявление процедуры клаузой

AS

Тело процедуры обрамляется «паскалевскими» выделителями блока BEGIN и END, тоже входящими в тело. Между объявлением процедуры и её телом – место для объявления промежуточных переменных с помощью PSQL-оператора DECLARE VARIABLE:

DECLARE VARIABLE VSYST INTEGER;

DECLARE VARIABLE VCAN INTEGER;

DECLARE VARIABLE VSIG CHAR(2);

DECLARE VARIABLE VRES FLOAT;

DECLARE VARIABLE I INTEGER;

Здесь и далее все PSQL-операторы должны заканчиваться точкой с запятой. Первые 3 переменные – это промежуточные значения, взятые из таблицы SUPGRU, далее – данные взятые из таблицы ARHEN. I – это дата, которая будет изменяться с LDATE по LDATE. Наконец, тело процедуры (открывающий BEGIN и завершающий END опущены):

I=LDATE;

WHILE (I<= HDATE) DO

BEGIN

  RES = 0;

  FOR SELECT SYST, CAN, SIG FROM SUPGRU

  WHERE (SGN=:PSGN) INTO :VSYST, :VCAN, :VSIG DO

  BEGIN

        FOR SELECT MEASURE FROM ARHEN

        WHERE (SYST=:VSYST) AND (CHN=:VCAN) AND (DAT=:I)

        INTO :VNG, :VHPK, :VPK DO

      BEGIN

            IF (:SIG = '+') THEN

                RES = RES + (:VRES);

            ELSE

                RES = RES – (:VRES);

       END

  END

  I = I+1;

SUSPEND;

EN

Наша процедура состоит из трёх циклов: внешнего цикла WHILE и двух вложенных FOR. Внутренний цикл FOR накапливает выходной параметр RES для очередной даты I. Обрамляющий цикл FOR извлекает из таблицы SUPGRU входные параметры для внутреннего цикла FOR, а внешний WHILE перебирает даты. «Паскалисты» улыбаются? Не спешите, есть нюансы.

Оператор SUSPEND – особый оператор. Здесь он относится к циклу WHILE и является в нём последним оператором. Его задача – вытолкнуть накопленные значения параметра RES в набор данных, а таким набором данных будет не что иное, как SQL-запрос, запускающий процедуру. Обычно в учебниках пишут, что оператор SUSPEND используется только совместно с FOR SELECT. Не верьте, это какое-то заблуждение. Оператор SUSPEND может использоваться с чем угодно, если это необходимо.

Другая тонкость: все алгебраические действия внутри процедур выполняются по правилам трёхзначной логики. То есть, если среди операндов встречается пустой операнд со значением NULL, то и результат будет NULL. В отличие от кода программы в таких случаях исключения не генерируются. В нашем примере один-единственный отсутствующий по дате замер MEASURE может испортить весь итог. Если это недопустимо, то перед выполнением операций нужно делать проверку: IF(операнд IS NULL) THEN операнд=0;

Заносим процедуру в БД

Работаем в IBExpert. Набрав текст новой процедуры, щёлкаем «компилировать». Если процедура написана без синтаксических ошибок, то она занесётся в подключенную БД, иначе менеджер сообщит об ошибке. Однако можно обойтись и без IBExpert. Создаём в своей программе с помощью new новый SQL-запрос, в его текст помещаем текст процедуры и запускаем на выполнение. Чтобы убедиться, что процедура занесена в БД, создаём ещё один запрос с текстом

SELECT * FROM CALC_SG_DAY (0,0,0)

И пытаемся его выполнить:

try { Query->Open();

res = true; }

catch(...) { res = false; }

Можем, конечно, получить и сообщения об ошибках в тексте процедуры. Если ошибок нет, то результат выполнения будет положительный. Значит, сервер СУБД скомпилировал процедуру и занёс её в БД.

Клиентский вызов

Ну это совсем просто. Пишем функцию, которая бы в качестве аргументов содержала параметры SQL-запроса, которые и передаём в его текст:

SELECT * FROM CALC_SG_DAY (параметры).

Остаётся в этой же функции распарсить возвращаемую таблицу в какой-нибудь контейнер, например, так:

Query->Open();

for(int i=0; !Query->Eof; Query->Next(), i++)

{…}

Затем употребляем контейнер по назначению. Например, выводим в график или в отчёт. Если возвращаемые данные не используются вызывающим SQL-запросом или процедура не возвращает никаких данных, то её можно запускать на выполнение оператором EXECUTE PROCEDURE(параметры). Однако внутри процедуры вызывать вложенную процедуру, которая возвращает данные, необходимо так:

EXECUTE PROCEDURE(входные параметры) RETURNING_VALUES (выходные параметры)

И не забывать про точку с запятой, так как во втором случае EXECUTE PROCEDURE – это не оператор SQL, а оператор PSQL.

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

Делаем лечилку

Если клиентская программа обновляется по подписке или другим образом и работает со своими БД, то есть смысл добавить в неё «лечилку», которая бы при первом запуске обновлённой программы добавляла бы в БД сохранённую процедуру, отсутствующую в прежней версии программы. Лечилка при первом запуске обновлённой программы делает вот что:

– смотрит, существует ли процедура в БД,

– если процедура в БД отсутствует, то заносит её в БД.

Чтобы проверить, существует ли процедура, выполните …процедуру:

CREATE PROCEDURE NEW_PROCEDURE RETURNS (x INTEGER) AS

BEGIN

IF (EXISTS(SELECT 1 FROM RDB$PROCEDURES WHERE rdb$Procedure_name = ' CALC_SG_DAY ')) THEN

x = 1;

ELSE

x = 0;

SUSPEND;

END

Можно и проще, но менее корректно: если SQL-запрос

SELECT * FROM имя_процедуры (параметры)

выбрасывает исключение, то процедуры имя_процедуры в БД нет.

Чтобы занести процедуру в БД из программы, вставляем текст создания процедуры в SQL-запрос и выполняем его.

Вместо заключения

Менеджеры СУБД позволяют отлаживать сохранённые процедуры так, как если бы вы делали это в Паскале. Ограниченный объём статьи не позволяет рассказать об отладке подробно, да в этом и нет необходимости – осваивается легко.

Очень часто сохранённые процедуры вызываются из триггеров. Триггер – это скрипт, который разработчик помещает в БД для того, чтобы БД автоматически запустила его на выполнение по событию изменения статуса той или иной таблицы. Механизм триггеров ещё больше разгружает сетевой трафик. Короче, чтобы по-настоящему работать с БД, надо работать с БД. Как говорят англичане – «Чтобы бегать, надо бегать».