Oracle – это реляционная база данных. Данные в базе хранятся в виде двумерных таблиц: есть строки и столбцы. Однако в жизни довольно часто приходиться сталкиваться с иерархической структурой данных. Простой пример: структура папок на вашем компьютере.
Безусловно, во многих случаях иерархию можно обойти путем создания отдельных таблиц для каждого уровня вхождения. Но если бездна иерархии заранее не известна?
На помощь ораклистам приходит вот такая конструкция.
Попробуем дерево папок на нашем компьютере внести в нашу базу данных, а затем его нарисовать средствами оракла.
Создаем тестовую табличку. Запись состоит из 3 полей: идентификатор узла, родительский узел, название узла.
CREATE TABLE my_table (id number, parent_id number, name number);
Вносим в табличку записи.
INSERT INTO my_table VALUES (1,0, 1); INSERT INTO my_table VALUES (2, 1, 10); INSERT INTO my_table VALUES (3, 2, 11); INSERT INTO my_table VALUES (4, 3, 111); INSERT INTO my_table VALUES (5, 4, 1111); INSERT INTO my_table VALUES (6, 5, 11111); INSERT INTO my_table VALUES (7, 3, 112); INSERT INTO my_table VALUES (8, 7, 1122); INSERT INTO my_table VALUES (9, 2, 12); INSERT INTO my_table VALUES (10, 9, 1122); INSERT INTO my_table VALUES (11, 10, 112233); INSERT INTO my_table VALUES (12, 2, 13); INSERT INTO my_table VALUES (13, 2, 14); INSERT INTO my_table VALUES (14, 1, 20); INSERT INTO my_table VALUES (15, 14, 21); INSERT INTO my_table VALUES (16, 15, 2211); INSERT INTO my_table VALUES (17, 14, 22); INSERT INTO my_table VALUES (18, 17, 2211); INSERT INTO my_table VALUES (19, 18, 221133); INSERT INTO my_table VALUES (20, 14, 23); INSERT INTO my_table VALUES (21, 1, 30); INSERT INTO my_table VALUES (22, 21, 31);
Теперь подготовим запросы на выборку.
Разберемся с обязательной конструкцией CONNECT BY. Эта конструкция задает условие рабочие мероприятия цикла. Чтобы возвести иерархию в запросе нужно каким-то образом связывать запись предыдущую и последующую. Для этого Оракл придумал оператор PRIOR, который позволяет работая с текущей записью обратиться к предыдущей. Конструкция connect by prior id=parent_id позволит нам перебрать все записи таблицы и для каждой найти родительскую запись, если таковая есть.
Конструкция START WITH задает корневой узел. Для нашего случая start with id=1.
Оракл также предлагает использовать псевдостолбец level, показывающий уровень записи по отношению к корневому узлу.
ORDER SIBLINGS BY – эта конструкция позволяет разбирать записи в пределах одного уровня иерархии.
Используя вышеупомянутые конструкции и операторы можно возвести иерархию, выполнив следующий запрос:
select a.*,level from my_table a start with id=1 connect by prior id=parent_id ORDER SIBLINGS BY name;
ID PARENT_ID NAME LEVEL 1 0 1 1 2 1 10 2 3 2 11 3 4 3 111 4 5 4 1111 5 6 5 11111 6 7 3 112 4 8 7 1122 5 9 2 12 3 10 9 1122 4 11 10 112233 5 12 2 13 3 13 2 14 3 14 1 20 2 15 14 21 3 16 15 2211 4 17 14 22 3 18 17 2211 4 19 18 221133 5 20 14 23 3 21 1 30 2 22 21 31 3
Вот так мы и получили иерархию.
А теперь попробуем красиво изобразить полученную иерархию.
select LPAD(' ', 8 * level) || name from my_table a start with id=1
ORDER SIBLINGS BY name;
1
10
11
111
1111
11111
112
1122
12
1122
112233
13
14
20
21
2211
22
2211
221133
23
30
31
И контрольный выстрел. Есть в ORACLE такая функция SYS_CONNECT_BY_PATH. О ней написано здесь. Используем ее себе во благо:
select SYS_CONNECT_BY_PATH(name, '/') AS Path from my_table a start with id=1 connect by prior id=parent_id ORDER SIBLINGS BY name;
/1 /1/10 /1/10/11 /1/10/11/111 /1/10/11/111/1111 /1/10/11/111/1111/11111 /1/10/11/112 /1/10/11/112/1122 /1/10/12 /1/10/12/1122 /1/10/12/1122/112233 /1/10/13 /1/10/14 /1/20 /1/20/21 /1/20/21/2211 /1/20/22 /1/20/22/2211 /1/20/22/2211/221133 /1/20/23 /1/30 /1/30/31
Ну вот и все.
