Написать эту статью побудило желание убедить некоторых коллег, которые используют тучи 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-запрос и выполняем его.
Вместо заключения
Менеджеры СУБД позволяют отлаживать сохранённые процедуры так, как если бы вы делали это в Паскале. Ограниченный объём статьи не позволяет рассказать об отладке подробно, да в этом и нет необходимости – осваивается легко.
Очень часто сохранённые процедуры вызываются из триггеров. Триггер – это скрипт, который разработчик помещает в БД для того, чтобы БД автоматически запустила его на выполнение по событию изменения статуса той или иной таблицы. Механизм триггеров ещё больше разгружает сетевой трафик. Короче, чтобы по-настоящему работать с БД, надо работать с БД. Как говорят англичане – «Чтобы бегать, надо бегать».
Комментарии
И да -- отключите в SQL-запросе, заносящем процедуру в БД, контроль параметров, иначе при попытке выполнении такого запроса будете получать ошибку. :)
Было бы интересно.
И обсуждаемо.