Juez LTI

T01-012-01 Consultas jerárquicas

Enunciado

Si mostramos los 5 primeros registros de la tabla employees del esquema HR

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
FROM HR.employees
FETCH FIRST 5 ROWS ONLY;

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
100	Steven	King	-
101	Neena	Kochhar	100
102	Lex	De Haan	100
103	Alexander	Hunold	102
104	Bruce	Ernst	103

podemos observar que existe relaciones de jerarquía entre esos registros: Steven King es el superior jerárquico de Lex De Haan y éste, a su vez, es superior jerárquico de Alexander Hunold.

Oracle nos permite consultar las relaciones jerárquicas existentes entre los registros de las tablas.

Sintaxis:

SELECT ...
FROM ...
START WITH condicion
-- Especifica que registro se va a considerar como raíz de la jerarquía.
CONNECT BY PRIOR condicion
-- Especifica cuál es la relación que indica qué registro se considera padre y cuál se considera hijo en la jerarquía.
...

Resultado:

El orden en el que se muestran los registros es colocando los registros hijos inmediatamente después de sus registros padres, como muestra el siguiente árbol:

Ejemplo:

La siguiente consulta muestra las relaciones jerárquicas existentes entre los empleados:

SELECT CONCAT(LPAD(' ', 10 * (LEVEL - 1), '-'),EMPLOYEE_ID) AS NIVEL, FIRST_NAME, LAST_NAME, MANAGER_ID
FROM HR.employees
WHERE LEVEL <= 3
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
FETCH NEXT 20 ROWS ONLY

Para que podamos observar de manera gráfica esas relaciones utilizamos la función LPAD(' ', 10 * (LEVEL - 1), '-') que inserta 10 guiones a la izquierda por cada uno de los jefes que tiene por encima suyo.

El resultado es el siguiente:

NIVEL	FIRST_NAME	LAST_NAME	MANAGER_ID
100	Steven	King	-
--------- 101	Neena	Kochhar	100
------------------- 108	Nancy	Greenberg	101
------------------- 200	Jennifer	Whalen	101
------------------- 203	Susan	Mavris	101
------------------- 204	Hermann	Baer	101
------------------- 205	Shelley	Higgins	101
--------- 102	Lex	De Haan	100
------------------- 103	Alexander	Hunold	102
--------- 114	Den	Raphaely	100
------------------- 115	Alexander	Khoo	114
------------------- 116	Shelli	Baida	114
------------------- 117	Sigal	Tobias	114
------------------- 118	Guy	Himuro	114
------------------- 119	Karen	Colmenares	114
--------- 120	Matthew	Weiss	100
------------------- 125	Julia	Nayer	120
------------------- 126	Irene	Mikkilineni	120
------------------- 127	James	Landry	120
------------------- 128	Steven	Markle	120

Ejercicio:

Muestra las relaciones jerárquicas existentes que comienzan con empleados del departamento de nombre 'Executive'.

												
SELECT CONCAT(LPAD(' ', 10 * (LEVEL - 1), '-'),EMPLOYEE_ID) AS NIVEL, FIRST_NAME, LAST_NAME, employees.MANAGER_ID
FROM HR.employees LEFT JOIN HR.departments USING (DEPARTMENT_ID)
START WITH DEPARTMENT_NAME = 'Executive'
CONNECT BY PRIOR EMPLOYEE_ID = employees.MANAGER_ID
                                            

• Anterior
• Siguiente