CTE Récursive

COMMENTSInstructions de procédure SQL (PSQL)

CTE récursive

Un ETC récursif (autoréférencé) est une UNION qui doit comporter au moins un élément non récursif auquel les autres éléments de l’union sont liés. 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 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 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.

Liste des champs`SELECT`

La liste des champs contient une ou plusieurs expressions séparées par des virgules. Le résultat de chaque expression est la valeur du champ correspondant dans l’ensemble de données SELECT. L’exception est l’expression * (‘asterisk’), qui renvoie tous les champs de la relation.

Syntaxe

SELECT
  [...]
  [DISTINCT | ALL] <select-list>
  [...]
  FROM ...

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    <qualifier>.*
  | <value-expression> [COLLATE collation] [[AS] alias]



<value-expression> ::=
    [<qualifier>.]col_name
  | [<qualifier>.]selectable_SP_outparm
  | <literal>
  | <context-variable>
  | <function-call>
  | <single-value-subselect>
  | <CASE-construct>
  | <other-single-value-expr>

Table 1. Paramètres de la liste des champs de l’instruction SELECT
Paramètre	Description
qualifier	Nom de la table (vue) ou alias d’une table (vue, procédure stockée, table dérivée).
collation	Nom de tri existant (uniquement pour les colonnes de type caractère).
alias	Alias du champ.
col_name	La colonne d’une table ou d’une vue.
selectable-SP-outparm	Le paramètre de sortie d’une procédure stockée sélective.
literal	Litéral
context-variable	La variable de contexte.
function-call	Appeler une fonction scalaire, agrégat ou window.
single-value-subselect	Une sous-requête qui renvoie une seule valeur scalaire.
CASE-construct	Design CASE.
other-single-value-expr	Toute autre expression qui renvoie une seule valeur de type de données Firebird ou `NULL`.

Une bonne pratique consiste à spécifier le nom du champ (ou *) avec le nom de la table/vue/procédure de stockage (ou son alias) à laquelle le champ appartient. Par exemple, relationname.columnname, relationname.*, alias.columnname, alias.*. La spécification du nom devient obligatoire si un champ portant le même nom se trouve dans plus d’une relation participant à l’union. La spécification de * est toujours obligatoire si ce n’est pas le seul élément de la liste de colonnes.

Important

Note

Les alias (alias) remplacent le nom original d’une table, d’une vue ou d’une procédure stockée : une fois qu’un alias est défini pour la relation correspondante, le nom original ne peut plus être utilisé.

Les mots-clés DISTINCT ou ALL peuvent être ajoutés au début de la liste des champs :

DISTINCT supprime les lignes en double : c’est-à-dire que si deux entrées ou plus contiennent les mêmes valeurs dans tous les champs pertinents, une seule de ces lignes sera incluse dans le jeu de données résultant.
ALL inclut toutes les lignes dans le jeu de données résultant. ALL est activé par défaut et donc rarement utilisé : une référence explicite est maintenue pour la compatibilité avec la norme SQL.

La clause COLLATE ne modifie pas le contenu d’un champ ; cependant, le fait de spécifier COLLATE pour un champ particulier peut modifier la sensibilité à la casse ou aux accents des caractères, ce qui peut à son tour affecter :

Ordre de tri, au cas où ce champ est spécifié dans une expression ORDER BY ;
Le regroupement, au cas où ce champ est spécifié dans la clause GROUP BY ;
Le nombre de lignes retournées si DISTINCT est utilisé.