Hierarkkinen kysely on eräänlainen SQL-kysely, joka käsittelee hierarkkisia mallitietoja. Ne ovat erikoistapauksia yleisemmille kiinteän pisteen rekursiivisille kyselyille, jotka arvioivat transitiivisia sulkemisia.
Vakio - SQL:1999: ssä hierarkkiset kyselyt toteutetaan käyttämällä rekursiivisia yleisiä taulukkolausekkeita (CTE). Toisin kuin Oraclen aikaisempi yhteysehdotus, rekursiiviset CTE:t suunniteltiin alusta alkaen kiinteän pisteen semantiikalla. Standardin rekursiiviset CTE:t olivat suhteellisen lähellä olemassa olevaa IBM DB2 -version 2 toteutusta [1] . Rekursiivisia CTE:itä tukevat myös Microsoft SQL Server (SQL Server 2008 R2:sta lähtien) [2] , Firebird 2.1 [3] , PostgreSQL 8.4+ [4] , SQLite 3.8.3+ [5] , IBM Informix versio 11.50+, CUBRID ja MySQL 8.0.1+ [6] . Tableau ja TIBCO Spotfire eivät tue CTE:itä, kun taas Oracle 11g Release 2 -toteutuksesta puuttuu sitoutumispisteen semantiikka.
Ilman yleisiä taulukkolausekkeita tai liitoslauseita voit suorittaa hierarkkisia kyselyitä mukautettujen rekursiivisten funktioiden avulla. [7]
Yleinen taulukkolauseke tai CTE ( SQL :ssä ) on väliaikainen nimetty tulosjoukko, joka on johdettu yksinkertaisesta kyselystä ja määritetty SELECT, INSERT, UPDATEtai käskyn suoritusalueella DELETE.
CTE:t voidaan ajatella vaihtoehtona johdetuille taulukoille (alikyselyille), näkymille ja sisäänrakennetuille käyttäjän määrittämille funktioille.
Yleisiä taulukkolausekkeita tukevat Teradata , DB2 , Firebird [8] , Microsoft SQL Server , Oracle (rekursiolla versiosta 11g 11g alkaen), PostgreSQL (8.4), MariaDB (10.2), MySQL (8.0), SQLite (alkaen ). 3.8.3), HyperSQL ja H2 (kokeellinen) [9] . Oracle kutsuu CTE:tä "alikyselyn factoringiksi". [kymmenen]
Rekursiivisen CTE:n syntaksi on:
WITH [ REKURSIIVINEN ] with_query [, ...] VALITSE ...missä syntaksi with_queryon:
kyselyn_nimi [ ( sarakkeen_nimi [,...]) ] AS ( VALITSE ...)Rekursiivisia CTE:itä (tai "rekursiivista alikyselyn faktorointia" [11] Oraclen ammattikielellä) voidaan käyttää suhteiden läpi kulkemiseen (kaavioiden tai puiden muodossa), vaikka syntaksi on paljon monimutkaisempi, koska automaattisia pseudosarakkeita ei luoda (kuten LEVEL alla); jos niitä halutaan, ne on luotava koodilla. Katso tapaustutkimuksia MSDN-dokumentaatiosta [2] tai IBM:n dokumentaatiosta [12] .
Avainsanaa ei RECURSIVEyleensä vaadita WITH:n jälkeen muissa järjestelmissä kuin PostgreSQL. [13]
SQL:1999:ssä rekursiivinen (CTE) kysely voi esiintyä kaikkialla, missä kysely on sallittu. Voit esimerkiksi nimetä tuloksen käyttämällä CREATE[ RECURSIVE] VIEW[1] . Käyttämällä CTE:tä sisällä INSERT INTOon mahdollista täyttää taulukko rekursiivisesta kyselystä luoduilla tiedoilla; satunnainen datan luominen on mahdollista tällä tekniikalla ilman menettelykäskyjä. [neljätoista]
Jotkut tietokannat, kuten PostgreSQL, tukevat lyhyempää CREATE RECURSIVE VIEW -muotoa, joka muunnetaan sisäisesti WITH RECURSIVE-koodaukseksi. [viisitoista]
Esimerkki rekursiivisesta kyselystä, joka laskee lukujen 0-9 tekijän, on seuraava:
REKURSIIVISELLA lämpötilalla (n, tosiasia) AS ( SELECT 0, 1 -- Alkuperäinen alikysely UNION ALL SELECT n +1, (n+1)*fact FROM temp -- Rekursiivinen alikysely WHERE n < 9) SELECT * FROM temp;Vaihtoehtoinen syntaksi on mukautettu rakenne CONNECT BY; Oracle esitteli sen 1980-luvulla. Ennen Oracle 10g:tä tämä rakennelma oli hyödyllinen vain asyklisten kuvaajien läpikäymiseen, koska se palauttaisi virheen, jos syklejä löydettäisiin; Versiossa 10g Oracle esitteli NOCYCLE-toiminnon (ja avainsanan), jotta läpikulku toimii myös syklien aikana. [16]
CONNECT BYEnterpriseDB, Oracle Database, [17] CUBRID, [18] IBM Informix ja DB2 tukevat, vaikkakin vain, jos ne on otettu käyttöön yhteensopivuustilana. Syntaksi näyttää tältä:
SELECT select_list FROM table_expression [ MISSÄ ... ] [ ALOITA aloituslausekkeella ] YHTEYS [ NOCYCLE ] { EDELLINEN lapsi_lause = vanhempi_lause | vanhempi_lause = EDELLINEN lapsi_lause } [ ORDER SIBLINGS BY sarake1 [ ASC | DESC ][, sarake2[ ASC | DESC ]] ... [ Ryhmittele ... ] [ ON ...] ... Esimerkiksi, SELECT LEVEL , LPAD (' ', 2 * ( LEVEL - 1)) || ename "työntekijä", empno, mgr "päällikkö" FROM emp ALKAA mgr IS NULL YHDISTÄ EDELLÄ empno = mgr ;Yllä olevan kyselyn tulos näyttää tältä:
taso | työntekijä | empno | johtaja -------+-------------+-------+---------- 1 | KUNINGAS | 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 riviä)Seuraava esimerkki palauttaa kunkin osastolla 10 olevan työntekijän sukunimen, kunkin hierarkiassa kyseisen työntekijän yläpuolella olevan johtajan, esimiehen ja työntekijän välisten tasojen lukumäärän sekä polun heidän välillään:
SELECT ename "Työntekijä", CONNECT_BY_ROOT ename "Manager", LEVEL -1 "Pathlen", SYS_CONNECT_BY_PATH(nimi, '/') "Polku" FROM emp WHERE TASO > 1 ja deptno = 10 CONNECT BY PRIOR empno =Y mgr ", "Manager", "Pathlen", "Path";