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

на главную

Жанры

Мир 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) );

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

Аморальные уроки

Дюран Хельга
Любовные романы:
современные любовные романы
эро литература
6.00
рейтинг книги
Аморальные уроки

На границе империй. Том 8. Часть 2

INDIGO
13. Фортуна дама переменчивая
Фантастика:
космическая фантастика
попаданцы
5.00
рейтинг книги
На границе империй. Том 8. Часть 2

Первый среди равных. Книга V

Бор Жорж
5. Первый среди Равных
Фантастика:
попаданцы
аниме
фэнтези
5.00
рейтинг книги
Первый среди равных. Книга V

Барон ненавидит правила

Ренгач Евгений
8. Закон сильного
Фантастика:
попаданцы
аниме
фэнтези
5.00
рейтинг книги
Барон ненавидит правила

Магнатъ

Кулаков Алексей Иванович
4. Александр Агренев
Приключения:
исторические приключения
8.83
рейтинг книги
Магнатъ

Отмороженный 4.0

Гарцевич Евгений Александрович
4. Отмороженный
Фантастика:
боевая фантастика
постапокалипсис
рпг
5.00
рейтинг книги
Отмороженный 4.0

Земная жена на экспорт

Шах Ольга
Любовные романы:
любовно-фантастические романы
5.57
рейтинг книги
Земная жена на экспорт

Система Возвышения. (цикл 1-8) - Николай Раздоров

Раздоров Николай
Система Возвышения
Фантастика:
боевая фантастика
4.65
рейтинг книги
Система Возвышения. (цикл 1-8) - Николай Раздоров

Девяностые приближаются

Иванов Дмитрий
3. Девяностые
Фантастика:
попаданцы
альтернативная история
7.33
рейтинг книги
Девяностые приближаются

Камень Книга двенадцатая

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

Отморозок 1

Поповский Андрей Владимирович
1. Отморозок
Фантастика:
попаданцы
5.00
рейтинг книги
Отморозок 1

Звезда Чёрного Дракона

Джейн Анна
2. Нежеланная невеста
Любовные романы:
любовно-фантастические романы
4.40
рейтинг книги
Звезда Чёрного Дракона

Кодекс Охотника. Книга XXI

Винокуров Юрий
21. Кодекс Охотника
Фантастика:
фэнтези
попаданцы
аниме
5.00
рейтинг книги
Кодекс Охотника. Книга XXI

Сам себе властелин 4

Горбов Александр Михайлович
4. Сам себе властелин
Фантастика:
фэнтези
юмористическая фантастика
попаданцы
6.09
рейтинг книги
Сам себе властелин 4