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

Каратаев Владимир Леонидович, 23.04.03

У многих программистов при переходе с файловых БД или 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. ;(
IDNAMEDENGIRDATE
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

Необходимо составить годовой отчет о полученных и израсходованных суммах в виде:
NAMEDOHODRASHOD
зарплата
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 раз она и выведется, притом список будет не сортированным. Таблица станет более читабельной, если будут сгруппированы все одинаковые статьи с соответствующим суммированием по столбцам. Тогда для подсчета сумм еще и по статьям необходимо ввести сортировку с суммированием строк, следовательно, требуется распознавать "переход" к другой статье (то есть сравнивать названия статьи в текущей записи с названием статьи в предыдущей записи) и хранить промежуточные результаты подсчета по статье:
NAMEDOHODRASHOD
дивиденды
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);
IDNAMEDENGIRDATE
1зарплата200001.04.2003
2проезд-20030.04.2003

 

то чтобы подвести общий итог по двум членам семьи в виде:

NAMESUMMA
дивиденды
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

Результат выполнения процедуры:
NAMEDOHODRASHOD
дивиденды
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)
)
NAMESETVARREMARK
OLDTEMP10Удаление "бесхозных" записей старше 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):
ПАРАМЕТРITOG2ITOG4
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 и хранение предыдущих значений), и только в крайнем случае использовать третий подход (организация псевдо временных таблиц).

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