Чтение онлайн

на главную - закладки

Жанры

Мир InterBase. Архитектура, администрирование и разработка приложений баз данных в InterBase/FireBird/Yaffil

Востриков С М

Шрифт:

Итак, мы получили среднее значение цены. Теперь необходимо пройтись по всей таблице, сравнить значение цены в каждой записи со средней ценой и предпринять соответствующие действия

С начала opганизуем перебор каждой записи из таблицы Table_example

FOR

SELECT ID, NAME, PRICE_1

FROM Table_Example

INTO :ID, :NAME, :new_price

DO

BEGIN

/*_здесь оОрсшатыьаем каждую запись*/

END

При выполнении этой конструкции из таблицы Table_example построчно будут выниматься данные и значения полей в каждой строке будут присвоены переменным ID, NAME и new_pnce. Вы, конечно, помните, что эти переменные объявлены

как выходные параметры, но беспокоиться, что выбранные данные будут возвращены как результаты, не стоит: тот факт, что выходным параметрам что-либо присвоено, не означает, что вызывающий ХП клиент немедленно получит эти значения! Передача параметров осуществляется только при исполнении команды SUSPEND, а до этого мы можем использовать выходные параметры в качестве обычных переменных - в нашем примере мы именно так и делаем с параметром new_price.

Итак, внутри тела цикла BEGIN.. .END мы можем обработать значения каждой строки. Как вы помните, нам необходимо выяснить, как существующая цена соотносится со средней, и предпринять соответствующие действия. Эту процедуру сравнения мы реализуем с помощью оператора IF:

IF (new_price > avg_price) THEN /*если существующая цена больше средней цены*/

BEGIN

/*то установим новую цену, равную величине средней цены, плюс фиксированный процент */

new_price = (avg_price + avg_price*(Percent2Increase/100));

UPDATE Table_example

SET PRICE_1 = :new_price

WHERE ID = :ID;

END

ELSE

BEGIN

/* Если существующая цена меньше или равна средней цене, то установим цену, равную прежней цене, плюс половина разницы между прежней и средней ценой */

new_price = (new_pnce + ( (avg_pnce new_price)/2) ) ;

UPDATE Table_example

SET PRICE_1 = :new_price

WHERE ID = .ID;

END

Как видите, получилось достаточно большая конструкция IF, в которой трудно было бы разобраться, если бы не комментарии, заключенные в символы /**/.

Для того чтобы изменить цену в соответствии с вычисленной разницей, мы воспользуемся оператором UPDATE, который позволяет модифицировать существующие записи - одну или несколько. Для того чтобы однозначно указать, в какой записи нужно изменять цену, мы используем в условии WHERE поле первичного ключа, сравнивая его со значением переменной, в которой хранится значение ID для текущей записи: ID=:ID. Обратите внимание, что переменная ID предваряется двоеточием.

После выполнения конструкции IF...THEN...ELSE в переменных ID, NAME и new_price находятся данные, которые мы должны возвратить клиенту, вызвавшему процедуру. Для этого после IF необходимо вставить команду SUSPEND, которая перешлет данные туда, откуда вызвали ХП На время пересылки действие процедуры будет приостановлено, а когда от ХП потребуется новая запись, то она будет вновь продолжена, - и так будет продолжаться до тех пор, пока FOR SELECT...DO не переберет все записи своего запроса.

Надо отметить, что помимо команды SUSPEND, которая только приостанавливает действие хранимой процедуры, существует команда EXIT, которая прекращает хранимую процедуру после передачи строки. Однако командой EXIT пользуются достаточно редко, поскольку она нужна в основном для того, чтобы прервать цикл при достижении какого-либо условия

При этом в случае, когда процедура вызывалась оператором SELECT и завершена по EXIT, последняя извлеченная строка не будет возвращена. То есть, если вам нужно прервать процедуру и все-таки получить эту строку, надо воспользоваться последовательностью

SUSPEND;

EXIT;

Основное назначение EXIT - получение singleton-наборов данных, возвращаемых параметров путем вызова через EXECUTE PROCEDURE. В этом случае устанавливаются значения выходных параметров, но из них не формируется набор данных SQL, и выполнение

процедуры завершается.

Давайте запишем текст нашей хранимой процедуры полностью, чтобы иметь возможность охватить ее логику одним взглядом:

CREATE PROCEDURE IncreasePrices (

Percent2Increase DOUBLE PRECISION)

RETURNS (ID INTEGER, NAME VARCHAR(80),

new_price DOUBLE PRECISION) AS

DECLARE VARIABLE avg_price DOUBLE PRECISION;

BEGIN

SELECT AVG(Price_l)

FROM Table_Example

INTO :avg_price;

FOR

SELECT ID, NAME, PRICE_1

FROM Table_Example

INTO :ID, :NAME, :new_price

DO

BEGIN

/*здесь обрабатываем каждую запись*/

IF (new_pnce > avg_price) THEN /*если существующая цена больше средней цены*/

BEGIN

/*установим новую цену, равную величине средней цены, плюс фиксированный процент */

