Замена временных таблиц в Firebird

Каратаев Владимир Леонидович, 23.04.2003.
В Firebird 2.1, в 2008 году, появились временные таблицы - Global Temporary Tables.

У многих программистов при переходе с файловых БД или MS SQL, Oracle на Firebird возникает часто вопрос о наличии временных таблиц в Firebird, которые они привыкли использовать при написании хранимых процедур. Однако, несмотря на отсутствие временных таблиц в этом SQL-сервере, ряд механизмов Firebird позволяет их успешно заменять. Ниже описываются три подхода для решения задач без использования временных таблиц.

Первый подход. Чаще всего достаточно использовать структуру FOR SELECT <ЗАПРОС> INTO <ПЕРЕМЕННЫЕ> DO <ДЕЙСТВИЕ>. Оператор FOR SELECT предназначен для выполнения операций (DO) над переменными (INTO), значение которых устанавливается равным значениям возвращаемых полей запроса (SELECT). При этом интерпретатором Firebird:
  1. Выполнится запрос
  2. Затем будет осуществлен переход на первую строку выборки запроса
  3. Значения полей первой строки запроса присвоятся переменным хранимой процедуры, перечисленным в into по порядку их перечисления: <ПЕРЕМЕННАЯ1>=<ПОЛЕ1>,<ПЕРЕМЕННАЯ2>=<ПОЛЕ2> и т. д.
  4. Выполнятся операции, указанные в блоке DO.
  5. Если в выборке запроса еще остались строки, то будет осуществлен переход ко второй строке и повторены операций с п. 3

