Рекурсивные запросы в Oracle - CONNECT BY

Чтобы понять рекурсию, сначала надо понять рекурсию.

Здесь пойдет речь об иерархичных запросах, которые часто называют рекурсивными, но будет правильнее назвать их иерархичными запросами с рекурсивным поведением.

Для дальнейших экспериментов создадим таблицу с иерархией времен года и месяцев.

 

create table table_tmp
(id number,
parent_id number,
name varchar2(50));

 

Заполним

insert into table_tmp values(1, null, 'Year');
insert into table_tmp values(2, 1, 'Winter');
insert into table_tmp values(3, 2, 'December');
insert into table_tmp values(4, 2, 'January');
insert into table_tmp values(5, 1, 'Spring');
insert into table_tmp values(6, 5, 'March');
insert into table_tmp values(7, 5, 'April');
insert into table_tmp values(8, 5, 'May');
insert into table_tmp values(9, 1, 'Summer');
insert into table_tmp values(10, 9, 'June');
insert into table_tmp values(11, 9, 'July');
insert into table_tmp values(12, 9, 'August');
insert into table_tmp values(13, 1, 'Autumn');
insert into table_tmp values(14, 13, 'September');
insert into table_tmp values(15, 13, 'October');
insert into table_tmp values(16, 13, 'November');

Немного теории. 

Описание синтаксиса в документации: 

 

 

Единственно важное условие для построения иерархического запроса - оператор CONNECT BY, остальное по необходимости.

Самый простой пример использования - генерация последовательности чисел. Например, месяцы от 1 до 12:

 

select level r from dual connect by level <= 12;

  

Условие после CONNECT BY обязательно. Здесь указываем сколько продолжать цикл.

Хозяйке на заметку: если нужно размножить некую строку, к примеру на 10 строк, то это можно сделать вот так:

 

select 1 as value r from dual connect by rownum <= 10;

 

LEVEL - это псевдостолбец, в нем записывается уровень записи по отношению к корневой. 1-ая запись будет иметь уровень 1, ее потомки - уровень 2, потомки потомков - уровень 3, etc.

Но в случае генерации чисел этого не видно, потому что иерархия фактически не построена.

Определимся сначала откуда стартовать.

 

START WITH - необязательный оператор, здесь указываем какая строка/строки будет корневой. Условие может быть числом, литералом, null и даже функцией - substr(column,1,1) = 'A' - или внутренним запросом. 

 

Для построения иерархии нужен специальный оператор PRIOR. Это обычный унарный оператор, такой же как + или -, только он указывает на предыдущую в иерархии запись, грубо говоря, минус 1 уровень.

 

parent_id = PRIOR id

 

Итак, иерархичный запрос будет следующим:

 

select lpad(' ',3 * level)||name as tree,  -- выстраиваем визуальное дерево

 sys_connect_by_path(name, '/') as Path  -- собираем путь, выглядящий как путь к файлу к папке
  from table_tmp
  start with parent_id is null    --- задаем старт иерархии
  connect by prior id = parent_id -- увязываем
order siblings by id; -- сортируем в пределах общего родителя, если order by без siblings иерархия при сортировке визуально развалится

 

Результат:

 

TREE	PATH
Year	/Year
Winter	/Year/Winter
December	/Year/Winter/December
January	/Year/Winter/January
February	/Year/Winter/February
Spring	/Year/Spring
March	/Year/Spring/March
April	/Year/Spring/April
May	/Year/Spring/May
Summer	/Year/Summer
June	/Year/Summer/June
July	/Year/Summer/Julay
August	/Year/Summer/August
Autumn	/Year/Autumn
September	/Year/Autumn/September
October	/Year/Autumn/October
November	/Year/Autumn/November

 

Еще может быть полезен псевдостолбец CONNECT_BY_ISLEAF. Его можно использовать так же, как LEVEL. В этом псевдостолбце напротив каждой строки проставляется 0 или 1. Если есть потомки – проставится 0. Если потомков нет, такой узел в дереве называется “листом”, тогда и значение в поле CONNECT_BY_ISLEAF будет равно 1.

 

Помимо оператора PRIOR, который ссылался к родительской записи, есть другой унарный оператор CONNECT_BY_ROOT, который ссылается на корневую запись, т.е. на самую первую в выборке.

 

select id,

       parent_id,

       name,

       level,

       CONNECT_BY_ISLEAF as IsLeaf,   -- определяем, является ли эта строка последним уровнем иерахии, т.е не имеет потомков

       PRIOR name as Parent_name,   -- находим родительское имя (можно ID или любой другой родительский  параметр)

       CONNECT_BY_ROOT name as Root  -- находим корень всего зла, то есть самый первый уровень

  from table_tmp

  start with parent_id is null

connect by prior id = parent_id

order siblings by id;

 

Результат:

 

ID	PARENT_ID	NAME	LEVEL	ISLEAF	PARENT_NAME	ROOT
1		Year	1	0		Year
2	1	Winter	2	0	Year	Year
3	2	December	3	1	Winter	Year
4	2	January	3	1	Winter	Year
4	2	February	3	1	Winter	Year
5	1	Spring	2	0	Year	Year
6	5	March	3	1	Spring	Year
7	5	April	3	1	Spring	Year
8	5	May	3	1	Spring	Year
9	1	Summer	2	0	Year	Year
10	9	June	3	1	Summer	Year
11	9	Julay	3	1	Summer	Year
12	9	August	3	1	Summer	Year
13	1	Autumn	2	0	Year	Year
14	13	September	3	1	Autumn	Year
15	13	October	3	1	Autumn	Year
16	13	November	3	1	Autumn	Year

 

