En el trabajo hoy hemos tenido que hacer una pequeña consulta recursiva sobre una tabla y como, aunque me acordaba que se podía hacer, no me acordaba de como, me ha todaco buscarlo. Así que para futuras ocasiones, y por si a alguien le es útil ahí va.

En primer lugar, que es, o a que llamo yo un select recursivo. Pues es una consulta en una tabla en la que uno o varios registos están vinculados entre sí por una relación generalemente jerárquica. Por poner un ejemplo imaginaros una tabla con empleados, donde cada empleado depende de otro. Dicha tabla sería algo así (resumida):

empleado_id | Nombre | jefe_id
---------------------------
        100 | Pepe   | 101
        101 | Juan   | 103
        102 | Jose   | 106
        103 | María  | 0 (0 -> No tiene jefe)
        106 | Ramón  | 0 (0 -> No tiene jefe)

La idea es recuperar en una sola consulta, todos los empleados asociados a Pepe, quizás respondiendo algo como “¿Quien es el jefe principal de Pepe?” Con una consulta normal, tendríamos que ejecutar tres sentencias, una para recuperar el jefe_id de Pepe, otra para recuperar a Juan y una última para recuperar a María. Extrapolad esto a una relación donde hayan cientos de elementos y tendréis un montón de conexiones a la BBDD innecesarias o algo peor. Con una consulta recursiva (Oracle la llama jerárquica) podríamos hacerlo todo de una vez.

select empleado_id, nombre, jefe_id, level
  from empleados
 start with empleado_id = 100
 connected by empleado_id = prior jefe_id
 order by level;

Como se puede ver la consulta es bastante intuitiva, empezando en nuestro empleado Pepe (empleado_id = 100) obtendrá todos los registros conectados con él a través del campo jafe_id, y para estos, hará lo mismo hasta no encontrar más resultado (llegará a María que no está relacionada con nadie jefe_id = 0). Tener cuidado con relaciones cíclicas.

Lo único importante digno de mención en la consulta es la palabrá PRIOR, depende de donde la pongamos, recorrerá la relación en un sentido u otro. Es decir, tal como está ahora busca la clave jefe_id en la columna empleado_id. Si cambiamos de posición la palabra reservada PRIOR (connected by prior empleado_id = jefe_id) buscará la clave empleado_id en la columna jefe_id. de está forma recorrerá las relaciones de hijos a padres o al revés.

La otra palabra reservada a la que merece la pena hacer mención es LEVEL, la cual nos muestra la posición de la relación en la que ha sido encontrado el registro. Es decir, para Pepe el nivel será igual a uno, para Juan será igual a dos y para María será igual a tres. En caso de haber registros en el mismo nivel, podrán aparecer varios niveles iguales.

Bueno, espero que os sirve a alguno, y desde luego a mi, espero que no se me vuelva a olvidar. Nos vemos.

Documentación de Oracle