Полный синтаксис этого оператора приведен в документации (например, см. файл LANGREF.PDF). В некотором роде FOR SELECT и есть временная таблица, над которой проводятся операции. Разберем это на примере таблицы:
// домашняя бухгалтерия
CREATE TABLE CASH (
   ID INTEGER, // идентификатор записи в таблице
   NAME VARCHAR(30), // статья доходов/расходов
   DENGI INTEGER, // сумма в рублях: с "плюсом" доходы (например, зарплата),
                  //с "минусом"- расходы (например, покупки)*
   RDATE TIMESTAMP // дата внесения записи
)
*- в принципе, для рядового российского инженера хватило бы поля smallint вместо integer. ;(
ID NAME DENGI RDATE
1 зарплата 3000 10.04.2003
2 ком. платежи -500 05.04.2003
3 интернет -150 22.04.2003
4 дивиденды 100 02.04.2003
5 зарплата 3000 24.04.2003
6 ком. платежи -400 29.04.2003
Необходимо составить годовой отчет о полученных и израсходованных суммах в виде:
NAME DOHOD RASHOD
зарплата 3 000 0
ком. платежи 0 -500
интернет 0 -150
дивиденды 100 0
зарплата 3 000 0
ком. платежи 0 -400
ИТОГО 6 100 -1 050
В данном случае проблема состоит в том, что колонку DENGI необходимо разбить на две колонки, к тому же подсчитать итоговую сумму. "Лобовое" решение выглядит так:
  1. Делается запрос по доходам: SELECT NAME, DENGI FROM CASH WHERE DENGI>=0.
  2. Затем делается запрос по расходам: SELECT NAME, DENGI FROM CASH WHERE DENGI<0.
  3. Потом подсчитывается итоговая сумма доходов: SELECT SUM(DENGI) FROM CASH WHERE DENGI>=0.
  4. И, наконец, итоговая сумма расходов: SELECT SUM(DENGI) FROM CASH WHERE DENGI<0.
  5. Полученные результаты нескольких запросов объединяются в клиентском приложении в таблицу.

Всего потребовалось пять запросов. Использование временных таблиц здесь является наиболее эффективным решением. Для Firebird решение будет таким:
CREATE PROCEDURE ITOG (
   NGOD TIMESTAMP,
   EGOD TIMESTAMP)
RETURNS (
   NAME VARCHAR(30),
   DOHOD INTEGER,
   RASHOD INTEGER)
AS
DECLARE VARIABLE SDOHOD INTEGER;
DECLARE VARIABLE SRASHOD INTEGER;
DECLARE VARIABLE TDENGI INTEGER;
BEGIN 
   /* ИНИЦИАЛИЗАЦИЯ ПЕРЕМЕННЫХ */
   SDOHOD=0;
   SRASHOD=0;
   /* ВЫБОРКА */
   FOR SELECT NAME, DENGI FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD
   INTO :NAME, :TDENGI
   DO
      BEGIN
         /* РАЗБИТИЕ ПОЛЕ DENGI НА ДВЕ КОЛОНКИ- DOHOD (ДОХОД) И RASHOD (РАСХОД) */
         IF (TDENGI>=0) THEN
         BEGIN
            DOHOD=TDENGI;
            RASHOD=0;
            /* ПОДСЧЕТ ИТОГОВОЙ СУММЫ ПО ДОХОДАМ */
            SDOHOD=SDOHOD+TDENGI;
         END
         ELSE
            BEGIN
               RASHOD=TDENGI;
               DOHOD=0;
               /* ПОДСЧЕТ ИТОГОВОЙ СУММЫ ПО РАСХОДАМ */
               SRASHOD=SRASHOD+TDENGI;
            END
         SUSPEND;
      END
   /* ВЫВОД ИТОГОВОЙ СУММЫ */
   NAME='ИТОГО';
   DOHOD=SDOHOD;
   RASHOD=SRASHOD;
   SUSPEND;
END
 
Второй подход заключается в использовании комбинации FOR SELECT и хранения в переменных хранимой процедуры предыдущих значений полей таблицы.

В предыдущем примере пункт "зарплата" будет выведен столько раз, сколько он встречается в таблице CASH, то есть, если получали в течение года зарплату 12 раз, то 12 раз она и выведется, притом список будет не сортированным. Таблица станет более читабельной, если будут сгруппированы все одинаковые статьи с соответствующим суммированием по столбцам. Тогда для подсчета сумм еще и по статьям необходимо ввести сортировку с суммированием строк, следовательно, требуется распознавать "переход" к другой статье (то есть сравнивать названия статьи в текущей записи с названием статьи в предыдущей записи) и хранить промежуточные результаты подсчета по статье:
NAME DOHOD RASHOD
дивиденды 100 0
зарплата 6 000 0
интернет 0 -150
ком. платежи 0 -900
ИТОГО 6 100 -1 050
Соответствующая процедура будет выглядеть следующим образом:
CREATE PROCEDURE ITOG2 (
   NGOD TIMESTAMP,
   EGOD TIMESTAMP)
RETURNS (
   NAME VARCHAR(30),
   DOHOD INTEGER,
   RASHOD INTEGER)
AS
DECLARE VARIABLE SDOHOD INTEGER;
DECLARE VARIABLE SRASHOD INTEGER;
DECLARE VARIABLE TDENGI INTEGER;
DECLARE VARIABLE STDOH INTEGER;
DECLARE VARIABLE STRAS INTEGER;
DECLARE VARIABLE STOLD VARCHAR(30) CHARACTER SET WIN1251;
DECLARE VARIABLE STNEW VARCHAR(30) CHARACTER SET WIN1251;
BEGIN
   /* ИНИЦИАЛИЗАЦИЯ ПЕРЕМЕННЫХ */
   SDOHOD=0;
   SRASHOD=0;
   STDOH=0;
   STRAS=0;
   STOLD='';
   STNEW='';
   /* ВЫБОРКА */
   FOR SELECT NAME, DENGI FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD ORDER BY NAME
   INTO :STNEW, :TDENGI
   DO
      BEGIN
         /* ДЕТЕКТИРУЕТСЯ НАЧАЛО НОВОЙ СТАТЬИ */
         IF (:STOLD<>:STNEW) THEN
            BEGIN
               /* ПО ПРЕДЫДУЩЕЙ СТАТЬЕ НЕОБХОДИМО ВЫВЕСТИ РЕЗУЛЬТАТ */
               NAME=STOLD;
               DOHOD=STDOH;
               RASHOD=STRAS;
               IF (:NAME<>'') THEN SUSPEND;
               /* ОБНУЛИТЬ ПЕРЕМЕННЫЕ ДЛЯ ПРОВЕДЕНИЯ СУММИРОВАНИЯ ПО СЛЕДУЮЩЕЙ СТАТЬЕ */
               STOLD=STNEW;
               STDOH=0;
               STRAS=0;
            END
         /* ПРОВЕРКА- ИДЕТ ЛИ СУММИРОВАНИЕ ПО ОДНОЙ И ТОЙ ЖЕ СТАТЬЕ */
         IF (:STOLD=:STNEW) THEN
            BEGIN
               /* РАЗБИТИЕ ДОХОДОВ И РАСХОДОВ ПО КОЛОНКАМ С СУММИРОВАНИЕМ */
               IF (:TDENGI>=0) THEN
                  BEGIN
                     /* СЧИТАЕТСЯ СУММА ДОХОДОВ ПО ТЕКУЩЕЙ СТАТЬЕ */
                     STDOH=STDOH+TDENGI;
                     /* СЧИТАЕТСЯ ОБЩАЯ СУММА ДОХОДОВ */
                     SDOHOD=SDOHOD+TDENGI;
                     END
               ELSE
                  BEGIN
                  /* СЧИТАЕТСЯ СУММА РАСХОДОВ ПО ТЕКУЩЕЙ СТАТЬЕ */
                  STRAS=STRAS+TDENGI;
                  /* СЧИТАЕТСЯ ОБЩАЯ СУММА РАСХОДОВ */
                  SRASHOD=SRASHOD+TDENGI;
                  END
            END
      END
   /* НЕОБХОДИМО УЧЕСТЬ ЗАМЫКАЮЩУЮ СПИСОК СТАТЬЮ */
   NAME=STNEW;
   DOHOD=STDOH;
   RASHOD=STRAS;
   SUSPEND;
   /* ВЫВОД ИТОГОВОЙ СУММЫ */
   NAME='ИТОГО';
   DOHOD=SDOHOD;
   RASHOD=SRASHOD;
   SUSPEND;
END

Как частный случай первых двух подходов, следует отметить возможность использования вложенного оператора FOR SELECT <ЗАПРОС> INTO <ПЕРЕМЕННЫЕ> DO <ДЕЙСТВИЕ>:
FOR SELECT <ЗАПРОС1> INTO <ПЕРЕМЕННЫЕ1> DO
   FOR SELECT <ЗАПРОС2> INTO <ПЕРЕМЕННЫЕ2> DO <ДЕЙСТВИЕ>
или
FOR SELECT <ЗАПРОС1> INTO <ПЕРЕМЕННЫЕ1> DO
   BEGIN
      SELECT <ЗАПРОС2> INTO <ПЕРЕМЕННЫЕ2>
      <ДЕЙСТВИЕ>
   END

При этом если интерпретировать FOR SELECT как своеобразную "временную" таблицу, то такое вложение предоставляет возможность работать с несколькими "временными" таблицами. Например, если ведется в отдельной таблице подробный учет по другому члену семьи в такой же по структуре таблице, как и CASH, но с именем CASH2:
CREATE TABLE CASH2 (
   ID INTEGER,
   NAME VARCHAR(30),
   DENGI INTEGER,
   RDATE TIMESTAMP);
 
ID NAME DENGI RDATE
1 зарплата 2000 01.04.2003
2 проезд -200 30.04.2003

то чтобы подвести общий итог по двум членам семьи в виде:
 
NAME SUMMA
дивиденды 100
зарплата 8 000
интернет -150
ком. платежи -900
проезд -200
ИТОГО 6 850
можно реализовать следующую процедуру:
CREATE PROCEDURE ITOG3 (
   NGOD TIMESTAMP,
   EGOD TIMESTAMP)
RETURNS (
   NAME VARCHAR(30),
   SUMMA INTEGER)
AS
DECLARE VARIABLE S INTEGER;
DECLARE VARIABLE TDENGI INTEGER;
DECLARE VARIABLE TDENGI2 INTEGER;
BEGIN
   /* ИНИЦИАЛИЗАЦИЯ */
   S=0;
   /* ВЫБОРКА */
   FOR SELECT NAME, SUM(DENGI) FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD GROUP BY NAME
   INTO :NAME, :TDENGI
   DO
      BEGIN
         /* ВЫБОРКА ИЗ ВТОРОЙ ТАБЛИЦЫ */
         TDENGI2=0;
         SELECT SUM(DENGI) FROM CASH2
            WHERE RDATE BETWEEN :NGOD AND :EGOD AND NAME=:NAME
         GROUP BY NAME
         INTO :TDENGI2;
         /* ОБРАБОТКА ВОЗВРАЩЕНИЯ ПУСТЫХ ЗАПИСЕЙ */
         IF (TDENGI IS NULL) THEN TDENGI=0;
         IF (TDENGI2 IS NULL) THEN TDENGI2=0;
         /* ПОДСЧЕТ СУММЫ И ВЫВОД РЕЗУЛЬТАТОВ */
         SUMMA=TDENGI+TDENGI2;
         S=S+SUMMA;
         SUSPEND;
      END
   /* НЕОБХОДИМО УЧЕСТЬ ЗАПИСИ, КОТОРЫЕ ЕСТЬ ВО ВТОРОЙ ТАБЛИЦЕ И НЕТ В ПЕРВОЙ */
   FOR SELECT NAME, SUM(DENGI)
      FROM CASH2
      WHERE RDATE BETWEEN :NGOD AND :EGOD AND
      NAME NOT IN (SELECT DISTINCT NAME FROM CASH)
      GROUP BY NAME
   INTO :NAME, :SUMMA
   DO
      BEGIN
         S=S+SUMMA;
         SUSPEND;
      END
   /* ВЫВОД ИТОГОВОЙ СУММЫ */
   NAME='ИТОГО';
   SUMMA=S;
   SUSPEND;
END

Третий подход применяется в тех случаях, когда невозможно по условиям задачи отсортировать предварительно выборку. Тогда организуется псевдо временная таблица с необходимыми полями, в процедуре производиться сначала ее наполнение, а затем делается требуемая обработка записей в такой таблице. Вот как выглядит решение задачи, поставленной при рассмотрении второго подхода, при использовании псевдо временных таблиц:
/* НЕОБХОДИМО СОЗДАТЬ ПСЕВДО ВРЕМЕННУЮ ТАБЛИЦУ */
CREATE TABLE TEMP (
NAME VARCHAR(30), // поле для отчета
DOHOD INTEGER, // поле для отчета
RASHOD INTEGER, // поле для отчета
TUSER VARCHAR(8), // так как возможен многопользовательский доступ к таблице, то необходимо указывать,
                  какому пользователю принадлежит запись- иначе, в случае, разбития процедуры
                  формирования отчета на несколько транзакций, записи разных пользователей нельзя будет отличить
TDATE TIMESTAMP // дата внесения записи- нужна для контроля за отслужившими записями
                и по каким-либо причинам не удаленными
)
/* ПРОЦЕДУРА ФОРМИРОВАНИЯ ОТЧЕТА */
CREATE PROCEDURE ITOG4 (
   NGOD TIMESTAMP,
   EGOD TIMESTAMP)
RETURNS (
   NAME VARCHAR(30),
   DOHOD INTEGER,
   RASHOD INTEGER)
AS
DECLARE VARIABLE TDENGI INTEGER;
DECLARE VARIABLE ST VARCHAR(30);
DECLARE VARIABLE SDOHOD INTEGER;
DECLARE VARIABLE SRASHOD INTEGER;
BEGIN
   /* ИНИЦИАЛИЗАЦИЯ ПЕРЕМЕННЫХ */
   SDOHOD=0;
   SRASHOD=0;
   /* УДАЛЕНИЕ СТАРЫХ ЗАПИСЕЙ, ВОЗМОЖНО ОСТАВШИХСЯ ОТ ПРЕДЫДУЩИХ ЗАПРОСОВ ПОЛЬЗОВАТЕЛЯ */
   DELETE FROM TEMP WHERE TUSER=USER;
   /* ЗАПОЛНЕНИЕ ПСЕВДО ВРЕМЕННОЙ ТАБЛИЦЫ СПИСКОМ СТАТЕЙ */
   INSERT INTO TEMP
   SELECT DISTINCT NAME, 0, 0, USER, CAST('NOW' AS TIMESTAMP)
   FROM CASH
   WHERE RDATE BETWEEN :NGOD AND :EGOD;
   /* ФОРМИРОВАНИЕ ОТЧЕТА */
   FOR SELECT NAME, DENGI FROM CASH INTO :ST, :TDENGI
   DO
      BEGIN
         IF (:TDENGI>=0) THEN
            UPDATE TEMP SET DOHOD=DOHOD+:TDENGI
            WHERE NAME=:ST AND TUSER=USER;
         ELSE
            UPDATE TEMP SET RASHOD=RASHOD+:TDENGI
            WHERE NAME=:ST AND TUSER=USER;
      END
   /* ВЫВОД РЕЗУЛЬТАТА */
   FOR SELECT NAME, DOHOD, RASHOD
      FROM TEMP
      WHERE TUSER=USER
      ORDER BY NAME
   INTO :NAME, :DOHOD, :RASHOD
   DO
      BEGIN
         SDOHOD=SDOHOD+DOHOD;
         SRASHOD=SRASHOD+RASHOD;
         SUSPEND;
      END
   /* ВЫВОД ИТОГОВОЙ СУММЫ */
   NAME='ИТОГО';
   DOHOD=SDOHOD;
   RASHOD=SRASHOD;
   SUSPEND;
END

Результат выполнения процедуры:
NAME DOHOD RASHOD
дивиденды 100 0
зарплата 6 000 0
интернет 0 -150
ком. платежи 0 -900
ИТОГО 6 100 -1 050
Так как по какой-либо причине в таблице TEMP могут остаться "бесхозные" старые записи, то необходимо ее периодически чистить (здесь потребуется использовать поле TDATE), например, следующим образом:
/* НЕБОЛЬШОЙ СОВЕТ: ВЫНОСИТЕ КОНСТАНТЫ, ИСПОЛЬЗУЕМЫЕ В ХРАНИМЫХ ПРОЦЕДУРАХ, В ОТДЕЛЬНЫЕ ТАБЛИЦЫ-
ЭТО ПОЗВОЛИТ МЕНЯТЬ ИХ ЗНАЧЕНИЕ НЕ ПЕРЕКОМПИЛИРУЯ ПРОЦЕДУРУ ЗАНОВО */
CREATE TABLE SETVARS ( // таблица констант
NAME VARCHAR(10), // по этому полю лучше создать индекс
SETVAR VARCHAR(50),
REMARK VARCHAR(50)
)
 
NAME SETVAR REMARK
OLDTEMP 10 Удаление "бесхозных" записей старше 10 дней

/* ПРОЦЕДУРА ЧИСТКИ */
CREATE PROCEDURE CLEARTEMP
AS
DECLARE VARIABLE T INTEGER;
BEGIN
   /* ИЗ ТАБЛИЦЫ КОНСТАНТ ОПРЕДЕЛЯЕТСЯ ПЕРЕМЕННАЯ "T" */
   FOR SELECT CAST(SETVAR AS INTEGER)
      FROM SETVARS
      WHERE NAME='OLDTEMP'
   INTO :T DO
      /* УДАЛЕНИЕ ВСЕХ СТАРЫХ ЗАПИСЕЙ */
      DELETE FROM TEMP
      WHERE TDATE<(CAST('NOW' AS TIMESTAMP)-:T);
END

Понятно, что третий подход самый медленный из всех, так как проход по таблице CASH осуществляется два раза, к тому же делаются вставки и обновления записей в псевдо временной таблице и только затем выводится результат. Сюда же добавляется задача обеспечения регулярной чистки таблицы TEMP. Первый и второй вариант будут выполняться значительно быстрее – практически со скоростью выборки записей. Как показывает опыт, индексирование таблицы CASH по сортируемому полю NAME позволит добиться получения первых результатов выполнения запроса через несколько секунд даже на таблицах с количеством записей равным примерно 10 млн. строк. В то же время при третьем подходе время получения первых записей составит несколько секунд уже на таблицах с количеством записей равным примерно 10 тыс. строк. Например, при выполнении процедур ITOG2 и ITOG4 на приведенных выше тестовых примерах были получены следующие результаты (данные программы IBExpert):
  ПАРАМЕТР ITOG2 ITOG4
Query Time Prepare 0,00 ms 0,00 ms
Execute 15,00 ms 16,00 ms
Avg fetch time 3,00 ms 3,20 ms
Operations Fetches 15 570
Поэтому рекомендуется в первую очередь пытаться реализовать при решении поставленных задач первый (использование FOR SELECT) или второй подход (комбинация FOR SELECT и хранение предыдущих значений), и только в крайнем случае использовать третий подход (организация псевдо временных таблиц).

ПРИЛОЖЕНИЕ. Иллюстративный материал статьи: тестовая БД и скрипт

Подпишитесь на новости Firebird в России

Подписаться