Все это результаты правильно построенной в таблице иерархии, когда есть все основные элементы иерархии: root, parent, child, и они между собой правильно взаимосвязаны и становятся branch или leaf.

 

Если иерархия построена неправильно, то произойдет либо выпадение уровней, либо ошибка ORA-01436.

 

Изменим иерархию - проставим второму уровню Autumn, который соотвествует временам года, parent_id равный ID сентября (September)

 

update table_tmp set parent_id=14 where id=13;

 

Если запустим селект с построением дерева, то получим то, что время года Autumn и его потомки, выпадут из иерархии. Это произошло потому, что построение началось с корня/предка, а Autumn своего предка "потерял". В принципе можно було проставить любое другое число, главное оборвать связь с корнем.

 

Результат наглядно -  Autumn и его потомки отсутствуют.

 

TREE	PATH
Year	/Year
Winter	/Year/Winter
December	/Year/Winter/December
January	/Year/Winter/January
February	/Year/Winter/February
Spring	/Year/Spring
March	/Year/Spring/March
April	/Year/Spring/April
May	/Year/Spring/May
Summer	/Year/Summer
June	/Year/Summer/June
July	/Year/Summer/Julay
August	/Year/Summer/August

 

Но если попробовать сейчас простроить иерархию осени - Autumn, то есть start with id = 13, то выпадет ошибка:

ORA-01436: CONNECT BY loop in user data.

 

Это так называемая "петля".

 

Если в данных содержатся "петли" – это явно ошибка, в деревьях не бывает петель.
Для этого служит оператор NOCYCLE после CONNECT BY.
А чтобы определить “больной” участок, используется псевдостолбец CONNECT_BY_ISCYCLE – в нем во всех "хороших" строках будет записано 0, а в тех, которые приводят к петлям, окажется 1. Применяется только с NOCYCLE.

 

select CONNECT_BY_ISCYCLE as IsCycle,

       id,

       parent_id,

       name,

       level

 from table_tmp

 start with parent_id = 13

connect by nocycle prior id = parent_id;

 

ISCYCLE	ID	PARENT_ID	NAME	LEVEL
0	14	13	September	1
1	13	14	Autumn	2
0	15	13	October	3
0	16	13	November	3
0	15	13	October	1
0	16	13	November	1

 

Практические случаи:

 

1) Найти пропущенные дни. Возьмем массив из 7 дней (недели) с произвольными датами, и пропустим среду и пятницу, то есть 3 и 5 день. Найдем эти дни.

 

    with t_data as (select date '2020-01-04' as ndate from dual union all

                    select date '2020-01-05' as ndate from dual union all

                    select date '2020-01-07' as ndate from dual union all

                    select date '2020-01-09' as ndate from dual union all

                    select date '2020-01-10' as ndate from dual)

 

select * from t_data t1

full join (select min_date + level as all_ndate

             from (select min(ndate) -1  as min_date from t_data)

                   connect by level <=7

          ) t2 on t1.ndate = t2.all_ndate;

 

 2) Преобразовать строку в столбец:

 

with t_data as (select '1;2;3;4;5' as value from dual)

 

 select regexp_substr(value, '[^;]+', 1, level) as value_rezult

from t_data

connect by instr(value, ';', 1, level - 1) > 0;

 

или похожая задача - распарсить строки:

 

with t_data as (select '1;Sea;Water;4;5' as value, 1 id from dual union all

                select '1;Mountain;Rock;4;5' as value, 2 id from dual)

 

select id -1 + level as id,

       regexp_substr(value, '[^;]+', 1, level) as value_rezult

from t_data

connect by LEVEL <= instr(value, ';', 1, level)

and prior dbms_random.value is not null

and prior id = id;

 

Зачем нужна строка prior dbms_random.value is not null?

Если ее не будет, то получим знакомую ошибку "петли" - ORA-01436: CONNECT BY loop in user data.

Это связано с тем, как Oracle определяет зацикливание: если у очередной строки значения всех полей, которые употребляются с prior, равно значению этих полей из предыдущего уровня рекурсии, значит запрос зациклился. На каждом уровне хотя бы одно поле, употребленное с prior, должно отличаться. Это и обеспечивает DBMS_RANDOM, дающий подряд неповторяющиеся значения.

А как видно из содержимого WITH такого не будет, ID для пяти строк после распарсивания будет одинаковый.

  

Также вместо dbms_random.value многие советут использовать sys_guid, чтобы обойтись без PL/SQL контекста. 

    

and prior sys_guid() is not null

 

3) И просто построить иерархию, визуализируя ее, например для отчетности, как в примерах выше - через отступы, добавление символов или построение пути.

 

Продолжение следует...

 

Источники:

Иерархические (рекурсивные) запросы 

Рекурсивные запросы в Oracle 

Hierarchical Queries (Oracle Docs)

Почему зацикливается запрос без prior dbms_random.value в запросах с connect by?