Anuncios en tutorial de programación PLSQL

viernes, 20 de enero de 2017

Consultas jerárquicas en PL/SQL (cláusulas START WITH y CONNECT BY PRIOR)

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.

Arbol jerárquico

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:

Unknown dijo...

Muchas gracias por el aporte!!!

Unknown dijo...

Excelente explicación! Muchas gracias !!!

Jacobus dijo...

Todo muy bien explicado. El Gráfico del arbol jerarquico , Tabla con sus campos, los ejemplos con su explicación.Muchas Gracias

carloncho.agapornis dijo...

Todo muy bien explicado y con muy buenos ejemplos, muchas gracias

Unknown dijo...

supongamos que cada nivel tiene una cantidad de unidades. Sabes como hago para multiplicar para atras la cantidad de cada nivel?

JLPM dijo...

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.

SandroX dijo...

Excelente explicación brother, gracias por el aporte.