Цю статтю написано занадто професійним зі специфічною термінологією, що може бути незрозумілим для більшості читачів. (квітень 2018) |
Ієрархічний запит — тип (запиту 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+, [en], (MariaDB 10.2+) і (MySQL 8.0.1+). Tableau має документацію, що описує, як ЗТВ можуть використовуватися. TIBCO Spotfire не підтримує ЗТВ, тоді як реалізації Oracle 11g Release 2 бракує нерухомої семантики.
Без загальних табличних виразів або умов connected-by можливо досягти ієрархічних запитів за допомогою користувацьких рекурсивних функцій.
Загальний табличний вираз
Загальний табличний вираз, або ЗТВ (в SQL) — тимчасовий іменований результатний набір, що походить із простого запиту та визначений усередині області виконання інструкції SELECT, INSERT, UPDATE чи DELETE.
ЗТВ можна вважати альтернативами похідним таблицям ((підзапитам)), (розрізам) і вбудованим користувацьким функціям.
Загальні табличні вирази підтримуються (Teradata), (DB2), (Firebird), (Microsoft SQL Server), (Oracle) (з рекурсією, починаючи з 11g release 2), (PostgreSQL) (починаючи з 8.4), (MariaDB) (починаючи з 10.2), (MySQL) (починаючи з 8.0), (SQLite) (починаючи з 3.8.3), (HyperSQL) і [en] (експериментально). Oracle називає ЗТВ «підзапитним факторингом» (англ. subquery factoring).
Синтаксис для рекурсивного ЗТВ виглядає наступним чином:
WITH [RECURSIVE] запит_with [, …] SELECT …
де синтаксисом запит_with є:
назва_запиту [ (назва_колонки [, …]) ] AS (SELECT …)
Рекурсивні ЗТВ (або «рекурсивний підзапитний факторинг» у жаргоні Oracle) можуть використовуватися для обходу відношень (як графів або дерев), хоча синтаксис набагато більше залучений через відсутність створених автоматичних псевдо-колонок (як LEVEL нижче); якщо вони є бажаними, то їх слід створити в коді. Навчальні приклади див. у документації MSDN або IBM.
Ключове слово RECURSIVE зазвичай не є необхідним після WITH у системах, крім PostgreSQL.
В SQL:1999 рекурсивний (ЗТВ) запит може з'являтися будь-де, де дозволено запит. Можливо, наприклад, назвати результат за допомогою CREATE [RECURSIVE] VIEW
. За допомогою ЗТВ усередині INSERT INTO можна наповнити таблицю даними, згенерованими з рекурсивного запиту; генерація випадкових даних можлива з використанням цієї техніки без використання жодних процедурних інструкцій.
Деякі бази даних на кшталт PostgreSQL підтримують скорочений формат CREATE RECURSIVE VIEW, який внутрішньо перекладається в кодування WITH RECURSIVE.
Прикладом рекурсивного запиту, що обчислює (факторіал) чисел від 0 до 9, є наступне:
WITH RECURSIVE temp (n, fact) AS (SELECT 0, 1 -- Початковий підзапит UNION ALL SELECT n + 1, (n + 1) * fact -- Рекурсивний підзапит FROM temp WHERE n < 9) SELECT * FROM temp;
CONNECT BY
Альтернативним синтаксисом є нестандартна конструкція CONNECT BY; її було впроваджено Oracle у 1980-х. До Oracle 10g конструкція була корисною тільки для обходу ациклічних графів, оскільки вона повертала помилку при виявленні будь-яких циклів; у версії 10g Oracle впровадила можливість NOCYCLE (та ключове слово), уможливлюючи роботу з обходу і за наявності циклів.
CONNECT BY підтримується (EnterpriseDB), (Oracle Database), [en], (IBM Informix) і (DB2), хоча тільки, якщо його увімкнено як режим сумісності. Синтаксис виглядає наступним чином:
SELECT список_вибірки FROM табличний_вираз [ WHERE … ] [ START WITH початковий_вираз ] CONNECT BY [NOCYCLE] { PRIOR дочірній_вираз = батьківський_вираз | батьківський_вираз = PRIOR дочірній_вираз } [ ORDER SIBLINGS BY колонка1 [ ASC | DESC ] [, колонка2 [ ASC | DESC ] ] … [ GROUP BY … ] [ HAVING … ] …
- Наприклад,
Виведення з вищенаведеного запиту виглядатиме як:
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 рядків)
Псевдо-колонки
- LEVEL
- CONNECT_BY_ISLEAF
- CONNECT_BY_ISCYCLE
- CONNECT_BY_ROOT
Унарні оператори
Наступний приклад повертає прізвище кожного працівника у відділі 10, кожного менеджера над цим працівником в ієрархії, кількість рівнів між менеджером і працівником і шлях між ними:
SELECT ename "Працівник", CONNECT_BY_ROOT ename "Менеджер", LEVEL - 1 "Довжина шляху", SYS_CONNECT_BY_PATH(ename, '/') "Шлях" FROM emp WHERE LEVEL > 1 and deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Працівник", "Менеджер", "Довжина шляху", "Шлях";
Функції
- SYS_CONNECT_BY_PATH
Див. також
- (Datalog) також реалізує нерухомі запити
- (Дедуктивні бази даних)
- (Деревоподібна структура)
- [en]
- (Ієрархічна модель даних)
- Транзитивне замикання
Примітки
- Melton, Jim; Simon, Alan R. (2002). . Morgan Kaufmann. с. 352. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
- Microsoft. . (MSDN). Архів оригіналу за 8 грудня 2009. Процитовано 23 грудня 2009.
- Borrie, Helen (15 липня 2008). . Архів оригіналу за 22 квітня 2017. Процитовано 24 листопада 2015.
- . (PostgreSQL). Архів оригіналу за 1 травня 2016. Процитовано 9 серпня 2019.
- . (SQLite). Архів оригіналу за 5 липня 2019. Процитовано 9 серпня 2019.
- . mysqlserverteam.com. Архів оригіналу за 16 серпня 2019. Процитовано 9 серпня 2019.
- . kb.tableau.com (англійською) . 8 січня 2019 [2016]. Архів оригіналу за 1 серпня 2019. Процитовано 9 серпня 2019.
- . 15 лютого 2004. Архів оригіналу за 23 вересня 2015. Процитовано 19 вересня 2015.
- [en]
- . h2database.com (англійською) . Архів оригіналу за 9 липня 2006. Процитовано 9 серпня 2019.
- Morton, Karen; Sands, Robyn; Still, Jared; Shamsudeen, Riyaj; Osborne, Kerry (2010). . Apress. с. 283. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
- Morton, Karen; Sands, Robyn; Still, Jared; Shamsudeen, Riyaj; Osborne, Kerry (2010). . Apress. с. 304. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
- http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htm
- http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm
- Obe, Regina; Hsu, Leo (2012). . (O'Reilly Media). с. 94. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
- Chamberlin, Don (1998). . Morgan Kaufmann. с. 253—254. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
- . Архів оригіналу за 4 жовтня 2018. Процитовано 9 серпня 2019.
{{}}
: Обслуговування CS1: Сторінки з текстом «archived copy» як значення параметру title () - Benedikt, M.; Senellart, P. (2011). Databases. У Blum, Edward K.; Aho, Alfred V. (ред.). Computer Science. The Hardware, Software and Heart of It. с. 189. doi:10.1007/978-1-4614-1168-0_10. ISBN .
- Beaulieu, Alan (2004). . O'Reilly Media, Inc. с. 227. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
{{}}
:|ім'я1=
з пропущеним|ім'я1=
() - . EnterpriseDB. Архів оригіналу за 21 червня 2008. Процитовано 9 серпня 2019.
- . Oracle. Архів оригіналу за 8 листопада 2011. Процитовано 9 серпня 2019.
- . Архів оригіналу за 14 лютого 2013. Процитовано 11 лютого 2013.
- . IBM Informix. Архів оригіналу за 4 березня 2016. Процитовано 9 серпня 2019.
- Gennick, Jonathan (2010). (вид. 3-є). O'Reilly Media, Inc. с. 8. ISBN . Архів оригіналу за 29 липня 2020. Процитовано 9 серпня 2019.
Література
- (Date, C. J.) (2011). SQL and Relational Theory: How to Write Accurate SQL Code (англійською) (вид. 2-е). (O'Reilly Media). с. 159—163. ISBN .
Академічні підручники. Зверніть увагу, що вони покривають тільки SQL:1999 (та Datalog), але не розширення Oracle.
- Silberschatz, Abraham; Korth, Henry; Sudarshan, S. (2010). (англійською) (вид. 6-е). McGraw-Hill. с. 187—192. ISBN .
- Ramakrishnan, Raghu; Gehrke, Johannes (2003). Chapter 24. Database management systems (англійською) (вид. 3-є). McGraw-Hill. ISBN .
- ; Ullman, Jeffrey D.; Widom, Jennifer (2009). Database systems: the complete book (англійською) (вид. 2-е). Pearson Prentice Hall. с. 437—445. ISBN .
Посилання
- https://stackoverflow.com/questions/1731889/cycle-detection-with-recursive-subquery-factoring [ 14 квітня 2020 у Wayback Machine.]
- http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/ [ 1 серпня 2020 у Wayback Machine.]
- http://www.cs.duke.edu/courses/fall04/cps116/lectures/11-recursion.pdf [ 1 серпня 2020 у Wayback Machine.]
- http://www.blacktdn.com.br/2015/06/blacktdn-mssql-usando-consulta-cte.html [ 28 липня 2020 у Wayback Machine.]
Вікіпедія, Українська, Україна, книга, книги, бібліотека, стаття, читати, завантажити, безкоштовно, безкоштовно завантажити, mp3, відео, mp4, 3gp, jpg, jpeg, gif, png, малюнок, музика, пісня, фільм, книга, гра, ігри, мобільний, телефон, android, ios, apple, мобільний телефон, samsung, iphone, xiomi, xiaomi, redmi, honor, oppo, nokia, sonya, mi, ПК, web, Інтернет