Глава 7. СПРАВОЧНИКИ

Работа с таблицами справочников, хранимые процедуры CLASS_TREE, CLASS_ROOT

Предположим, у нас имеются классы справочников (таблицы):

• Товары (GOODS)
• Крепеж (METAL_ITEMS)
• Винты (SCREWS)
• Разный крепеж (OTHER_METAL_ITEMS)
• Обувь (SHOES)

Мы можем легко запросить все краткие наименования объектов класса « Обувь», объединив таблицу наименований объектов OBJECT_NAMES с таблицей SHOES по полю ID:

select o1.object_id, o1.short_name
from object_names o1, shoes s
where o1.object_id = s.id;

Так как все объекты в таблице OBJECT_NAMES хранят свои ID в поле OBJECT_ID, то, объединяя OBJECT_NAMES с любой таблицей класса по полю ID, мы получим наименования любого класса объектов, какой только существует в системе. Например, мы можем получить весь «Крепеж», в который войдут и «Винты » и «Разный крепеж»:

select o1.object_id, o1.short_name
from object_names o1, metal_items m
where o1.object_id = m.id;

Или даже все «Товары» :

select o1.object_id, o1.short _name

from object_names o1, goods g

where o1.object_id = g.id ;

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

select
  sum(a.debit) – sum(a.credit) amount,
  o1.short_name shoes_color
from
  shoes s,
  acc_turn a,
  object_names o1
where
  a.object_id = s.id and
  o1.object_id = s.color and a.acc_id = 123
group by
  o1.short_name;

В приведенном примере мы использовали запрос с группировкой. Предполагалось , что ACC_ID главного склада равняется 123.

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

select
  sum(a.debit) – sum(a.credit) amount,
  c.name class_name
from
  acc_turn a,
  object_names o1,
  class c
where
  a.object_id = o1.object_id and
  o1.class_id = c.class_id and
  a.acc_id = 123
group by
  c.name;

А теперь предположим, что нам нужен список всех классов . Как нам его получить? Это очень просто – достаточно запросить таблицу CLASS:

select class_id, name from class c

А если нам нужен список всех классов, потомков класса «Товары»? Простой запрос типа

select class_id, name from class c
where parent_id = 100

нам вернет только прямых потомков класса «товары», но не все дочерние классы. Конечно, можно написать хранимую процедуру обхода дерева классов, но проще воспользоваться готовой системной процедурой CLASS_TREE, которая возвращает все сочетания CLASS _ID по правилу старший-младший для каждого класса и всех его потомков.

Результирующий набор, поставляемый хранимой процедурой CLASS_TREE

Запрос вида:

select c.class_id, c.name
from class_tree ct, class c
where c.class_id = ct.child_id and  ct.parent_id = 100

вернет список всех классов, потомков класса «Товары», включая его самого.

Последняя системная хранимая процедура, имеющая отношение к справочной системе это процедура CLASS_ROOT. Эта процедура позволяет получить CLASS_ID корневого класса для любого выбранного класса, если нам известен его CLASS_ID. Она имеет входной параметр типа INTEGER, в котором при вызове передается CLASS_ID интересующего нас класса. Процедура возвращает один параметр: ROOT_ID, в котором передает нам CLASS_ID корневого класса. Например, для класса «Винты» корневым является класс «Товары». Использовать процедуру CLASS_ROOT несложно:

select c.class_id, c.name
from class_root(1005) cr, class c
where c.class_id = cr.root_id

Здесь предполагается, что класс «Винты» имеет CLASS _ID = 1005.