В этой публикации объясняется, как работать со специфическими методами, которые присутствуют в базе данных Oracle, для работы с Деревьями. Итак, сразу к делу...

--Создаем таблицу для экспериментов с деревьями.

create table my_tree (
  id number,
  id_parent number,
  nm varchar2(100)
  );

--Добавляем нашей тестовой таблице индекс (для порядка).

create index idx_my_tree on my_tree(id,id_parent);

--Заполняем нашу тестовую таблицу классификатора (категориями продуктов).

insert into MY_TREE values (1,null, 'Продукты');
insert into MY_TREE values (2,1, 'Колбасы');
insert into MY_TREE values (3,1, 'Полуфабоикаты');
insert into MY_TREE values (4,1, 'Фрукты');
insert into MY_TREE values (5,3, 'Котлеты');
insert into MY_TREE values (6,3, 'Пельмени');
insert into MY_TREE values (7,3, 'Манты');
insert into MY_TREE values (8,3, 'Бифштексы');
insert into MY_TREE values (9,2, 'Копченая');
insert into MY_TREE values (10,2, 'Вареная');
insert into MY_TREE values (11,4, 'Помидоры');
insert into MY_TREE values (12,4, 'Огурцы');
insert into MY_TREE values (13,4, 'Бананы');
insert into MY_TREE values (14,2, 'Салями');

--Смотроим что у нас получилось в табличке.

select * from MY_TREE;

--Вот что получилось:

"ID" "ID_PARENT" "NM"
1 "Продукты"
2 1 "Колбасы"
3 1 "Полуфабоикаты"
4 1 "Фрукты"
5 3 "Котлеты"
6 3 "Пельмени"
7 3 "Манты"
8 3 "Бифштексы"
9 2 "Копченая"
10 2 "Вареная"
11 4 "Помидоры"
12 4 "Огурцы"
13 4 "Бананы"
14 2 "Салями"

--В таком виде, очевидно работать с данными не солвсем удобно, однако в Oracle есть замесательный способ CONNECT BY, который позволяет создавать иерархию всего одним запросом выстроить все возможные деревья:

select id, id_parent, nm from my_tree connect by prior id = id_parent;

--Вот такой результат получим:

"ID" "ID_PARENT" "NM"
2 1 "Колбасы"
9 2 "Копченая"
14 2 "Салями"
10 2 "Вареная"
4 1 "Фрукты"
11 4 "Помидоры"
13 4 "Бананы"
12 4 "Огурцы"
3 1 "Полуфабоикаты"
5 3 "Котлеты"
8 3 "Бифштексы"
7 3 "Манты"
6 3 "Пельмени"
9 2 "Копченая"
14 2 "Салями"
10 2 "Вареная"
5 3 "Котлеты"
8 3 "Бифштексы"
7 3 "Манты"
6 3 "Пельмени"
11 4 "Помидоры"
13 4 "Бананы"
12 4 "Огурцы"
1 "Продукты"
2 1 "Колбасы"
9 2 "Копченая"
14 2 "Салями"
10 2 "Вареная"
4 1 "Фрукты"
11 4 "Помидоры"
13 4 "Бананы"
12 4 "Огурцы"
3 1 "Полуфабоикаты"
5 3 "Котлеты"
8 3 "Бифштексы"
7 3 "Манты"
6 3 "Пельмени"

--Добавим в запрос конструкцию START WITH, получим более удобный для работы вид.

select id, id_parent, nm from my_tree connect by prior id = id_parent start with id in 1;

--Теперь результат выглядит намного более понятно и удобно:

"ID" "ID_PARENT" "NM"
1 "Продукты"
2 1 "Колбасы"
9 2 "Копченая"
10 2 "Вареная"
14 2 "Салями"
3 1 "Полуфабоикаты"
5 3 "Котлеты"
6 3 "Пельмени"
7 3 "Манты"
8 3 "Бифштексы"
4 1 "Фрукты"
11 4 "Помидоры"
12 4 "Огурцы"
13 4 "Бананы"

--Добавим псевдоколонку LEVEL:

select id, id_parent, nm, level from my_tree connect by prior id = id_parent start with id in 1;

--И теперь мы наглядно видим к какому уровню принадлежит та или иная запись:

"ID" "ID_PARENT" "NM" "LEVEL"
1 "Продукты" 1
2 1 "Колбасы" 2
9 2 "Копченая" 3
10 2 "Вареная" 3
14 2 "Салями" 3
3 1 "Полуфабоикаты" 2
5 3 "Котлеты" 3
6 3 "Пельмени" 3
7 3 "Манты" 3
8 3 "Бифштексы" 3
4 1 "Фрукты" 2
11 4 "Помидоры" 3
12 4 "Огурцы" 3
13 4 "Бананы" 3

