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

На Лист. 25 был приведен пример запроса, в котором ищутся подчиненные Иванова. Возникает вопрос, как построить всю иерархию подчиненных, а не только его непосредственных подчиненных. Фактически мы должны применить этот же самый запрос Лист. 25 к результату выполнения его самого, затем еще раз и т.д., пока будут находиться подчиненные на всё более низких уровнях иерархии.

Построить иерархию всех подчиненных Иванова в «в длину» (запрос приведен на Лист. 41, результат -Табл. 54).

Рекурсивные запросы можно опознать по ключевому слову WITH RECURSIVE. Чтобы быть вызванным рекурсивно, запрос фактически должен иметь имя (в данном случае - Прод). Запрос состоит из двух частей, объединяемых с помощью UNION. В первой части – начальники, во второй части – их подчиненные. Запрос находит первого подчиненного, затем первого подчиненного первого и т.д. Такие запросы называются запросами на поиск «в длину»

Лист. 41. Рекурсивный запрос «в длину»

WITH RECURSIVE Прод (N, Имя) as

(SELECT N, Имя

FROM Продавцы

WHERE Имя = ‘Иванов’

UNION ALL

SELECT Продавцы. N, Продавцы.Имя

FROM Продавцы, Прод

WHERE Прод.N = Продавцы.N_Начальника);

SELECT * FROM Прод;

Табл. 54. Результат рекурсивного запроса «в длину»

34 Рекурсия «в длину»
N	Имя
1	Иванов
2	Петров
4	Шмидт
5	Зайцев
3	Сидоров

Если нам нужно сперва перечислить всех подчиненных Иванова, затем подчиненных подчиненных Иванова и т.д., то нам нужно использовать рекурсивные запросы «в ширину», используя оператор SEARCH BREADTH FIRST. В запросе с помощью оператора SET устанавливается номер итерации.

Построить иерархию всех подчиненных Иванова «в ширину» (запрос приведен на Лист. 42, результат -Табл. 55).

Лист. 42. Рекурсивный запрос «в ширину»

WITH RECURSIVE Прод (N, Имя, N_Начальника) as

(SELECT N, Имя

FROM Продавцы

WHERE Имя = ‘Иванов’

UNION ALL

SELECT Продавцы. N, Продавцы. Имя

FROM Продавцы, Прод

WHERE Прод.N = Продавцы.N_Начальника);

SEARCH BREADTH FIRST BY N_Начальника, N

SET order_column

SELECT * FROM Прод

ORDER BY order_column;

Табл. 55. Результат запроса «в ширину»

35 Рекурсия «в ширину»
N	Имя
1	Иванов
2	Петров
3	Сидоров
4	Шмидт
5	Зайцев

При рекурсивном запросе возможна ситуация зацикливания. Хотя этого и нет в нашем примере, можно предположить, что бывают ситуации, когда один коллектив работает над двумя проектами, причем один человек в первом проекте – начальник, а во втором – подчиненный и наоборот, второй человек в первом проекте – подчиненный. А во втором – начальник. В этом случае предыдущие рекурсивные запросы зациклятся.

Построить иерархию всех подчиненных Иванова, учесть возможность зацикливания (Лист. 43).

Лист. 43. Рекурсивный запрос с учетом зацикливания

WITH RECURSIVE Прод (N, Имя) as

(SELECT N, Имя

FROM Продавцы

WHERE Имя = ‘Иванов’

UNION ALL

SELECT Продавцы. N, Продавцы. Имя

FROM Продавцы, Прод

WHERE Прод.N = Продавцы .N_Начальника);

CYRCLE N

SET cyrclemark to “Y” default “N”

USING cyrclepath

SELECT * FROM Прод

ORDER BY N;

Д/З . Для примера из Д/З придумайте следующие запросы: