Опросы
Активных опросов на данный момент нет.
Счетчики
Rambler's Top100





Яндекс цитирования


Rambler's Top100

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

  У многих программистов при переходе с файловых БД или 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
// домашняя бухгалтерия
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
ute
15,00 ms
16,00 ms
Avg fetch time
3,00 ms
3,20 ms
Operations
Fetches
15
570

Поэтому рекомендуется в первую очередь пытаться реализовать при решении поставленных задач первый (использование FOR SELECT) или второй подход (комбинация FOR SELECT и хранение предыдущих значений), и только в крайнем случае использовать третий подход (организация псевдо временных таблиц).

Страниц: 1
Опубликовано: 26.03.09 | Просмотров: 2349 | [ + ]   [ - ]   | Печать
 
 
© 2018