Немного об иерархии. start with connect by prior ORDER SIBLINGS BY

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

Ну вот и все.

Вас заинтересует / Intresting for you:

Отмена сессий в Oracle (ALTER ...
Отмена сессий в Oracle (ALTER ... 2624 просмотров Stepan Ushakov Thu, 01 Nov 2018, 18:04:59
STANDBY и создание нового файл...
STANDBY и создание нового файл... 5098 просмотров dbstalker Tue, 21 Nov 2017, 13:32:50
Хеш-разделение таблиц Oracle
Хеш-разделение таблиц Oracle 7979 просмотров Валерий Павлюков Tue, 21 Nov 2017, 13:32:50
Найти номер отсутствующей запи...
Найти номер отсутствующей запи... 3738 просмотров Anna Tue, 21 Nov 2017, 13:32:12