Consultas Jerárquicas
Si una tabla contine datos jerárquicos (en forma de árbol), se pueden seleccionar estos en orden jerárquico mediante una consulta jerárquica.
select … start with initial-condition connect by nocycle recurse-condition
select … connect by recurse-condition
START WITH especifica la fila/s raiz de la jerarquía.
CONNECT BY especifica la relación entre las filas padres y las filas hijas de la jerarquía.
· El parámetro NOCYCLE da las instrucciones a la base de datos para devolver filas de una consulta incluso cuando exista un bucle en los datos jerárquicos. Se debe usar este parámetro junto con la pseudocolumna CONNECT_BY_ISCYCLE para ver que fila contiene el bucle.
· En una consulta jerárquica una expresión de la condición debe ser cualificada mediante el operador PRIOR para hacer referencia a la fila padre. Por ejemplo,
… PRIOR expr = expr
or
… expr = PRIOR expr
Si la condición CONNECT BY es compuesta, entonces solo una de las condiciones debe ser obligatorio que contenga el operador PRIOR, pero puede haber multiples condiciones PRIOR. Por ejemplo:
CONNECT BY
last_name != ‘King’ AND PRIOR empno = mgr …
CONNECT BY
PRIOR empno = mgr and
PRIOR account_mgr_id = customer_id …
PRIOR se utiliza normalmente con comparaciones de igualdad aunque se puede utilizar con cualquier otra, pero esto puede dar lugar a un bucle infinito. En este caso Oracle detecta el bucle y devuelve un error (excepto cuando se utiliza NOCYCLE).
Ejemplos de consultas jeráquicas:
Ejemplo de CONNECT BY para definir la relación entre empleados y directivos:
SELECT empno, ename, mgr
FROM scott.emp
CONNECT BY PRIOR empno = mgr;
EMPNO ENAME MGR
—– ——– —-
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7934 MILLER 7782
7876 ADAMS 7788
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
7369 SMITH 7902
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
Ejemplo de LEVEL Este ejemplo es similar al anterior pero usando la pseudocolumna LEVEL para distinguir las filas padres y las hijas:
SELECT empno, ename, mgr, LEVEL
FROM scott.emp
CONNECT BY PRIOR empno = mgr;
EMPNO ENAME MGR LEVEL
—– —— —- —–
7788 SCOTT 7566 1
7876 ADAMS 7788 2
7902 FORD 7566 1
7369 SMITH 7902 2
7499 ALLEN 7698 1
7521 WARD 7698 1
7654 MARTIN 7698 1
7844 TURNER 7698 1
7900 JAMES 7698 1
7934 MILLER 7782 1
7876 ADAMS 7788 1
7566 JONES 7839 1
7788 SCOTT 7566 2
7876 ADAMS 7788 3
7902 FORD 7566 2
7369 SMITH 7902 3
7698 BLAKE 7839 1
7499 ALLEN 7698 2
7521 WARD 7698 2
7654 MARTIN 7698 2
7844 TURNER 7698 2
7900 JAMES 7698 2
7782 CLARK 7839 1
7934 MILLER 7782 2
7369 SMITH 7902 1
7839 KING 1
7566 JONES 7839 2
7788 SCOTT 7566 3
7876 ADAMS 7788 4
7902 FORD 7566 3
7369 SMITH 7902 4
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7521 WARD 7698 3
7654 MARTIN 7698 3
7844 TURNER 7698 3
7900 JAMES 7698 3
7782 CLARK 7839 2
7934 MILLER 7782 3
Ejemplo de START WITH El siguiente ejemplo añade la clausula START WITH para especificar una fila raiz de la jerarquía, en nuestro caso el manager del departamento 20, JONES, y una ordenación mediante la clausula ORDER BY usando lapalabra SIBLINGS para mantener el orden en la jerarquía:
SELECT ename, empno, mgr, LEVEL
FROM scott.emp
START WITH empno = 7566
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;
Si no funciona utilizar la siguiente instrucción:
alter session set «_optimizer_connect_by_cost_based» = false;
ENAME EMPNO MGR LEVEL
—— —— —– —–
JONES 7566 7839 1
FORD 7902 7566 2
SMITH 7369 7902 3
SCOTT 7788 7566 2
ADAMS 7876 7788 3
En la tabla de empleados KING es el nivel mas alto de la compañía y no tiene manager (responsable). Entre sus empleados está JONES, el cual es el responsable del departamento 20. Si modificamos la tabla de empleados para hacer a JONES manager de KING, crearemos un bucle en los datos jerárquicos.
UPDATE scott.emp SET mgr = 7566
WHERE empno = 7839;
SELECT ename «Employee»,
LEVEL, SYS_CONNECT_BY_PATH(ename, ‘/’) «Path»
FROM scott.emp
WHERE level <= 3 AND deptno = 30
START WITH ename = ‘KING’
CONNECT BY PRIOR empno = mgr
AND LEVEL <= 4;
2 3 4 5 6 7 ERROR:
ORA-01436: CONNECT BY loop in user data
El parámetro NOCYCLE en la condición CONNECT BY hace que Oracle devuelva filas a pesar del bucle. La pseudocolumna CONNECT_BY_ISCYCLE nos mostrará que fila es la que contiene el bucle:
SELECT
ename «Employee»
,CONNECT_BY_ISCYCLE «Cycle»
,LEVEL
,SYS_CONNECT_BY_PATH(ename, ‘/’) «Path»
FROM
scott.emp
WHERE
level <= 4 AND deptno = 20
START WITH
ename = ‘KING’
CONNECT BY NOCYCLE
PRIOR empno = mgr
Employee Cycle LEVEL Path
——— —– —– ————————
JONES 1 2 /KING/JONES
SCOTT 0 3 /KING/JONES/SCOTT
ADAMS 0 4 /KING/JONES/SCOTT/ADAMS
FORD 0 3 /KING/JONES/FORD
SMITH 0 4 /KING/JONES/FORD/SMITH
Ejemplo de CONNECT_BY_ROOT. El siguiente ejemplo devuelve el nombre de cada empleado del departamento 20, cada manager por encima de cada empleado en la jerarquía, el número de niveles entre el manager y el empleado y del pathentre ambos:
SELECT
ename «Employee»
,CONNECT_BY_ROOT ename «Manager»
,LEVEL-1 «Pathlen»
,SYS_CONNECT_BY_PATH(ename, ‘/’) «Path»
FROM
scott.emp
WHERE
LEVEL > 1 and deptno = 20
CONNECT BY
PRIOR empno = mgr;
Employee Manager Pathlen Path
——– ——- ——- ———————–
ADAMS SCOTT 1 /SCOTT/ADAMS
SMITH FORD 1 /FORD/SMITH
SCOTT JONES 1 /JONES/SCOTT
ADAMS JONES 2 /JONES/SCOTT/ADAMS
FORD JONES 1 /JONES/FORD
SMITH JONES 2 /JONES/FORD/SMITH
JONES KING 1 /KING/JONES
SCOTT KING 2 /KING/JONES/SCOTT
ADAMS KING 3 /KING/JONES/SCOTT/ADAMS
FORD KING 2 /KING/JONES/FORD
SMITH KING 3 /KING/JONES/FORD/SMITH
El siguiente ejemplo utiliza la clausula GROUP BY para devolver la suma del salario total de cada empleado y todos los empleados que están por debajo en la jerarquía en el departamento 20:
SELECT
name,
SUM(sal) «Total_Sal»
FROM
(SELECT
CONNECT_BY_ROOT ename as name
,Sal
FROM
scott.emp
WHERE
deptno = 20
CONNECT BY
PRIOR empno = mgr)
GROUP BY
name;
NAME Total_Sal
—– ————
JONES 10875
FORD 3800
SMITH 800
SCOTT 4100
ADAMS 1100
KING 10875
