Connect by prior и иерархические запросы в Oracle

Иерархические запросы в 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' )

В решении используется условие CONNECT BY, инвертированное по сравнению с предыдущей задачей. В этом случае следующей выбирается строка, в которой значение employee_id равно значению manager_id в только что выбранной строке, что обеспечивает движение от корня дерева вниз. Начальной строкой является та, которая содержит код должности, соответствующий функции PRESIDENT. Выборки Oracle, использующие иерархические свойства запросов, могут использовать псевдостолбец level. Этот псевдостолбец имеет значение 1 для узла дерева, находящегося в корне, 2 – для узлов, являющихся непосредственными потомками корневого, и т.д.

Источник: http://khpi-iip.mipk.kharkiv.edu/library/extent/dbms/sql/35.html
Оффдоки: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm

Оставить комментарий