new_price = (avg_price + avg_price*(Percent2lncrease/100));

UPDATE Table_example

SET PRICE_1 = :new_price

WHERE ID = :ID;

END

ELSE

BEGIN

/* Если существующая цена меньше или равна средней цене, то устанавливает цену, равную прежней цене, плюс половина разницы между прежней и средней ценой */

new_price = (new_price + ((avg_price - new_price)/2));

UPDATE Table_example

SET PRICE_1 = :new_price

WHERE ID = :ID;

END

SUSPEND;

END

END

Данный пример хранимой процедуры иллюстрирует применение основных конструкций языка хранимых процедур и триггеров. Далее мы рассмотрим способы применения хранимых процедур для решения некоторых часто возникающих задач.

Рекурсивные хранимые процедуры

Хранимые процедуры InterBase могут быть рекурсивными. Это означает, что из хранимой процедуры можно вызвать саму себя. Допускается до 1000 уровней вложенности хранимых процедур, однако надо помнить о том, что свободные ресурсы на сервере могут закончиться раньше, чем будет достигнута максимальная вложенность ХП.

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

Давайте рассмотрим пример хранимой процедуры, которая выбирает все товары определенного типа, начиная с определенного уровня вложенности.

Пусть у нас есть следующая постановка задачи: имеем справочник товаров с иерархической структурой такого вида:

– Товары

– Бытовая техника

– Холодильники

– Трехкамерные

– Двухкамерные

– Однокамерные

– Стиральные машины

– Вертикальные

– Фронтальные

– Классические

– Узкие

– Компьютерная техника

....

Эта структура справочника категорий товаров может иметь ветки различной глубины. а также нарастать со временем. Наша задача - обеспечить выборку всех конечных элементов из справочника с "разворачивание полного имени", начиная с любого узла. Например, если мы выбираем узел "Стиральные машины", то нам надо получить следующие категории:

Стиральные машины - Вертикальные

Стиральные машины - Фронтальные Классические

Стиральные машины - Фронтальные Узкие

Определим структуру таблиц для хранения информации справочника товаров. Используем упрощенную схему для организации дерева в одной таблице:

CREATE TABLE GoodsTree

(ID_GOOD INTEGER NOT NULL,

ID_PARENT_GOOD INTEGER,

GOOD_NAME VARCHAR(80),

constraint pkGooci primary key (ID_GOOD) );

Поделиться:
Популярные книги

Черный дембель. Часть 5

Федин Андрей Анатольевич
5. Черный дембель
Фантастика:
попаданцы
альтернативная история
5.00
рейтинг книги
Черный дембель. Часть 5

30 сребреников

Распопов Дмитрий Викторович
1. 30 сребреников
Фантастика:
попаданцы
альтернативная история
фэнтези
фантастика: прочее
5.00
рейтинг книги
30 сребреников

Жребий некроманта 2

Решетов Евгений Валерьевич
2. Жребий некроманта
Фантастика:
боевая фантастика
6.87
рейтинг книги
Жребий некроманта 2

Охота на разведенку

Зайцева Мария
Любовные романы:
современные любовные романы
эро литература
6.76
рейтинг книги
Охота на разведенку

Чужбина

Седой Василий
2. Дворянская кровь
Фантастика:
попаданцы
альтернативная история
5.00
рейтинг книги
Чужбина

Возвышение Меркурия. Книга 4

Кронос Александр
4. Меркурий
Фантастика:
героическая фантастика
боевая фантастика
попаданцы
5.00
рейтинг книги
Возвышение Меркурия. Книга 4

Надуй щеки! Том 3

Вишневский Сергей Викторович
3. Чеболь за партой
Фантастика:
попаданцы
дорама
5.00
рейтинг книги
Надуй щеки! Том 3

Идеальный мир для Лекаря 16

Сапфир Олег
16. Лекарь
Фантастика:
боевая фантастика
юмористическая фантастика
аниме
5.00
рейтинг книги
Идеальный мир для Лекаря 16

По воле короля

Леви Кира
Любовные романы:
любовно-фантастические романы
5.00
рейтинг книги
По воле короля

Он тебя не любит(?)

Тоцка Тала
Любовные романы:
современные любовные романы
7.46
рейтинг книги
Он тебя не любит(?)

Курсант: назад в СССР 9

Дамиров Рафаэль
9. Курсант
Фантастика:
попаданцы
альтернативная история
5.00
рейтинг книги
Курсант: назад в СССР 9

Штуцер и тесак

Дроздов Анатолий Федорович
1. Штуцер и тесак
Фантастика:
боевая фантастика
альтернативная история
8.78
рейтинг книги
Штуцер и тесак

Камень Книга седьмая

Минин Станислав
7. Камень
Фантастика:
фэнтези
боевая фантастика
6.22
рейтинг книги
Камень Книга седьмая

Хозяйка дома в «Гиблых Пределах»

Нова Юлия
Любовные романы:
любовно-фантастические романы
5.75
рейтинг книги
Хозяйка дома в «Гиблых Пределах»