--Добавим сортировку дерева по наименованию, обычный order by здесь не поможет, делаем так:

select id, id_parent, nm, level from my_tree connect by prior id = id_parent start with id in 1 order siblings by nm;

--И теперь внутри групп всё отсортировано по наименованию:

"ID" "ID_PARENT" "NM" "LEVEL"
1 "Продукты" 1
2 1 "Колбасы" 2
10 2 "Вареная" 3
9 2 "Копченая" 3
14 2 "Салями" 3
3 1 "Полуфабоикаты" 2
8 3 "Бифштексы" 3
5 3 "Котлеты" 3
7 3 "Манты" 3
6 3 "Пельмени" 3
4 1 "Фрукты" 2
13 4 "Бананы" 3
12 4 "Огурцы" 3
11 4 "Помидоры" 3

--Ещё одна очень интересная функция SYS_CONNECT_BY_PATH(имя_поля, 'разделитель'), которая позволяет вывести наименование в виде пути (как в проводнике).

select id, id_parent, SYS_CONNECT_BY_PATH(nm, '>') as path_nm, level from my_tree connect by prior id = id_parent start with id in 1 order siblings by nm;

--Вот как это выглядит:

"ID" "ID_PARENT" "PATH_NM" "LEVEL"
1 ">Продукты" 1
2 1 ">Продукты>Колбасы" 2
10 2 ">Продукты>Колбасы>Вареная" 3
9 2 ">Продукты>Колбасы>Копченая" 3
14 2 ">Продукты>Колбасы>Салями" 3
3 1 ">Продукты>Полуфабоикаты" 2
8 3 ">Продукты>Полуфабоикаты>Бифштексы" 3
5 3 ">Продукты>Полуфабоикаты>Котлеты" 3
7 3 ">Продукты>Полуфабоикаты>Манты" 3
6 3 ">Продукты>Полуфабоикаты>Пельмени" 3
4 1 ">Продукты>Фрукты" 2
13 4 ">Продукты>Фрукты>Бананы" 3
12 4 ">Продукты>Фрукты>Огурцы" 3
11 4 ">Продукты>Фрукты>Помидоры" 3

--Ещё интересная функция CONNECT_BY_ISLEAF, проверяет есть ли у ветки потомки (Если потомков нет то 1 если есть то 0).

select id, id_parent, SYS_CONNECT_BY_PATH(nm, '>') as path_nm, level, CONNECT_BY_ISLEAF as leaf from my_tree connect by prior id = id_parent start with id in 1 order siblings by nm;

-- Вот как это выглядит:

"ID" "ID_PARENT" "PATH_NM" "LEVEL" "LEAF"
1 ">Продукты" 1 0
2 1 ">Продукты>Колбасы" 2 0
10 2 ">Продукты>Колбасы>Вареная" 3 1
9 2 ">Продукты>Колбасы>Копченая" 3 1
14 2 ">Продукты>Колбасы>Салями" 3 1
3 1 ">Продукты>Полуфабоикаты" 2 0
8 3 ">Продукты>Полуфабоикаты>Бифштексы" 3 1
5 3 ">Продукты>Полуфабоикаты>Котлеты" 3 1
7 3 ">Продукты>Полуфабоикаты>Манты" 3 1
6 3 ">Продукты>Полуфабоикаты>Пельмени" 3 1
4 1 ">Продукты>Фрукты" 2 0
13 4 ">Продукты>Фрукты>Бананы" 3 1
12 4 ">Продукты>Фрукты>Огурцы" 3 1
11 4 ">Продукты>Фрукты>Помидоры" 3 1

--Теперь сделаем Петлю, и посмотрим как избежать ошибки которую порождает данная криворукость :)

update my_tree set id_parent = 2 where id = 2;

--Пытаемся сделать выборку начиная с второго элемента:

select id, id_parent, SYS_CONNECT_BY_PATH(nm, '>') as path_nm, level, CONNECT_BY_ISLEAF as leaf from my_tree connect by prior id = id_parent start with id in 2 order siblings by nm;

--Получаем ошибку вида:

ORA-01436: CONNECT BY loop in user data

--Избежать этого можно с помощью команды NOCYCLE так:

select id, id_parent, SYS_CONNECT_BY_PATH(nm, '>') as path_nm, level, CONNECT_BY_ISLEAF as leaf from my_tree connect by nocycle prior id = id_parent start with id in 2 order siblings by nm;

--Получится, вот так:

"ID" "ID_PARENT" "PATH_NM" "LEVEL" "LEAF"
2 2 ">Колбасы" 1 0
10 2 ">Колбасы>Вареная" 2 1
9 2 ">Колбасы>Копченая" 2 1
14 2 ">Колбасы>Салями" 2 1

Вот, пожалуй и все на мой взгляд необходимое, для работы с деревьями в Oracle.