Ієрархічний запит — тип запиту SQL, що обробляє ієрархічну модель даних. Вони є особливим випадком загальніших рекурсивних нерухомих запитів, які обчислюють транзитивні замикання.
У стандарті SQL:1999 ієрархічні запити реалізовані шляхом рекурсивних загальних табличних виразів (ЗТВ). На відміну від більш ранніх умов connect-by в Oracle, рекурсивні ЗТВ було розроблено з нерухомою семантикою від початку. Рекурсивні ЗТВ зі стандарту були відносно близькі до наявної реалізації в IBM DB2 версії 2. Рекурсивні ЗТВ також підтримуються Microsoft SQL Server (починаючи з SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, IBM Informix версії 11.50+, , MariaDB 10.2+ і MySQL 8.0.1+ . Tableau має документацію, що описує, як ЗТВ можуть використовуватися. TIBCO Spotfire не підтримує ЗТВ, тоді як реалізації Oracle 11g Release 2 бракує нерухомої семантики.
Без загальних табличних виразів або умов connected-by можливо досягти ієрархічних запитів за допомогою користувацьких рекурсивних функцій.
Загальний табличний вираз Загальний табличний вираз, або ЗТВ (в SQL) — тимчасовий іменований результатний набір, що походить із простого запиту та визначений усередині області виконання інструкції , , чи .
ЗТВ можна вважати альтернативами похідним таблицям (підзапитам), розрізам і вбудованим користувацьким функціям.
Загальні табличні вирази підтримуються Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (з рекурсією, починаючи з 11g release 2), PostgreSQL (починаючи з 8.4), MariaDB (починаючи з 10.2), MySQL (починаючи з 8.0), SQLite (починаючи з 3.8.3), HyperSQL і (експериментально). Oracle називає ЗТВ «підзапитним факторингом» ().
Синтаксис для рекурсивного ЗТВ виглядає наступним чином:
де синтаксисом є:
Рекурсивні ЗТВ (або «рекурсивний підзапитний факторинг» у жаргоні Oracle) можуть використовуватися для обходу відношень (як графів або дерев), хоча синтаксис набагато більше залучений через відсутність створених автоматичних псевдо-колонок (як нижче); якщо вони є бажаними, то їх слід створити в коді. Навчальні приклади див. у документації MSDN або IBMhttp://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htmhttp://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm.
Ключове слово зазвичай не є необхідним після у системах, крім PostgreSQL.
В SQL:1999 рекурсивний (ЗТВ) запит може з'являтися будь-де, де дозволено запит. Можливо, наприклад, назвати результат за допомогою . За допомогою ЗТВ усередині можна наповнити таблицю даними, згенерованими з рекурсивного запиту; генерація випадкових даних можлива з використанням цієї техніки без використання жодних процедурних інструкцій.
Деякі бази даних на кшталт PostgreSQL підтримують скорочений формат , який внутрішньо перекладається в кодування .
Прикладом рекурсивного запиту, що обчислює факторіал чисел від 0 до 9, є наступне:
CONNECT BY Альтернативним синтаксисом є нестандартна конструкція ; її було впроваджено Oracle у 1980-х. До Oracle 10g конструкція була корисною тільки для обходу ациклічних графів, оскільки вона повертала помилку при виявленні будь-яких циклів; у версії 10g Oracle впровадила можливість (та ключове слово), уможливлюючи роботу з обходу і за наявності циклів.
підтримується EnterpriseDB , Oracle Database, , IBM Informix і DB2, хоча тільки, якщо його увімкнено як режим сумісності. Синтаксис виглядає наступним чином:
Наприклад,
Виведення з вищенаведеного запиту виглядатиме як: level | працівник | empno | менеджер -------+-------------+-------+---------- 1 | KING | 7839 | 2 | JONES | 7566 | 7839 3 | SCOTT | 7788 | 7566 4 | ADAMS | 7876 | 7788 3 | FORD | 7902 | 7566 4 | SMITH | 7369 | 7902 2 | BLAKE | 7698 | 7839 3 | ALLEN | 7499 | 7698 3 | WARD | 7521 | 7698 3 | MARTIN | 7654 | 7698 3 | TURNER | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782 (14 рядків)
Псевдо-колонки
Унарні оператори Наступний приклад повертає прізвище кожного працівника у відділі 10, кожного менеджера над цим працівником в ієрархії, кількість рівнів між менеджером і працівником і шлях між ними:
Функції
Див. також Datalog також реалізує нерухомі запити Дедуктивні бази даних Деревоподібна структура
Ієрархічна модель даних Транзитивне замикання
Примітки
Література
Академічні підручники. Зверніть увагу, що вони покривають тільки SQL:1999 (та Datalog), але не розширення Oracle.
Посилання https://stackoverflow.com/questions/1731889/cycle-detection-with-recursive-subquery-factoring http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/ https://web.archive.org/web/20131114094211/http://gennick.com/with.html http://www.cs.duke.edu/courses/fall04/cps116/lectures/11-recursion.pdf http://www.blacktdn.com.br/2015/06/blacktdn-mssql-usando-consulta-cte.html