O interogare ierarhică este un tip de interogare SQL care procesează datele modelului ierarhic. Sunt cazuri speciale de interogări recursive mai generale în punct fix care evaluează închiderile tranzitive.
În standardul SQL:1999, interogările ierarhice sunt implementate folosind expresii de tabel comune recursive (CTE). Spre deosebire de propunerea anterioară de conectare a Oracle, CTE-urile recursive au fost proiectate cu semantică în punct fix încă de la început. CTE-urile recursive din standard au fost relativ apropiate de implementarea existentă în IBM DB2 versiunea 2 [1] . CTE-urile recursive sunt acceptate și de Microsoft SQL Server (din SQL Server 2008 R2) [2] , Firebird 2.1 [3] , PostgreSQL 8.4+ [4] , SQLite 3.8.3+ [5] , IBM Informix versiunea 11.50+, CUBRID și MySQL 8.0.1+[6] . Tableau și TIBCO Spotfire nu acceptă CTE-uri, în timp ce implementarea Oracle 11g Release 2 nu are semantica punctului de comitere.
Fără expresii comune de tabel sau clauze de unire, puteți rula interogări ierarhice folosind funcții recursive personalizate. [7]
O expresie de tabel comună, sau CTE, (în SQL ) este un set de rezultate denumit temporar derivat dintr-o interogare simplă și definit în domeniul de execuție al unei instrucțiuni SELECT, INSERT, UPDATEsau DELETE.
CTE-urile pot fi gândite ca o alternativă la tabelele derivate (subinterogări), vizualizările și funcțiile încorporate definite de utilizator.
Expresiile comune de tabel sunt acceptate de Teradata , DB2 , Firebird [8] , Microsoft SQL Server , Oracle (cu recursivitate de la versiunea 11g 11g), PostgreSQL (de la 8.4), MariaDB (de la 10.2), MySQL (de la 8.0), SQLite (de la 8.0), 3.8.3), HyperSQL și H2 (experimental) [9] . Oracle numește CTE „subquery factoring”. [zece]
Sintaxa pentru un CTE recursiv este:
WITH [ RECURSIVE ] with_query [, ...] SELECT ...unde sintaxa with_queryeste:
nume_interogare [ ( nume_coloană [,...]) ] AS ( SELECTARE ...)CTE recursive (sau „recursive subquery factoring” [11] în jargonul Oracle) pot fi folosite pentru a parcurge relații (sub formă de grafice sau arbori), deși sintaxa este mult mai complexă, deoarece nu sunt create pseudo-coloane automate (cum ar fi LEVEL). de mai jos); daca sunt dorite, acestea trebuie create in cod. Consultați documentația MSDN [2] sau documentația IBM [12] pentru studii de caz.
Cuvântul cheie RECURSIVEde obicei nu este necesar după WITH pe alte sisteme decât PostgreSQL. [13]
În SQL:1999, o interogare recursivă (CTE) poate apărea oriunde este permisă o interogare. De exemplu, puteți denumi rezultatul folosind CREATE[ RECURSIVE] VIEW[1] . Folosind CTE în interiorul INSERT INTO, este posibil să populați un tabel cu date generate dintr-o interogare recursivă; generarea aleatorie de date este posibilă folosind această tehnică fără utilizarea declarațiilor procedurale. [paisprezece]
Unele baze de date, cum ar fi PostgreSQL, acceptă formatul mai scurt CREATE RECURSIVE VIEW, care este convertit intern la codificare WITH RECURSIVE. [cincisprezece]
Un exemplu de interogare recursivă care calculează factorialul numerelor de la 0 la 9 este următorul:
WITH RECURSIVE temp (n, fact) AS ( SELECT 0, 1 -- Initial Subquery UNION ALL SELECT n+1, (n+1)*fact FROM temp -- Recursive Subquery WHERE n < 9) SELECT * FROM temp;Sintaxa alternativă este o construcție personalizată CONNECT BY; a fost introdus de Oracle în anii 1980. Înainte de Oracle 10g, această construcție era utilă doar pentru parcurgerea graficelor aciclice, deoarece ar returna o eroare dacă s-ar găsi vreun ciclu; În versiunea 10g, Oracle a introdus funcția NOCYCLE (și cuvântul cheie), astfel încât traversarea să funcționeze chiar și atunci când există cicluri. [16]
CONNECT BYsuportat de EnterpriseDB, Oracle Database, [17] CUBRID, [18] IBM Informix și DB2, deși numai dacă este activat ca mod de compatibilitate. Sintaxa arată astfel:
SELECTează select_list FROM table_expression [ UNDE ... ] [ ÎNCEPE CU start_expression ] CONECTAȚI DE [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr } [ ORDENAȚI FRATII DUPĂ coloana1 [ ASC | DESC ][, coloana2[ ASC | DESC ]] ... [ GRUPĂ PENTRU ... ] [ AVÂND ...] ... De exemplu, SELECTARE LEVEL , LPAD (' ', 2 * ( LEVEL - 1)) || ename „angajat”, empno, mgr „manager” FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;Rezultatul interogării de mai sus va arăta astfel:
nivel | angajat | empno | administrator -------+-------------+-------+--------- 1 | REGELE | 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 rânduri)Următorul exemplu returnează numele de familie al fiecărui angajat din departamentul 10, fiecare manager deasupra acelui angajat din ierarhie, numărul de niveluri dintre manager și angajat și calea dintre ele:
SELECT ename "Employee", CONNECT_BY_ROOT ename "Manager", LEVEL -1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Path" FROM emp WHERE LEVEL > 1 and deptno = 10 CONNECT BY PRIOR empno = " mgr ORDER BY „, „Manager”, „Pathlen”, „Cale”;