En hierarkisk fråga är en typ av SQL-fråga som bearbetar hierarkisk modelldata. De är specialfall av mer allmänna rekursiva frågor med fast punkt som utvärderar transitiva stängningar.
I standard SQL:1999 implementeras hierarkiska frågor med hjälp av rekursiva vanliga tabelluttryck (CTE). Till skillnad från Oracles tidigare anslutningsförslag, designades rekursiva CTE:er med fixpunktssemantik från början. De rekursiva CTE:erna från standarden låg relativt nära den befintliga implementeringen i IBM DB2 version 2 [1] . Rekursiva CTE:er stöds också av Microsoft SQL Server (sedan SQL Server 2008 R2) [2] , Firebird 2.1 [3] , PostgreSQL 8.4+ [4] , SQLite 3.8.3+ [5] , IBM Informix version 11.50+, CUBRID och MySQL 8.0.1+[6] . Tableau och TIBCO Spotfire stöder inte CTE:er, medan implementeringen av Oracle 11g Release 2 saknar commit point semantik.
Utan vanliga tabelluttryck eller join-satser kan du köra hierarkiska frågor med hjälp av anpassade rekursiva funktioner. [7]
Ett vanligt tabelluttryck, eller CTE, (i SQL ) är en temporär namngiven resultatuppsättning som härrör från en enkel fråga och definieras inom exekveringsomfånget för en SELECT, INSERT, UPDATEeller sats DELETE.
CTE:er kan ses som ett alternativ till härledda tabeller (underfrågor), vyer och inbyggda användardefinierade funktioner.
Vanliga tabelluttryck stöds av Teradata , DB2 , Firebird [8] , Microsoft SQL Server , Oracle (med rekursion sedan version 11g 11g), PostgreSQL (från 8.4), MariaDB (från 10.2), MySQL (från 8.0), SQLite (sedan 3.8.3), HyperSQL och H2 (experimentell) [9] . Oracle kallar CTE för "subquery factoring". [tio]
Syntaxen för en rekursiv CTE är:
MED [ RECURSIVE ] with_query [, ...] VÄLJ ...där syntaxen with_queryär:
query_name [ ( kolumn_namn [,...]) ] AS ( VÄLJ ...)Rekursiva CTE:er (eller "rekursiv subquery factoring" [11] i Oracle-jargong) kan användas för att korsa relationer (i form av grafer eller träd), även om syntaxen är mycket mer komplex eftersom inga automatiska pseudo-kolumner skapas (som LEVEL Nedan); om de önskas måste de skapas i kod. Se MSDN-dokumentationen [2] eller IBM-dokumentationen [12] för fallstudier.
Nyckelordet RECURSIVEkrävs vanligtvis inte efter WITH på andra system än PostgreSQL. [13]
I SQL:1999 kan en rekursiv (CTE) fråga visas var som helst där en fråga är tillåten. Du kan till exempel namnge resultatet med CREATE[ RECURSIVE] VIEW[1] . Genom att använda CTE inuti INSERT INTOär det möjligt att fylla en tabell med data som genereras från en rekursiv fråga; slumpmässig datagenerering är möjlig med denna teknik utan användning av procedurpåståenden. [fjorton]
Vissa databaser, som PostgreSQL, stöder det kortare formatet CREATE RECURSIVE VIEW, som internt konverteras till MED RECURSIVE encoding. [femton]
Ett exempel på en rekursiv fråga som beräknar fakulteten av tal från 0 till 9 är följande:
MED REKURSIV temp (n, faktum) SOM ( VÄLJ 0, 1 -- Initial delfråga UNION ALLA VÄLJ n+1, (n+1)*fakta FRÅN temp -- Rekursiv delfråga VAR n < 9) VÄLJ * FRÅN temp;Den alternativa syntaxen är en anpassad konstruktion CONNECT BY; det introducerades av Oracle på 1980-talet. Före Oracle 10g var denna konstruktion endast användbar för att korsa acykliska grafer, eftersom den skulle returnera ett fel om några cykler hittades; I version 10g introducerade Oracle funktionen NOCYCLE (och nyckelordet) så att traversering fungerar även när det finns cykler. [16]
CONNECT BYstöds av EnterpriseDB, Oracle Database, [17] CUBRID, [18] IBM Informix och DB2, men endast om det är aktiverat som ett kompatibilitetsläge. Syntaxen ser ut så här:
SELECT select_list FROM table_expression [ VAR ... ] [ STARTA MED start_expression ] ANSLUTA MED [NOCYCLE] { FÖREGÅENDE child_expr = parent_expr | parent_expr = FÖREGÅENDE child_expr } [ BESTÄLL SYSKON EFTER kolumn1 [ ASC | DESC ][, kolumn2[ ASC | DESC ]] ... [ GRUPPER AV ... ] [ HA ...] ... Till exempel, VÄLJ NIVÅ , LPAD (' ', 2 * ( NIVÅ - 1)) || ename "anställd", empno, mgr "manager" FRÅN emp STARTA MED mgr ÄR NULL CONNECT BY PRIOR empno = mgr;Utdata från ovanstående fråga kommer att se ut så här:
nivå | anställd | empno | chef -------+-------------+--------+---- 1 | KUNGEN | 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 | AVDELNING | 7521 | 7698 3 | MARTIN | 7654 | 7698 3 | TURNER | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782 (14 rader)Följande exempel returnerar efternamnet på varje anställd på avdelning 10, varje chef ovanför den anställde i hierarkin, antalet nivåer mellan chefen och medarbetaren och sökvägen mellan dem:
VÄLJ ename " Employee", CONNECT_BY_ROOT ename "Manager", LEVEL -1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Path" FROM emp WHERE LEVEL > 1 och deptnr = 10 CONNECT BY PRIOR empno = BYEORploy ", "Manager", "Pathlen", "Path";