Cuando nos encontramos ante una tabla en la que los datos se encadenan siguiendo una estructura jerárquica (es decir, existen registros padre y registros hijo), puede llegar a ser necesario recuperar los datos de forma recursiva, mostrando la estructura jerárquica o la relación existente entre unos datos y otros.
Si todavía no tenéis claro de que estoy hablando, sólo tenéis que mirar a la estructura tipo árbol que aparece en la figura, donde vemos que del presidente de una empresa (nodo principal) cuelgan tres directores (nodos descendientes de primer nivel), de estos 6 supervisores (nodos descendientes de segundo nivel) y así hasta llegar a los empleados que no tienen gente a su cargo.
Ahora supongamos que almacenamos la estructura del ejemplo en una tabla como la que sigue:
CREATE TABLE jerarquia_empleados ( emp_id number, nombre varchar(6), cargo varchar(10), sup_id number);
Siendo los datos los siguientes:
Emp_ID Nombre Cargo Sup_ID ====== ====== ========== ====== 1 Laura Presidente 2 Pepe Director 1 3 Sergio Director 1 4 Elena Director 1 5 Sara Supervisor 2 6 Pedro Supervisor 2 7 José Supervisor 3 8 María Supervisor 3 9 Carlos Supervisor 4 10 Gema Supervisor 4 11 01 Empleado 5 12 02 Empleado 5 13 03 Empleado 5 14 04 Empleado 6 15 05 Empleado 6 16 06 Empleado 7 17 07 Empleado 7 18 08 Empleado 7 19 09 Empleado 8 20 10 Empleado 9 21 11 Empleado 10 22 12 Empleado 10
Como podéis observar, en la columna emp_id almacenamos un identificativo único de cada empleado, y la columna sup_id la utilizamos para almacenar el identificativo único correspondiente al superior de ese empleado.
Desde la versión 9i de la base de datos Oracle, SQL permite recuperar los datos de una tabla o vista siguiendo el recorrido de un árbol jerárquico, y dicho recorrido se puede realizar desde el nodo superior a los inferiores o desde los inferiores al nodo superior.
Cláusulas START WITH y CONNECT BY PRIOR
Para construir una consulta jerárquica, en el comando SQL SELECT se deben utilizar las cláusulas START WITH y CONNECT BY PRIOR. Por ejemplo, si utilizamos los datos de nuestra estructura y ejecutamos la siguiente consulta:
SELECT nombre, cargo, level FROM jerarquia_empleados START WITH nombre = 'Pepe' CONNECT BY PRIOR emp_id = sup_id;
Obtendremos los siguientes datos:
NOMBRE CARGO LEVEL ------ ---------- ---------- Pepe Director 1 Sara Supervisor 2 01 Empleado 3 02 Empleado 3 03 Empleado 3 Pedro Supervisor 2 04 Empleado 3 05 Empleado 3 8 filas seleccionadas.
Como podemos observar la consulta devuelve el nombre de todas las personas y su cargo que dependen del director Pepe (incluyendo a este mismo). También podemos ver que la pseudocolumna LEVEL, que también se podría utilizar en la cláusula WHERE de la consulta SELECT, muestra el nivel de la estructura en que se encuentra el registro, empezando desde el director Pepe.
Por lo tanto, con la cláusula START WITH identificamos el registro inicial, es decir, el lugar desde el que empieza nuestro árbol, y con la cláusula CONNECT BY PRIOR, indicamos las columnas entre las que establece la relación registro padre - registro hijo.
Si lo que queremos es subir en la jerarquía en lugar de bajar, entonces debemos cambiar el orden de los registros en la cláusula CONNECT BY PRIOR.
Veámoslo con un ejemplo.
SELECT nombre, cargo, level FROM jerarquia_empleados START WITH nombre = '09' CONNECT BY PRIOR sup_id = emp_id;
Que devuelve:
NOMBRE CARGO LEVEL ------ ---------- ---------- 09 Empleado 1 María Supervisor 2 Sergio Director 3 Laura Presidente 4 4 filas seleccionadas.
Es decir, en este caso el recorrido en el árbol se hace de abajo hacia arriba y lo que obtenemos son todos los jefes que tiene por encima el empleado 09. Como ya hemos mencionado, el orden de las columnas en la cláusula CONNECT BY PRIOR establece el orden del recorrido.
Por último, no podemos dejar de mencionar una función que puede resultar de mucha utilidad en las consultas jerárquicas, se trata de la función PL/SQL SYS_CONNECT_BY_PATH(), que permite concatenar los diferentes valores de las ramas del árbol mientras se recorre la estructura jerárquica.
Veamos un ejemplo para comprender mejor su funcionamiento:
SELECT level , nombre , SYS_CONNECT_BY_PATH(nombre,'>') recorrido FROM jerarquia_empleados START WITH nombre = 'Elena' CONNECT BY PRIOR emp_id = sup_id;
Consulta que genera la siguiente salida:
- LEVEL NOMBRE RECORRIDO ---------- ------ ---------------------- 1 Elena >Elena 2 Carlos >Elena>Carlos 3 10 >Elena>Carlos>10 2 Gema >Elena>Gema 3 11 >Elena>Gema>11 3 12 >Elena>Gema>12 6 filas seleccionadas.
Como podéis observar, resulta muy sencillo realizar consultas jerárquicas mediante la utilización de las cláusulas SQL START WITH y CONNECT BY PRIOR, y la función PLSQL SYS_CONNECT_BY_PATH (), ya sólo os queda practicar.
7 comentarios:
Muchas gracias por el aporte!!!
Excelente explicación! Muchas gracias !!!
Todo muy bien explicado. El Gráfico del arbol jerarquico , Tabla con sus campos, los ejemplos con su explicación.Muchas Gracias
Todo muy bien explicado y con muy buenos ejemplos, muchas gracias
supongamos que cada nivel tiene una cantidad de unidades. Sabes como hago para multiplicar para atras la cantidad de cada nivel?
Hola Unknown,
Para calcular un producto acumulado hay dos opciones:
1. Aprovecharse de la propiedad de los logaritmos:
LN(a*b) = LN(a)+LN(b)
Con lo cual puedes utilizar la cláusula SUM para calcular el producto de la siguiente manera:
EXP(SUM(LN(quantity)))
2. O lo que yo prefiero que es utilizar SYS_CONNECT_BY_PATH().
Por ejemplo:
'1'||SYS_CONNECT_BY_PATH(QUANTITY,'*')
Esa columna devolvería una cadena de caracteres del tipo:
1*2*4*1*2
Luego hay que utilizar una sencilla función que realice ese cálculo. Por ejemplo:
CREATE OR REPLACE FUNCTION fcalcular(pstring IN VARCHAR2)
RETURN NUMBER
IS
vcalculation NUMBER;
vsql VARCHAR2(1000);
BEGIN
vsql := REPLACE('SELECT ' || pstring || ' FROM DUAL', ',', '.');
EXECUTE IMMEDIATE vsql INTO vcalculation;
RETURN vcalculation;
END;
Al final quedaría:
fcalcular('1'||SYS_CONNECT_BY_PATH(QUANTITY,'*'))
Un saludo.
Excelente explicación brother, gracias por el aporte.
Publicar un comentario