3.5.1.1 Иерархические запросы в Oracle обеспечиваются фразой CONNECT BY в операторе SELECT. Эта фраза употребляется в запросе после фразы WHERE и имеет синтаксис, показанный на рис.3.22.

Рисунок 3.22 - Синтаксис фразы CONNECT BY Oracle

Oracle формирует иерархическую выборку, выполняя следующие шаги:

1. Oracle выбирает корневую строку (строки) иерархии - ту строку, которая удовлетворяет условию в выражении START WITH.
2. Затем выбираются дочерние строки для каждой корневой строки. Каждая дочерняя строка должна удовлетворять условию в фразе CONNECT BY по отношению к одной из корневых строк.
3. Выбираются следующие поколения дочерних строк. Сначала выбираются потомки строк, выбранных на шаге 2, потом - их потомки и т.д.
4. Oracle всегда выбирает потомков, вычисляя условия CONNECT BY относительно текущей родительской строки.

Если запрос содержит фразу WHERE, исключаются все строки, которые не удовлетворяют условию в фразе WHERE. Oracle вычисляет эти условия для каждой строки, а не просто удаляет всех потомков строки, которая не удовлетворяет условию.

Оператор SELECT, выполняющий иерархический запрос, не может содержать соединение.

Выражение START WITH - задает строку/строки, лежащие в корне иерархии. Это выражение определяет условие, которому должны соответствовать корневые строки. Условие может содержать вложенные запросы. Если эта фраза не задана, то все строки таблицы являются корневыми.

CONNECT BY - задает отношение между родительскими и дочерними строками в иерархии. Отношение задается "P-условием", это может быть любое сравнение, но какая-то его часть должна содержать ключевое слово PRIOR, относящееся к родительской строке.

Чтобы найти дочерние строки, Oracle вычисляет PRIOR-выражение для родительской строки, а другое выражение - для каждой строки таблицы. Строки, для которых это выражение дает истину, являются дочерними. CONNECT BY может содержать и другие условия-фильтры. CONNECT BY не может содержать вложенных запросов.

Если CONNECT BY приводит к петле, Oracle возвращает ошибку.

3.5.1.2 Запрос: выбрать фамилии всех прямых начальников сотрудника по фамилии ADAMS.

    SELECT employee_id, last_name, manager_id 
      FROM employee 
      CONNECT BY PRIOR manager_id=employee_id
      START WITH last_name= 'ADAMS'

Обработка иерархии обеспечивается выражением CONNECT BY, которое выполняет рекурсивную выборку строк: условие, по которому выбирается следующая строка, определяется значениями, выбранными в составе текущей строки. В нашем случае следующей выбирается строка, в которой значение manager_id равно значению employee_id в только что выбранной строке. Выражение START WITH определяет условие выборки первой строки.

3.5.1.3 Запрос: вывести структуру подчиненности в фирме.

    SELECT level, employee_id, last_name, manager_id
      FROM employee
      CONNECT BY PRIOR employee_id=manager_id
      START WITH last_name=
       (SELECT last_name
         FROM employee, job
         WHERE employee.job_id=job.job_id
         AND function ='PRESIDENT' )

3.5.2.1 DB2 позволяет создавать временные представления. Временные представления являются виртуальными таблицами, создающимися только для данного запроса и существующими только на время выполнения этого запроса, но в запросе к временным представлениям можно обращаться, как и к реальным таблицам. С учетом временных представлений синтаксис оператора SELECT в DB2 расширяется, как показано на рис.3.23

Рисунок 3.23 - Полный синтаксис оператора SELECT DB2

Такое представление может, прежде всего, использоваться как способ сокращения запроса. Так, решение запроса, представленного в пп.3.3.4.6, в DB2 может выглядеть так:

    WITH tmp AS
     (SELECT product_id, SUM(quantity) sss
       FROM item, sales_order
       WHERE YEAR(order_date)=1990
       GROUP BY product_id) 
    SELECT description, sss
      FROM product, tmp
      WHERE tmp.product_id=product.product_id
      AND sss=(SELECT MAX(sss) FROM tmp)

В этом решении создается временное представление tmp, формирующееся как результат запроса на выборку суммарного количества проданных в 1990 году экземпляров каждого товара. В основном запросе вместо повторения этого вложенного запроса указывается имя tmp.

Однако функциональность временных представлений этим не исчерпывается. Запросы, формирующие временные представления, могут быть рекурсивными, эта возможность и является инструментом для построения иерархических запросов.

3.5.2.2 Запрос: выбрать фамилии всех прямых начальников сотрудника по фамилии ADAMS.

    WITH temp (name, id, mid) AS
     (SELECT last_name, employee_id, manager_id
        FROM employee
        WHERE last_name= 'ADAMS'
      UNION ALL
      SELECT last_name, employee_id, manager_id
        FROM employee, temp
        WHERE employee_id=mid )
    SELECT name, id, mid
      FROM temp

Временное представление определяется запросом, содержащим объединение. Первый вложенный запрос в объединении выбирает самого ADAMS, второй - соединение таблицы, полученной на предыдущем шаге с таблицей employee по условию employee_id=manager_id. Основной запрос просто выбирает столбцы из временного представления.

Во всех рекурсивных запросах объединение должно производиться операцией UNION ALL, а не просто UNION.

3.5.2.3 Запрос: вывести структуру подчиненности в фирме.

    WITH temp (level, name, id, mid) AS
     (SELECT 1 AS level, last_name, 
             employee_id, manager_id
        FROM employee
        WHERE last_name=
           (SELECT last_name
             FROM employee, job
             WHERE employee.job_id=job.job_id
             AND function ='PRESIDENT' )
      UNION ALL
      SELECT level+1 AS level, last_name, 
             employee_id, manager_id
        FROM employee, temp
        WHERE manager_id=id )
    SELECT level, name, id, mid
     FROM temp

Временное представление определяется запросом, содержащим объединение. В первом вложенном запросе объединения выбирается президент, во втором - все подчиненные тех, кто был выбран на предыдущем шаге. Псевдостолбец level, имеющийся в Oracle, в DB2 отсутствует, но мы его вводим сами, в каждой следующей итерации его значение увеличивается на 1.