Иерархические запросы в Oracle обеспечиваются фразой CONNECT BY в операторе SELECT. Эта фраза употребляется в запросе после фразы WHERE и имеет синтаксис, показанный на рис.3.22.
Рисунок 3.22 – Синтаксис фразы CONNECT BY Oracle
Oracle формирует иерархическую выборку, выполняя следующие шаги:
- Oracle выбирает корневую строку (строки) иерархии – ту строку, которая удовлетворяет условию в выражении START WITH.
- Затем выбираются дочерние строки для каждой корневой строки. Каждая дочерняя строка должна удовлетворять условию в фразе CONNECT BY по отношению к одной из корневых строк.
- Выбираются следующие поколения дочерних строк. Сначала выбираются потомки строк, выбранных на шаге 2, потом – их потомки и т.д.
- 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.
Обработка иерархии обеспечивается выражением CONNECT BY, которое выполняет рекурсивную выборку строк: условие, по которому выбирается следующая строка, определяется значениями, выбранными в составе текущей строки. В нашем случае следующей выбирается строка, в которой значение manager_id равно значению employee_id в только что выбранной строке. Выражение START WITH определяет условие выборки первой строки.
3.5.1.3 Запрос: вывести структуру подчиненности в фирме.
В решении используется условие CONNECT BY, инвертированное по сравнению с предыдущей задачей. В этом случае следующей выбирается строка, в которой значение employee_id равно значению manager_id в только что выбранной строке, что обеспечивает движение от корня дерева вниз. Начальной строкой является та, которая содержит код должности, соответствующий функции PRESIDENT. Выборки Oracle, использующие иерархические свойства запросов, могут использовать псевдостолбец level. Этот псевдостолбец имеет значение 1 для узла дерева, находящегося в корне, 2 – для узлов, являющихся непосредственными потомками корневого, и т.д.
Запись опубликована 02.01.2011 в 3:21 пп и размещена в рубрике Oracle PL/SQL. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
Если в таблице содержатся иерархические данные (данные, которые могут быть сгруппированы в уровни с размещением родительских данных на более высоких, а дочерних — на более низких уровнях), можно использовать поддерживаемые Oracle иерархические запросы. В иерархических запросах обычно применяются следующие конструкции:
- START WITH , которая обозначает корневую строку или строки иерархического отношения;
- CONNECT BY , которая задает отношения между родительскими и дочерними строками вместе с операций PRIOR , которая всегда указывает на родительскую строку.
В листинге ниже приведен пример иерархического отношения между столбцами сотрудников и менеджеров. Конструкция CONNECT BY указывает, как должно выглядеть это отношение, а конструкция START WITH — с какого места оператору следует начинать отслеживать иерархию.
Выбор данных из нескольких таблиц
До сих пор главным образом показывалось, как выполнять различные DML-операции в отношении одиночных таблиц, в том числе с применением различных SQL-функций и выражений. В реальной жизни, однако, чаще всего требуется извлекать данные не из одной, а сразу из нескольких таблиц или представлений. При извлечении данных из нескольких таблиц, таблицы необходимо соединять (join). Под соединением подразумевается запрос, который позволяет объединять данные из таблиц, представлений и материализованных представлений. Следует отметить, что таблица может соединяться как с другими таблицами, так и сама с собой.
Соединение двух таблиц без использования сужающей выбор конструкции WHERE называется декартовым произведением (Cartesian product) или декартовым соединением (Cartesian join). При таком соединении, следовательно, вывод запроса будет содержать все строки из обеих таблиц. Ниже приведен пример декартового соединения:
Декартово произведение двух больших таблиц практически всегда является результатом выполнения ошибочного SQL-запроса, в котором было пропущено условие соединения (join condition). За счет использования условия соединения при объединении данных из двух или более таблиц можно ограничивать количество возвращаемых строк. Это условие можно применять в конструкции WHERE или FROM и тем самым указывать, что извлекаться должны только те данные, которые удовлетворяют описанному в условии соединения условию.
Ниже приведен пример использования условия соединения в операторе соединения:
Oracle – это реляционная база данных. Данные в базе хранятся в виде двумерных таблиц: есть строки и столбцы. Однако в жизни довольно часто приходиться сталкиваться с иерархической структурой данных. Простой пример: структура папок на вашем компьютере.
Безусловно, во многих случаях иерархию можно обойти путем создания отдельных таблиц для каждого уровня вхождения. Но если бездна иерархии заранее не известна?
На помощь ораклистам приходит вот такая конструкция.
Попробуем дерево папок на нашем компьютере внести в нашу базу данных, а затем его нарисовать средствами оракла.
Создаем тестовую табличку. Запись состоит из 3 полей: идентификатор узла, родительский узел, название узла.
Вносим в табличку записи.
Теперь подготовим запросы на выборку.
Разберемся с обязательной конструкцией CONNECT BY. Эта конструкция задает условие рабочие мероприятия цикла. Чтобы возвести иерархию в запросе нужно каким-то образом связывать запись предыдущую и последующую. Для этого Оракл придумал оператор PRIOR, который позволяет работая с текущей записью обратиться к предыдущей. Конструкция connect by prior >
Конструкция START WITH задает корневой узел. Для нашего случая start with >
Оракл также предлагает использовать псевдостолбец level, показывающий уровень записи по отношению к корневому узлу.
ORDER SIBLINGS BY – эта конструкция позволяет разбирать записи в пределах одного уровня иерархии.
Используя вышеупомянутые конструкции и операторы можно возвести иерархию, выполнив следующий запрос:
select a.*,level from my_table a start with >
ID PARENT_ID NAME LEVEL 1 0 1 1 2 1 10 2 3 2 11 3 4 3 111 4 5 4 1111 5 6 5 11111 6 7 3 112 4 8 7 1122 5 9 2 12 3 10 9 1122 4 11 10 112233 5 12 2 13 3 13 2 14 3 14 1 20 2 15 14 21 3 16 15 2211 4 17 14 22 3 18 17 2211 4 19 18 221133 5 20 14 23 3 21 1 30 2 22 21 31 3
Вот так мы и получили иерархию.
А теперь попробуем красиво изобразить полученную иерархию.
И контрольный выстрел. Есть в ORACLE такая функция SYS_CONNECT_BY_PATH. О ней написано здесь. Используем ее себе во благо:
select SYS_CONNECT_BY_PATH(name, ‘/’) AS Path from my_table a start with >
/1 /1/10 /1/10/11 /1/10/11/111 /1/10/11/111/1111 /1/10/11/111/1111/11111 /1/10/11/112 /1/10/11/112/1122 /1/10/12 /1/10/12/1122 /1/10/12/1122/112233 /1/10/13 /1/10/14 /1/20 /1/20/21 /1/20/21/2211 /1/20/22 /1/20/22/2211 /1/20/22/2211/221133 /1/20/23 /1/30 /1/30/31