jueves, 29 de enero de 2015

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.

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.

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 = 'USER'
CONNECT BY PRIOR emp_id = sup_id;
El script devolvera el nombre de todos los empleados y su cargo que dependen de USER. También podemos ver que la columna LEVEL muestra el nivel de la estructura en que se encuentra el registro. Por lo tanto, con la cláusula START WITH identificamos el registro inicial, y con la cláusula CONNECT BY PRIOR, indicamos las columnas entre las que establece la relación registro padre = registro hijo.

Existe 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 durante el recorrido dentro de una estructura jerárquica. Veamos un ejemplo para comprender mejor su funcionamiento:

SELECT SUBSTR(SYS_CONNECT_BY_PATH(NOMBRE, ','), 2) CSV
        FROM (SELECT NOMBRE,
                   ROW_NUMBER() OVER(ORDER BY CARGO) RN,
                   COUNT(*) OVER() CNT
              FROM JERARQUIA_EMPLEADOS
             WHERE EMP_ID = SUP_ID)
     WHERE RN = CNT
     START WITH RN = 1

    CONNECT BY RN = PRIOR RN + 1;

Al consultar  tendremos lo siguiente : PEDRO,JUAN,MARIA. Con ello resulta muy sencillo realizar consultas jerárquicas mediante la utilización de las cláusulas START WITH y CONNECT BY PRIOR, y la función PLSQL SYS_CONNECT_BY_PATH ().

No hay comentarios:

Publicar un comentario