Expressions De Table Courantes CTE ("WITH … AS … SELECT")

COMMENTSInstructions de procédure SQL (PSQL)

expressions de table courantes CTE ("WITH … AS … SELECT")

expressions de table courantes (Common Table Expressions), en abrégé CTE, sont décrites comme des tables ou des vues virtuelles définies dans le préambule de la requête principale, qui participent à la requête principale. La requête principale peut faire référence à n’importe quelle CTE définie dans le préambule, comme lors de l’extraction de données de tables ou de vues ordinaires. Les CTE peuvent être récursives, c’est-à-dire se référer à elles-mêmes, mais ne peuvent pas être imbriquées.

Syntaxe

<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [   <rows-clause>
    | { [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <cte> [, <cte> ...]

<cte> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

<column-name-list> ::= column-name [, column-name ...]

Table 1. Paramètres CTE
Paramètre	Description
query-name	Le nom de l’expression du tableau.
column-name	Un alias (alias) pour une colonne d’une expression de tableau.

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)

Expressions De Table Courantes CTE ("WITH …​ AS …​ SELECT")