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

dbstalker, 27 июля

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               

Ну вот и все.

 

Новый комментарий

Я не спамер: введите суму 6+2



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Спутниковое тв
21 января, 3 ответа