CTE récursive

Une CTE récursif est une CTE qui doit avoir au moins un élément non récursif auquel les autres membres de l'association se lient. L'élément non récursif est placé en premier dans la CTE. Les membres récursifs sont séparés des membres non récursifs et les uns des autres par `UNION ALL'. L'association des membres non récursifs peut être de n'importe quel type.

Le CTE récursif nécessite le mot clé RECURSIVE à droite de WITH. Chaque membre récursif ne peut se référer qu'une seule fois à lui-même et cela doit être fait dans une clause FROM.

Le principal avantage des CTE récursifs est qu'ils utilisent beaucoup moins de mémoire et de temps CPU que les procédures stockées récursives équivalentes.

Exécution d'une opération récursive CTE

L'exécution d'un CTE récursif du point de vue du serveur Firebird peut être décrite comme suit :

Le serveur commence l'exécution avec le membre non-récursif ;
Pour chaque ligne sélectionnée dans la partie non récursive, chaque membre récursif est exécuté un par un, en utilisant les valeurs actuelles de l'itération précédente comme paramètres ;
Si l'instance membre récursive ne produit aucune ligne pendant l'exécution, la boucle d'exécution passe au niveau précédent et récupère la ligne suivante dans le jeu de données externe.

Exemples

Example 1. Récursive CTE

WITH RECURSIVE DEPT_YEAR_BUDGET AS (
  SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
  FROM PROJ_DEPT_BUDGET
  GROUP BY FISCAL_YEAR, DEPT_NO
),
DEPT_TREE AS (
  SELECT
      DEPT_NO,
      HEAD_DEPT,
      DEPARTMENT,
      CAST('' AS VARCHAR(255)) AS INDENT
  FROM DEPARTMENT
  WHERE HEAD_DEPT IS NULL
  UNION ALL
  SELECT
      D.DEPT_NO,
      D.HEAD_DEPT,
      D.DEPARTMENT,
      H.INDENT || ' '
  FROM DEPARTMENT D
    JOIN DEPT_TREE H ON H.HEAD_DEPT = D.DEPT_NO
)
SELECT
    D.DEPT_NO,
    D.INDENT || D.DEPARTMENT DEPARTMENT,
    DYB_2008.BUDGET AS BUDGET_08,
    DYB_2009.BUDGET AS BUDGET_09
FROM DEPT_TREE D
    LEFT JOIN DEPT_YEAR_BUDGET DYB_2008 ON
      (D.DEPT_NO = DYB_2008.DEPT_NO) AND
      (DYB_2008.FISCAL_YEAR = 2008)
    LEFT JOIN DEPT_YEAR_BUDGET DYB_2009 ON
      (D.DEPT_NO = DYB_2009.DEPT_NO) AND
      (DYB_2009.FISCAL_YEAR = 2009)

L'exemple suivant permet de dériver le pedigree d'un cheval, la principale différence étant que la récursion passe par deux branches de l'arbre généalogique à la fois.

WITH RECURSIVE PEDIGREE (
    CODE_HORSE,
    CODE_FATHER,
    CODE_MOTHER,
    NAME,
    MARK,
    DEPTH)
AS (SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        CAST('' AS VARCHAR(80)),
        0
    FROM HORSE
    WHERE
        HORSE.CODE_HORSE = :CODE_HORSE
    UNION ALL
    SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        'F' || PEDIGREE.MARK,
        PEDIGREE.DEPTH + 1
    FROM HORSE
        JOIN PEDIGREE
          ON HORSE.CODE_HORSE = PEDIGREE.CODE_FATHER
    WHERE
        -- limite de la profondeur de récursion
        PEDIGREE.DEPTH < :MAX_DEPTH
    UNION ALL
    SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        'M' || PEDIGREE.MARK,
        PEDIGREE.DEPTH + 1
    FROM HORSE
        JOIN PEDIGREE
          ON HORSE.CODE_HORSE = PEDIGREE.CODE_MOTHER
    WHERE
        -- limite de la profondeur de récursion
        PEDIGREE.DEPTH < :MAX_DEPTH
)
SELECT
    CODE_HORSE,
    NAME,
    MARK,
    DEPTH
FROM
    PEDIGREE

Notes sur la récurrence CTE:

Les agrégats (DISTINCT, GROUP BY, HAVING) et les fonctions d'agrégation (SUM, COUNT, MAX, etc.) ne sont pas autorisés dans les membres d'unions récursives ;
Le lien récursif ne peut pas être membre de l'association externe OUTER JOIN ;
La profondeur maximale de la récursion est de 1024 ;
Un membre récursif ne peut pas être représenté comme une table dérivée.