Материал в этом подразделе довольно сложен. Поэтому рекомендуется пропустить его при первом чтении книги.

Вы можете использовать ОТВ для реализации рекурсии, потому что ОТВ могут содержать ссылки на самих себя. Основной синтаксис ОТВ для рекурсивных запросов:

with cte_name (column_list) as (ancho r_membe r union all recurs ive_member) outer_query

Здесь ctename и column_list имеют то же самое значение, что и ОТВ для нерекурсивных запросов. Тело предложения with содержит два запроса, которые связаны с оператором union all. Первый запрос будет вызван только один раз, он запускается для аккумулирования результата рекурсии. Первый операнд в union all не ссылается на ОТВ (см. пример 6.75). Этот запрос называется опорным запросом или источником.

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

Оператор union all соединяет аккумулированные строки до тех пор, пока дополнительные строки добавляются в текущие вызовы. (Включение union all означает, что дубликаты строк будут удалены из результата.)

Под конец outerquery задает спецификацию запроса, который использует ОТВ для отыскания всех вызовов объединения обоих членов.

Определение таблицы в примере 6.75 будет использовано для демонстрации рекурсивной формы ОТВ.

 

 

 

Таблица airplane содержит четыре столбца. Столбец containingassembly задает сборку, тогда как столбец containedassembly содержит части (одна за другой), которые составляют соответствующую сборку. На рис. 6.1 дано графическое представление самолета с его составными частями.

 

 

Предположим, что таблица airplane содержит 11 строк, которые показаны в табл. 6.2. Оператор insert добавляет эти строки в таблицу airplane.

 

 

В примере 6.76 показано использование предложения with для задания запроса, который вычисляет общую стоимость каждой сборки.

 

 

Предложение with определяет ОТВ с именем listofparts, которое содержит три столбца: assembly, quantity и cost. Первый оператор select в примере 6.76 будет вызван только один раз для аккумулирования результатов первого шага в рекурсивном процессе.

Оператор select в последней строке примера 6.76 отображает следующий результат:

 

 

Первые пять строк в этом наборе показывают результирующий набор первого вызова опорного члена запроса в примере 6.67. Все остальные строки являются результатом рекурсивного члена (вторая часть) запроса в том же примере. Рекурсивный член запроса будет вызван дважды: первый раз для сборки фюзеляжа, а второй раз для самого самолета.

Запрос в примере 6.77 будет использован для получения стоимости каждой сборки со всеми ее составными элементами.

 

 

Результат выполнения запроса примера 6.77 будет следующим:

 

 

Существует несколько ограничений для ОТВ в рекурсивных запросах:

♦ определение ОТВ должно содержать как минимум два оператора select (опорный член и один рекурсивный член), совмещенные с оператором union all;

♦ количество столбцов в опорном и рекурсивном члене должно быть одним и тем же (это является прямым следствием использования оператора union all);

♦ тип данных столбца в рекурсивном члене должен быть тем же самым, что и тип данных соответствующего столбца в опорном члене;

♦ предложение from рекурсивного члена должно ссылаться только один раз на имя ОТВ;

♦ следующие опции недопустимы в определении части рекурсивного члена: select distinct, group by, having, агрегатные функции, top, подзапросы. (Кроме того, оператором соединения, который допустим в определении запроса, является только оператор внутреннего соединения.)