Тема: Иерархические запросы Вопросы: 1. 2. 3. 4.

Тема: Иерархические запросы Вопросы: 1. 2. 3. 4. 5. 6. Введение Примеры SQL 1999 Примеры на Oracle Запрос с сортировкой по уровням Функция SYS_CONNECT_BY_PATH(). Оператор CONNECT_BY_ROOT 1

Литература 1. Стаья «SQL. Иерархические (рекурсивные) запросы» . http: //club. shelek. ru/viewart. php? id=306. 2. Статья «Построение дерева иерархии с помощью PHP/My. SQL» : http: //www. codenet. ru/webmast/php/tree. php. 3. Статья «Traversing Trees in SQLBase» и примеры кода к ней: http: //dev. e-taller. net/dbtree/. 4. Статья «MS SQL: hierarchyid — иерархия поновому» : http: //habrahabr. ru/blogs/sql/27774/. 5. Пользователь «Xaoc. CPS» на сайте habrahabr. ru: http: //xaoccps. habrahabr. ru/. 2

Введение Большинство современных СУБД являются реляционными, т. е. представляют данные в виде двумерной таблицы. На практике мы часто сталкиваемся с иерархической организацией данных. Например, книги в библиотеке можно представить в виде дерева: Библиотека→Зал→Шкаф→Полка→Книга. 3

Введение Для демонстрации будем использовать структуру каталогов, нам потребуется тестовая таблица с 3 -мя полями: • id — идентификатор, • pid — идентификатор родителя (ссылается на id другой записи в той же таблице), • title — название каталога (вместо него может быть что угодно, даже несколько полей или ссылок к другим таблицам). 4

Введение. Код: (SQL) CREATE TABLE test_table ( id INT, pid INT, title VARCHAR(256) ); 5

Введение. Тестовые данные Код: ID PID TITLE ---------- 1 Россия 2 1 Воронеж 3 2 ООО "Рога и копыта" 4 1 Москва 5 1 Лиски 6 3 Главный офис 7 3 Офис 1 8 3 Офис 2 9 8 Сервер 1 10 5 Лиски. Пресс 6

Код запроса на SQL 1999 Для получения иерархических данных используется временное представление, описываемое оператором WITH. После этого из нее выбираются данные простым SELECT. В общем виде синтаксис примерно такой: WITH [RECURSIVE] имя_алиаса_запроса[ (список столбцов) ] AS (запрос) основной запрос 7

Код запроса на SQL 1999 Код запроса для получения дерева: WITH RECURSIVE Rec (id, pid, title) AS ( SELECT id, pid, title FROM test_table UNION ALL SELECT Rec. id, Rec. pid, Rec. title FROM Rec, test_table WHERE Rec. id = test_table. pid ) SELECT * FROM Rec WHERE pid IS NULL; 8

Иерархические запросы в Oracle Структура операторов иерархического запроса в Oracle. Единственно важное условие для построения иерархического запроса — это оператор CONNECT BY. Необязательный оператор START WITH указывает, с чего начинать цикл, т. е. какая строка (или строки) будет корневой. Условие может быть любым, можно использовать функции или внутренние запросы: pid IS NULL, или id = 1, или SUBSTR(title, 1, 1) = 'Р'. 9

Иерархические запросы в Oracle Чтобы получить нормальную иерархию нужно использовать оператор PRIOR. Это обычный унарный оператор, точно такой же как «+» или «-» . Он заставляет обратиться к предыдущей записи. С его помощью можно написать правило pid = PRIOR id (или PRIOR id = pid). Для упорядочения родителей и потомков Oracle предлагает дополнительный псевдостолбец LEVEL. В нем записывается уровень записи по отношению к корневой. Так, 1 -я запись будет иметь уровень 1, ее потомки уровень 2, потомки потомков — 3 и т. д. 10

Иерархические запросы в Oracle. Код SQL SELECT level, id, pid, title FROM test_table START WITH pid IS NULL CONNECT BY PRIOR id = pid; 11

Иерархические запросы в Oracle. Результат LEVEL ID PID TITLE ------------ 1 Россия 2 1 Воронеж 3 2 ООО "Рога и копыта" 4 6 3 Главный офис 4 7 3 Офис 1 4 8 3 Офис 2 5 9 8 Сервер 1 2 4 1 Москва 2 5 1 Лиски 3 10 5 Лиски. Пресс 12

Запрос с сортировкой. Код SQL: Для сортировки по алфавиту добавим в конец запроса конструкцию ORDER BY title. SELECT level, id, pid, title FROM test_table START WITH pid IS NULL CONNECT BY PRIOR id = pid ORDER BY title; 13

Запрос с сортировкой. Результат LEVEL ID PID TITLE ------------ 2 1 Воронеж 4 6 3 Главный офис 2 5 1 Лиски 3 10 5 Лиски. Пресс 2 4 1 Москва 3 2 ООО "Рога и копыта" 4 7 3 Офис 1 4 8 3 Офис 2 1 Россия 5 9 8 Сервер 1 14

Запрос с сортировкой по уровням. Код SQL Для сортировки в пределах одного уровня иерархии укажем оператор SIBLINGS: заменим условие сортировки на ORDER SIBLINGS BY title. Для представления строк в виде дерева уберем «лишние» поля и добавим отступы: SELECT LPAD(' ', 3 * level) || title AS Tree FROM test_table START WITH pid IS NULL CONNECT BY PRIOR id = pid ORDER SIBLINGS BY title; 15

Запрос с сортировкой по уровням. Результат: TREE -------------- Россия Воронеж ООО "Рога и копыта" Главный офис Офис 1 Офис 2 Сервер 1 Лиски. Пресс Москва 16

Функция SYS_CONNECT_BY_PATH(). Позволяет задать путь к каталогу. Код SQL-запроса: SELECT SYS_CONNECT_BY_PATH(title, '/') AS Path FROM test_table WHERE id=9 START WITH pid IS NULL CONNECT BY PRIOR id = pid; Результат: PATH --------------------------/Россия/Воронеж/ООО "Рога и копыта"/Офис 2/Сервер 1 17

Унарный оператор CONNECT_BY_ROOT Ссылается на корневую запись. Псевдостолбец CONNECT_BY_ISLEAF можно использовать как LEVEL. В этом псевдостолбце напротив каждой строки проставляется 0, если есть потомки. Если потомков нет, такой узел в дереве называется «листом» , и значение в поле равно 1. Код SQL-запроса: SELECT id, pid, title, level, CONNECT_BY_ISLEAF AS Is. Leaf, PRIOR title AS Parent, CONNECT_BY_ROOT title AS Root FROM test_table START WITH pid IS NULL CONNECT BY PRIOR id = pid 18 ORDER SIBLINGS BY title;

Унарный оператор CONNECT_BY_ROOT ID PID TITLE LEVEL LEAF PARENT ROOT -- ----------- -----1 Россия 1 0 Россия 2 1 Воронеж 2 0 Россия Россия 3 2 ООО "Рога и " 3 0 Воронеж Россия 6 3 Главный офис 4 1 ООО "Рога и копыта" Россия 7 3 Офис 1 4 1 ООО "Рога и копыта" Россия 8 3 Офис 2 4 0 ООО "Рога и копыта" Россия 9 8 Сервер 1 5 1 Офис 2 Россия 5 1 Лиски 2 0 Россия Россия 10 5 Лиски. Пресс 3 1 Лиски Россия 4 1 Москва 2 1 Россия Россия 19