RSS

Select recursivo en Oracle

28 Ene

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

Anuncios
 
3 comentarios

Publicado por en 28 enero, 2013 en programación

 

3 Respuestas a “Select recursivo en Oracle

  1. Robert

    12 febrero, 2015 at 5:04 pm

    Gracias muy bueno, lo único es que en la consulta me sirvió con CONNECT no con CONNECTED

     
  2. Benito villatoro

    24 abril, 2015 at 5:10 pm

    Necesito hacer una consulta recursiva pero en donde las relación gerárquica se encuentra en una segunda tabla.

     
  3. Ckevyn Ovalle

    20 marzo, 2017 at 5:39 pm

    Excelente post, me sirvio mucho; Cabe mencionar que la palabra “PRIOR” se coloca del lado del hijo, en este caso como se quiere construir el árbol desde abajo hacia arriba se coloca de ese lado. si se desea construir el árbol de arriba hacia abajo se coloca del lado izquierdo y las columns de START WITH las igualamos a NULL, eso pude observar.
    Suerte a todos.

     

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

 
A %d blogueros les gusta esto: