Una consulta jerárquica es un tipo de consulta SQL que procesa datos de modelos jerárquicos. Son casos especiales de consultas recursivas de punto fijo más generales que evalúan cierres transitivos.
En SQL estándar: 1999, las consultas jerárquicas se implementan mediante expresiones de tabla comunes (CTE) recursivas. A diferencia de la propuesta de conexión anterior de Oracle, los CTE recursivos se diseñaron con semántica de punto fijo desde el principio. Los CTE recursivos del estándar estaban relativamente cerca de la implementación existente en IBM DB2 versión 2 [1] . Los CTE recursivos también son compatibles con Microsoft SQL Server (desde SQL Server 2008 R2) [2] , Firebird 2.1 [3] , PostgreSQL 8.4+ [4] , SQLite 3.8.3+ [5] , IBM Informix versión 11.50+, CUBRID y MySQL 8.0.1+ [6] . Tableau y TIBCO Spotfire no admiten CTE, mientras que la implementación de Oracle 11g versión 2 carece de semántica de punto de confirmación.
Sin expresiones de tabla comunes ni cláusulas de combinación, puede ejecutar consultas jerárquicas mediante funciones recursivas personalizadas. [7]
Una expresión de tabla común, o CTE, (en SQL ) es un conjunto de resultados con nombre temporal derivado de una consulta simple y definido dentro del ámbito de ejecución de una SELECTinstrucción o . INSERTUPDATEDELETE
Las CTE se pueden considerar como una alternativa a las tablas derivadas (subconsultas), vistas y funciones integradas definidas por el usuario.
Las expresiones de tabla comunes son compatibles con Teradata , DB2 , Firebird [8] , Microsoft SQL Server , Oracle (con recursividad desde la versión 11g 11g), PostgreSQL (desde 8.4), MariaDB (desde 10.2), MySQL (desde 8.0), SQLite (desde 3.8.3), HyperSQL y H2 (experimental) [9] . Oracle llama a CTE "factorización de subconsultas". [diez]
La sintaxis para un CTE recursivo es:
CON [ RECURSIVO ] with_query [, ...] SELECCIONAR ...donde la sintaxis with_queryes:
nombre_consulta [ ( nombre_columna [,...]) ] COMO ( SELECCIONAR ...)Los CTE recursivos (o "factorización de subconsultas recursivas" [11] en la jerga de Oracle) se pueden usar para atravesar relaciones (en forma de gráficos o árboles), aunque la sintaxis es mucho más compleja ya que no se crean pseudocolumnas automáticas (como LEVEL abajo); si se desean, deben crearse en código. Consulte la documentación de MSDN [2] o la documentación de IBM [12] para conocer casos prácticos.
La palabra clave RECURSIVEgeneralmente no se requiere después de WITH en sistemas que no sean PostgreSQL. [13]
En SQL:1999, una consulta recursiva (CTE) puede aparecer en cualquier lugar donde se permita una consulta. Por ejemplo, puede nombrar el resultado usando CREATE[ RECURSIVE] VIEW[1] . Al usar CTE inside INSERT INTO, es posible completar una tabla con datos generados a partir de una consulta recursiva; la generación aleatoria de datos es posible utilizando esta técnica sin el uso de declaraciones de procedimiento. [catorce]
Algunas bases de datos, como PostgreSQL, admiten el formato más breve CREAR VISTA RECURSIVA, que se convierte internamente a CON codificación RECURSIVA. [quince]
Un ejemplo de consulta recursiva que calcula el factorial de números del 0 al 9 es el siguiente:
CON RECURSIVO temp (n, hecho) COMO ( SELECCIONE 0, 1 -- Subconsulta inicial UNION ALL SELECT n+1, (n+1)*hecho DESDE temp -- Subconsulta recursiva DONDE n < 9) SELECCIONE * DESDE temp;La sintaxis alternativa es una construcción personalizada CONNECT BY; fue introducido por Oracle en la década de 1980. Antes de Oracle 10g, esta construcción solo era útil para atravesar gráficos acíclicos, ya que devolvía un error si se encontraba algún ciclo; En la versión 10g, Oracle introdujo la función NOCYCLE (y la palabra clave) para que el recorrido funcione incluso cuando hay ciclos. [dieciséis]
CONNECT BYcompatible con EnterpriseDB, Oracle Database, [17] CUBRID, [18] IBM Informix y DB2, aunque solo si está habilitado como modo de compatibilidad. La sintaxis se ve así:
SELECCIONE select_list DESDE table_expression [ DONDE ... ] [ COMENZAR CON start_expression ] CONECTAR POR [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = ANTERIOR child_expr } [ ORDENAR HERMANOS POR columna1 [ ASC | DESC ][, columna2[ ASC | DESC ]] ... [ AGRUPADO POR ... ] [ TENIENDO ...] ... Por ejemplo, SELECCIONE NIVEL , LPAD (' ', 2 * ( NIVEL - 1)) || ename "empleado", empno, mgr "gerente" DESDE emp EMPIEZA CON mgr ES NULO CONECTAR POR ANTERIOR empno = mgr;El resultado de la consulta anterior se verá así:
nivel | empleado | empno | gerente -------+-------------+-------+--------- 1 | REY | 7839 | 2 | JONES | 7566 | 7839 3 | SCOTT | 7788 | 7566 4 | ADÁN | 7876 | 7788 3 | FORD | 7902 | 7566 4 | SMITH | 7369 | 7902 2 | BLAKE | 7698 | 7839 3 | ALLEN | 7499 | 7698 3 | SALA | 7521 | 7698 3 | MARTÍN | 7654 | 7698 3 | TORNEADOR | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | clark | 7782 | 7839 3 | MILLER | 7934 | 7782 (14 filas)El siguiente ejemplo devuelve el apellido de cada empleado en el departamento 10, cada gerente por encima de ese empleado en la jerarquía, el número de niveles entre el gerente y el empleado y la ruta entre ellos:
SELECCIONE nombre "Empleado", CONNECT_BY_ROOT nombre "Gerente", NIVEL -1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Ruta" FROM emp WHERE LEVEL > 1 and deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Empleado ", "Administrador", "Pathlen", "Ruta";