En un artículo anterior ya vimos un ejemplo de un SELECT recursivo, el cual fue muy útil, así que ahora explicaremos mejor como crear SELECTs recursivos.

  1. Se construye usando CTE (Common Table Expression)
  2. Un SELECT recursivo es una UNION entre miembros recursivos y no recursivos
  3. Debe tener al menos un miembro no recursivo al cual se le llama anchor (ancla)
  4. El miembro (o miembros) no recursivo/s debe colocarse antes que el miembro (o miembros) recursivo/s
  5. El enlace entre el miembro (o miembros) no recursivo/s y el miembro (o miembros) recursivo/s se hace mediante UNION ALL
  6. Las uniones entre los miembros no recursivos puede ser de cualquier tipo
  7. Después de la palabra WITH hay que escribir la palabra RECURSIVE
  8. El nombre de la tabla virtual puede usarse solamente en un FROM o en un INNER JOIN
  9. En el miembro (o miembros) recursivo/s no puede usarse un OUTER JOIN (LEFT JOIN, RIGHT JOIN, FULL JOIN). En el miembro (o miembros) no recursivo/s sí pueden usarse los OUTER JOIN
  10. Un SELECT recursivo usa mucha menos memoria y ciclos de CPU que un stored procedure recursivo equivalente
  11. En el miembro (o miembros) recursivo/s no se pueden usar agregados (DISTINCT, GROUP BY, HAVING). En el miembro (o miembros) no recursivos, sí
  12. En el miembro (o miembros) recursivo/s no se pueden usar funciones agregadas (COUNT(), SUM(), AVG(), MAX(), MIN(), LIST()). En el miembro (o miembros) no recursivo/s, sí
  13. La máxima profundidad admitida es 1024

Listado 1.

WITH RECURSIVE MiTablaVirtual AS (
   SELECT <datos_padre>
   UNION ALL
   SELECT <datos_hijo> JOIN <MiTablaVirtual> ON <enlace_con_el_padre>
)

SELECT * FROM MiTablaVirtual
  1. La ejecución se inicia en el primer SELECT
  2. El conjunto resultado obtenido del primer SELECT se llama “MiTablaVirtual” (en este ejemplo) y como cuando finaliza ya existe “MiTablaVirtual” entonces a ésta se la puede referenciar en el segundo SELECT.
  3. A “MiTablaVirtual” se le unen los conjuntos resultados obtenidos del segundo SELECT
  4. Cuando de la ejecución del segundo SELECT se obtiene un conjunto resultado vacío (es decir, ninguna fila) se regresa un nivel y se obtiene la siguiente fila, si es posible
  5. Cuando ya no se pueden obtener filas, termina la recursividad
  6. A continuación del CTE hay que usar la tabla virtual creada porque de lo contrario todo lo que se hizo en ella se perderá
  7. Los nombres de las columnas que pueden usarse externamente de la tabla virtual son los nombres de columnas que especificamos en el segundo SELECT. O sea que en el “SELECT * FROM MiTablaVirtual” del Listado 1. veremos los nombres de las columnas del segundo SELECT.

Tipos de recursión

La recursión generalmente es útil cuando podemos representar a los datos en forma de árbol, como en el Gráfico 1., que es el caso más común. También es muy útil si los datos se pueden representar como pilas, colas o listas; cuando no es así en general no se puede o no se debe usar recursión.

RECURSIÓN01

Gráfico 1. Si haces clic en la imágen la verás más grande

Hay dos tipos de recursión:

  • De arriba hacia abajo
  • De abajo hacia arriba

Cuando la recursión es de abajo hacia arriba, puede reemplazarse con iteración, es decir con un ciclo WHILE que vaya subiendo al nodo superior hasta descubrir que ya no hay más nodos.

El Firebird tiene actualmente un límite de 1024 llamadas recursivas. O sea que en el Gráfico 1. podríamos tener como máximo 1024 nodos. Esto es mucho más que suficiente para la gran mayoría de las aplicaciones … pero no para todas.

Artículos relacionados:

Usando recursividad con CTE

Stored procedures recursivos

El índice del blog Firebird21

El foro del blog Firebird21