Hierarchický dotaz je typ dotazu SQL, který zpracovává data hierarchického modelu. Jsou to zvláštní případy obecnějších rekurzivních dotazů s pevnou řádovou čárkou, které vyhodnocují tranzitivní uzávěry.
Ve standardním SQL:1999 jsou hierarchické dotazy implementovány pomocí rekurzivních společných tabulkových výrazů (CTE). Na rozdíl od dřívějšího návrhu připojení společnosti Oracle byly rekurzivní CTE od začátku navrženy se sémantikou pevných bodů. Rekurzivní CTE ze standardu byly relativně blízké existující implementaci v IBM DB2 verze 2 [1] . Rekurzivní CTE jsou také podporovány Microsoft SQL Server (od SQL Server 2008 R2) [2] , Firebird 2.1 [3] , PostgreSQL 8.4+ [4] , SQLite 3.8.3+ [5] , IBM Informix verze 11.50+, CUBRID a MySQL 8.0.1+ [6] . Tableau a TIBCO Spotfire nepodporují CTE, zatímco implementace Oracle 11g Release 2 postrádá sémantiku bodu odevzdání.
Bez běžných tabulkových výrazů nebo spojovacích klauzulí můžete spouštět hierarchické dotazy pomocí vlastních rekurzivních funkcí. [7]
Běžný tabulkový výraz neboli CTE (v SQL ) je dočasná pojmenovaná sada výsledků odvozená z jednoduchého dotazu a definovaná v rámci rozsahu provádění příkazu SELECT, INSERTnebo . UPDATEDELETE
CTE lze považovat za alternativu k odvozeným tabulkám (poddotazům), pohledům a vestavěným uživatelsky definovaným funkcím.
Běžné tabulkové výrazy podporují Teradata , DB2 , Firebird [8] , Microsoft SQL Server , Oracle (s rekurzí od verze 11g 11g), PostgreSQL (od 8.4), MariaDB (od 10.2), MySQL (od 8.0), SQLite (od r. 3.8.3), HyperSQL a H2 (experimentální) [9] . Oracle nazývá CTE „faktoring poddotazů“. [deset]
Syntaxe pro rekurzivní CTE je:
WITH [ RECURSIVE ] with_query [, ...] SELECT ...kde je syntaxe with_query:
název_dotazu [ ( název_sloupce [,...]) ] AS ( SELECT ...)Rekurzivní CTE (nebo "rekurzivní faktoring poddotazů" [11] v žargonu Oracle) lze použít k procházení vztahů (ve formě grafů nebo stromů), i když syntaxe je mnohem složitější, protože nejsou vytvářeny žádné automatické pseudosloupky (jako LEVEL níže); pokud jsou požadovány, musí být vytvořeny v kódu. Případové studie naleznete v dokumentaci MSDN [2] nebo dokumentaci IBM [12] .
Klíčové slovo RECURSIVEobvykle není vyžadováno po WITH na jiných systémech než PostgreSQL. [13]
V SQL:1999 se rekurzivní (CTE) dotaz může objevit kdekoli, kde je dotaz povolen. Výsledek můžete pojmenovat například pomocí CREATE[ RECURSIVE] VIEW[1] . Pomocí CTE inside INSERT INTOje možné naplnit tabulku daty generovanými z rekurzivního dotazu; náhodné generování dat je možné pomocí této techniky bez použití procedurálních příkazů. [čtrnáct]
Některé databáze, jako je PostgreSQL, podporují kratší formát CREATE RECURSIVE VIEW, který je interně převeden na kódování WITH RECURSIVE. [patnáct]
Příklad rekurzivního dotazu, který vypočítá faktoriál čísel od 0 do 9, je následující:
WITH RECURSIVE temp (n, fact) AS ( SELECT 0, 1 -- Počáteční poddotaz UNION ALL SELECT n+1, (n+1)*fact FROM temp -- Rekurzivní poddotaz WHERE n < 9) SELECT * FROM temp;Alternativní syntaxí je vlastní konstrukce CONNECT BY; to bylo představeno Oracle v 80-tých letech. Před Oracle 10g byla tato konstrukce užitečná pouze pro procházení acyklických grafů, protože by vracela chybu, pokud by byly nalezeny nějaké cykly; Ve verzi 10g zavedl Oracle funkci NOCYCLE (a klíčové slovo), takže procházení funguje, i když existují cykly. [16]
CONNECT BYpodporované EnterpriseDB, Oracle Database, [17] CUBRID, [18] IBM Informix a DB2, i když pouze pokud je povoleno jako režim kompatibility. Syntaxe vypadá takto:
SELECT select_list FROM table_expression [ KDE ... ] [ START WITH start_expression ] CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PŘEDCHOZÍ child_expr } [ ŘADIT SOUrozence PODLE sloupce1 [ ASC | DESC ][, sloupec2[ ASC | DESC ]] ... [ SESKUPIT PODLE ... ] [ MÍT ...] ... Například, SELECT LEVEL , LPAD (' ', 2 * ( LEVEL - 1)) || ename "zaměstnanec", empno, mgr "manažer" OD ZAČÁTEK S mgr JE NULL PŘIPOJIT PŘEDCHOZÍM empno = mgr ;Výstup výše uvedeného dotazu bude vypadat takto:
úroveň | zaměstnanec | empno | manažer -------+-------------+-------+---------- 1 | KRÁL | 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 řádků)Následující příklad vrátí příjmení každého zaměstnance v oddělení 10, každého manažera nad tímto zaměstnancem v hierarchii, počet úrovní mezi manažerem a zaměstnancem a cestu mezi nimi:
SELECT ename "Zaměstnanec", CONNECT_BY_ROOT ename "Manažer", LEVEL -1 " Pathlen ", SYS_CONNECT_BY_PATH(jméno, '/') "Cesta" FROM emp WHERE LEVEL > 1 a deptno = 10 PŘIPOJIT PODLE PŘEDCHOZÍHO ZAMĚSTNANCE mg ", "Manažer", "Pathlen", "Cesta";