Notes Exemples

COMMENTSInstructions de procédure SQL (PSQL)

Notes

Une définition CTE peut contenir n’importe quel instruction SELECT valide tant qu’elle ne contient pas le préambule “`WITH…`” (les instructions WITH ne peuvent pas être imbriqués) ;
Les CTE peuvent s’utiliser mutuellement, mais les références ne doivent pas comporter de boucles ;
CTE peut être utilisé dans n’importe quelle partie de la requête principale ou autre expression tabulaire et autant de fois que souhaité ;
La requête principale peut faire référence à un CTE plusieurs fois, mais avec des alias différents ;
Les CTE peuvent être utilisés dans les instructions INSERT, UPDATE et DELETE comme sous-requêtes ;
Si le CTE déclaré n’est pas utilisé, un message d’avertissement “CTE cte is not used in query” sera émis. Dans les versions antérieures, une erreur était émise au lieu d’un avertissement ;

Les CTE peuvent également être utilisés dans PSQL dans les boucles FOR :

FOR
  WITH
    MY_RIVERS AS (
      SELECT *
      FROM RIVERS
      WHERE OWNER = 'me'
    )
  SELECT
    NAME,
    LENGTH
  FROM MY_RIVERS
  INTO :RNAME,
       :RLEN
DO
BEGIN
  ...
END

Exemples

Example 1. Requête utilisant CTE

WITH
  DEPT_YEAR_BUDGET AS (
    SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
    FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  )
SELECT
  D.DEPT_NO,
  D.DEPARTMENT,
  DYB_2008.BUDGET BUDGET_08,
  DYB_2009.BUDGET AS BUDGET_09
FROM
  DEPARTMENT 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
WHERE EXISTS (SELECT *
              FROM PROJ_DEPT_BUDGET B
              WHERE D.DEPT_NO = B.DEPT_NO)

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.