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

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

Давайте занесем в нашу таблицу следующие данные:

ID_GOOD 1 2 3 4 5 6 7 8 9 10 11 12

ID_PARENT_GOOD 0 1 1 2 2 4 4 4 5 5 10 10

GOOD_NAME GOODS Бытовая техника Компьютеры и комплектующие Холодильники Стиральные машины Трехкамерные Двухкамерные Однокамерные Вертикальные Фронтальные Узкие Классические

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

В хранимых процедурах, обрабатывающих древообразные структуры, сложилась своя терминология. Каждый элемент дерева называются узлом; а отношения между ссылающимися друг на друга узлами называется отношениями родитель-потомок. Узлы, находящиеся на самом конце дерева и не имеющие потомков, называются "листьями".

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

CREATE PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)

RETURNS (FULL_GOODS_NAME VARCHAR(1000),

ID_CHILD_GOOD INTEGER)

AS

DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);

BEGIN

/*0рганизуем внешний цикл FOR SELECT по непосредственным потомкам товара с ID_GOOD=ID_GOOD2SHOW */

FOR SELECT gtl.id_good, gtl.good_name

FROM GoodsTree gtl

WHERE gtl.id_parent_good=:ID_good2show

INTO:ID_CHILD_GOOD, :full_goods_name

DO

BEGIN

/"Проверка с помощью функции EXISTS, которая возвращает TRUE, если запрос в скобках вернет хотя бы одну строку. Если у найденного узла с ID_PARENT_GOOD = ID_CHILD_GOOD нет потомков, то он является "листом" дерева и попадает в результаты */

IF (NOT EXISTS(

SELECT * FROM GoodsTree

WHERE GoodsTree.id_parent_good=:id_child_good))

THEN

BEGIN

/* Передаем "лист" дерева в результаты */

SUSPEND;

END

ELSE

/* Для узлов, у которых есть потомки*/

BEGIN

/*сохраняем имя узла-родителя во временной переменной */

CURR_CHILD_NAME=full_goods_name;

/* рекурсивно запускаем эту процедуру */

FOR

SELECT ID_CHILD_GOOD, full_goods_name

FROM GETFULLNAME (:ID_CHILD_GOOD)

INTO:ID_CHILD_GOOD, :full_goods_name

DO BEGIN

/*добавляем имя узла-родителя к найденном., имени потомка с помощью операции конкатенации строк || */

full_goods_name=CURR_CHILD_NAME| ' ' | full_goods_name,-

SUSPEND; /* возвращаем полное имя товара*/

END

END

END

END

Если мы выполним данную процедуру с входным параметром ID_GOOD2SHOW= 1, то получим следующее:

FULL_GOODS__NAME Бытовая техника Холодильники Трехкамерные Бытовая техника Холодильники Двухкамерные Бытовая техника Холодильники Однокамерные Бытовая техника Стиральные машины Вертикальные Бытовая техника Стиральные машины Фронтальные Узкие Бытовая техника Стиральные машины Фронтальные Классические Компьютеры и комплектующие

ID_CHILD_GOOD 6 7 8 9 11 12 3

Как видите, с помощью рекурсивной хранимой процедуры мы прошлись по всему дереву категорий и вывели полное наименование категорий - "листьев", которые находятся на самых кончиках ветвей.

Заключение

На этом закончим рассмотрение основных возможностей языка хранимых процедур. Очевидно, что полностью освоить разработку хранимых процедур при чтении одной главы невозможно, однако здесь мы постарались представить и объяснить основные концепции, связанные с хранимыми процедурами. Описанные конструкции и приемы проектирования ХП могут быть применены в большинстве приложений баз данных

Часть важных вопросов, связанных с разработкой хранимых процедур, будет раскрыта в следующей главе - "Расширенные возможности языка хранимых процедур InterBase", которая посвящена обработке исключений, разрешению ошибочных ситуаций в хранимых процедурах и работе с массивами.

Расширенные возможности языка хранимых процедур InterBase

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

Обработка исключений и ошибок

Исключения

Первой из рассматриваемых особенностей языка хранимых процедур (ХП) и триггеров InterBase является возможность использовать "исключения"

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

СРЕАТЕ EXCEPTION <имя_исключения> <текст_исключения>;

Например, мы можем создать исключение такого вида:

CREATE EXCEPTION test_except 'Test exception';

Исключение легко удалить или изменить - удаление совершается командой DROP EXCEPTION <имя_удаляемого_исключения>, а изменение - ALTER EXCEPTION <имя_исключения> <текст_исключения>

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

EXCEPTION <имя_исключения>;

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

Для нашего примера создадим следующее исключение:

CREATE EXCEPTION zero_divide 'Cannot divide by zero!';

Создадим хранимую процедуру, использующую это исключение:

CREATE PROCEDURE SP_DIVIDE (

DELIMOE DOUBLE PRECISION,

DELITEL DOUBLE PRECISION)

RETURNS (

RESULT DOUBLE PRECISION)

AS

BEGIN

if (Delitel<0.0000001) then

BEGIN

EXCEPTION zero_divide;

Result=0;

END

ELSE

BEGIN

Result=Delimoe/Delitel;

END

SUSPEND;

END

Как видите, текст ХП тривиален - на входе получаем Delitel и Delimoe, затем сравниваем Delitel с 0.0000001, т. е. фактически с нулем, в пределах выбранной погрешности в одну десятимиллионную (так как вещественные числа невозможно непосредственно сравнивать из-за погрешностей в дробной части). Если Delitel близок к нулю в пределах выбранной погрешности, то мы возбуждаем исключение zero_divide. Что же происходит в случае возникновения исключения? Если мы попробуем вызвать исключение, выполняя процедуру SP_divide с нулевым делителем в isql. то получим следующее

SQL> select * from sp_divide(300,0);

RESULT

==========

Statement failed, SQLCODE = -836

exception 1

– Cannot divide by zero!

Если мы вызовем эту ХП с нулевым делителем в каком-либо другом приложении, то скорее всего получим сообщение об ошибке следующего вида:

Рис 1.4. Сообщение о возникновении исключения

Другими словами, сообщение об ошибке - это результат обработки нашего исключения сервером InterBase. Когда InterBase обнаруживает возникшее в ХП или триггере исключение он прерывает работу этой хранимой процедуры и откатывает все изменения, сделанные в текущем блоке BEGIN END, причем если ХП является процедурой-выборкой, то отменяются действия лишь до последнего оператора SUSPEND.