•Interpretar el concepto de consulta jerárquica.
•Crear un informe en estructura de árbol.
•Formatear datos jerárquicos.
•Excluir ramas de la estructura de árbol.
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
_____________________________________________________________________________________
Consultas Jerárquicas.
Mediante consultas jerárquicas, puede recuperar datos basándose en una relación jerárquica natural entre filas de una tabla. Una base de datos relacional no almacena registros de forma jerárquica. Sin embargo, si existe una relación jerárquica entre las filas de una sola tabla, un proceso denominado desplazamiento por el árbol permite que se construya la jerarquía. Una consulta jerárquica es un método de informar de las ramas de un árbol en un orden específico.
Imagine un árbol genealógico con los miembros mayores de la familia situados cerca de la base o del tronco del árbol y los más jóvenes representados como ramas del árbol. Las ramas pueden tener más ramas, y así sucesivamente.
Tabla Employees:
Una consulta jerárquica es posible cuando existe una relación entre filas en una tabla. Por ejemplo, en la imagen anterior, se ve que los empleados con el identificador de puesto AD_VP, ST_MAN, SA_MAN y MK_MAN están bajo las órdenes directas del presidente de la compañía. Lo sabemos porque la columna MANAGER_ID de estos registros contiene el identificador de empleado 100, que pertenece al presidente (AD_PRES).
Nota: Los árboles jerárquicos se utilizan en diversos campos como, por ejemplo, la genealogía humana (árboles genealógicos), la cría de ganado (reproducción), la gerencia empresarial (jerarquías de los supervisores), la manufactura (ensamblaje de productos), la investigación evolutiva (desarrollo de especies) y la investigación científica.
Estructura de Árbol Natural.
La tabla EMPLOYEES tiene una estructura de árbol que representa la línea de supervisores. Para crear la jerarquía, se puede observar la relación entre valores equivalentes de las columnas EMPLOYEE_ID y MANAGER_ID . Para utilizar esta relación, se puede unir la tabla a sí misma. La columna MANAGER_ID contiene el número de empleado del supervisor del empleado.
La relación principal-secundario de una estructura de árbol le permite controlar:
•La dirección de desplazamiento por la jerarquía.
•El punto de partida dentro de la jerarquía.
Nota: La ultima imagen muestra una estructura de árbol invertido de la jerarquía de supervisores de los empleados de la tabla EMPLOYEES.
Sintaxis:
SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];
En la sintaxis:
•SELECT: Es la cláusula SELECT estándar.
•LEVEL: Para cada fila devuelta por una consulta jerárquica, la pseudocolumna LEVEL devuelve 1 para una fila de raíz, 2 para un secundario de la raíz y así sucesivamente.
•FROM: table Especifica la tabla, la vista o la instantánea que contiene las columnas. Puede seleccionar de una sola tabla.
•WHERE: Restringe las filas devueltas por la consulta sin que afecte a otras filas de la jerarquía.
•condition: Es una comparación con expresiones.
•START WITH: Especifica las filas de raíz de la jerarquía (dónde comenzar). Esta cláusula es necesaria para una consulta jerárquica verdadera.
•CONNECT BY: Especifica las columnas en las que existe la relación entre filas PRIOR principales y secundarias. Esta cláusula es necesaria para una consulta jerárquica.
Las consultas jerárquicas se pueden identificar por la presencia de las cláusulas CONNECT BY y START WITH.
La sentencia SELECT no puede contener una unión ni una consulta de una vista que contenga una unión.
_____________________________________________________________________________________
Desplazamiento por el Árbol.
La cláusula START WITH (Punto de Partida) determina la fila o las filas que se deben utilizar como raíz del árbol. La cláusula START WITH se puede utilizar en conjunción con cualquier condición válida.
... START WITH manager_id IS NULL.
---
... START WITH employee_id = (SELECT employee_id
FROM employees
WHERE last_name = 'Kochhar')
---
Si se omite la cláusula START WITH, el desplazamiento por el árbol parte de todas las filas de la tabla como filas de raíz. Si se utiliza una cláusula WHERE, el desplazamiento parte de todas las filas que satisfacen la condición WHERE. Esto ya no refleja una jerarquía verdadera.
Si se omite la cláusula START WITH, el desplazamiento por el árbol parte de todas las filas de la tabla como filas de raíz. Si se utiliza una cláusula WHERE, el desplazamiento parte de todas las filas que satisfacen la condición WHERE. Esto ya no refleja una jerarquía verdadera.
Nota: Las cláusulas CONNECT BY PRIOR y START WITH no son del estándar ANSI SQL.
La dirección de la consulta, ya sea de principal a secundario o de secundario a principal, está determinada por la colocación de la columna CONNECT BY PRIOR. El operador PRIOR se refiere a la fila principal. Para encontrar las filas secundarias de una fila principal, Oracle Server evalúa la expresión PRIOR para la fila principal y las demás expresiones para cada fila de la tabla. Las filas para las que la condición es verdadera son las filas secundarias de la principal. Oracle Server selecciona siempre las filas secundarias evaluando la condición CONNECT BY con respecto a una fila principal actual.
... CONNECT BY PRIOR employee_id = manager_id
---
... CONNECT BY PRIOR manager_id = employee_id
---
No es necesario codificar inmediatamente el operador PRIOR después de CONNECT BY. Así, la siguiente cláusula CONNECT BY PRIOR genera el mismo resultado que la del ejemplo anterior.
... CONNECT BY employee_id = PRIOR manager_id
---
Nota: La cláusula CONNECT BY no puede contener una subconsulta.
Ejemplos:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id;
/*En el ejemplo se muestra una lista de supervisores que comienza por el empleado cuyo identificador es 101.*/
---
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id
AND salary > 9000;
/*En este ejemplo, los valores de EMPLOYEE_ID se evalúan para la fila principal y MANAGER_ID, y los valores de SALARY son evaluados por las filas secundarias. El operador PRIOR se aplica sólo al valor de EMPLOYEE_ID.
Para calificarse como fila secundaria, una fila debe tener un valor de MANAGER_ID igual al valor EMPLOYEE_ID de la fila principal y debe tener un valor SALARY superior a 15.000 dólares.*/
---SELECT last_name||' Reporta a '||
PRIOR last_name "De Arriba hacia Abajo"
FROM employees
START WITH last_name = 'King' AND manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
/**/
---
Pseudocolumna LEVEL.
Puede mostrar explícitamente la clasificación o el nivel de una fila en la jerarquía mediante la pseudocolumna LEVEL. Esto hará que el informe resulte más legible. Las bifurcaciones en las que salen una o varias ramas de una rama mayor se denominan nodos y el extremo de una rama se denomina hoja, o nodo hoja.
Valor
|
Nivel
|
1
|
Nodo raíz
|
2
|
Secundario de un nodo raíz
|
3
|
Secundario de un secundario y así sucesivamente.
|
El diagrama muestra los nodos del árbol invertido con sus valores LEVEL. Por ejemplo, el empleado Higgins es principal y secundario, mientras que el empleado Davies es secundario y hoja.
King es la raíz o el principal (LEVEL = 1). Kochhar, De Haan, Mourgos, Zlotkey, Hartstein, Higgins y Hunold son secundarios y también principales (LEVEL = 2). Whalen, Rajs, Davies, Matos, Vargas, Gietz, Ernst, Lorentz, Abel, Taylor, Grant y Fay son secundarios y hojas. (LEVEL = 3 y LEVEL = 4).
Nota: Un nodo raíz es el nodo más alto dentro de un árbol invertido. Un nodo secundario es cualquier nodo no raíz. Un nodo principal es cualquier nodo que tenga secundarios. Un nodo hoja es cualquier nodo sin secundarios. El número de niveles devuelto por una consulta jerárquica se puede ver limitado por la memoria de usuario disponible.
Ejemplo:
SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS Nivel
FROM employees
START WITH last_name='King'
CONNECT BY PRIOR employee_id=manager_id;
/*En el ejemplo:
•LPAD(char1,n [,char2]) devuelve char1, rellenado a la izquierda hasta la longitud n con la secuencia de caracteres en char2. El argumento n es la longitud total del valor de retorno como se muestra en la pantalla de su terminal.
•LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') define el formato de visualización.
•char1 es LAST_NAME, n la longitud total del valor de retorno total es la longitud de LAST_NAME +(LEVEL*2)-2 y char2 es '_'.
Dicho de otro modo, esto indica a SQL que tome LAST_NAME y lo rellene a la izquierda con el carácter '_' hasta que la longitud de la cadena resultante sea igual a los valores determinados por LENGTH(last_name)+(LEVEL*2)-2.
Para King, LEVEL = 1. Por tanto, (2 * 1) – 2 = 2 – 2 = 0. Así pues, King no se rellena con ningún carácter '_' y se muestra en la columna 1.
Para Kochhar, LEVEL = 2. Por tanto, (2 * 2) – 2 = 4 – 2 = 2 . Así pues, Kochhar se rellena con 2 caracteres '_' y se muestra con sangrado.
El resto de los registros de la tabla EMPLOYEES se muestra de forma parecida.*/
---
Eliminación de Ramas.
Puede utilizar las cláusulas WHERE y CONNECT BY para eliminar el árbol; es decir, para controlar qué nodos o qué filas se muestran. El predicado que utilice funciona como condición booleana.
Si el caso es que desea eliminar una Rama completa entonces usamos cláusula CONNECT BY.
Ejemplos:
SELECT employee_id,last_name, job_id, manager_id
FROM employees
WHERE last_name != 'Greenberg'
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
/*Este ejemplo muestro como eliminar un nodo, en este caso el empleado Greenberg. Notar que solo falta él en la lista de empleados.*/
---
SELECT employee_id,last_name, job_id, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
AND last_name != 'Greenberg';
/*En este eliminamos la Rama completa, osea, el empleado Greenberg y sus hijos. Notar la cantidad de registros que no se muestran en la consulta.*/
_____________________________________________________________________________________
_____________________________________________________________________________________
