Примечания Примеры

COMMENTSОператоры процедурного SQL (PSQL)

Примечания

Определение CTE может содержать любой правильный оператор SELECT, если он не содержит преамбулы “WITH…” (операторы WITH не могут быть вложенными);
CTE могут использовать друг друга, но ссылки не должны иметь циклов;
CTE могут быть использованы в любой части главного запроса или другого табличного выражения и сколько угодно раз;
Основной запрос может ссылаться на CTE несколько раз, но с разными алиасами;
CTE могут быть использованы в операторах INSERT, UPDATE и DELETE как подзапросы;
Если объявленное CTE не использовано, то будет выдано предупреждение “CTE cte is not used in query”. В более ранних версиях вместо предупреждения выдавалась ошибка;

CTE могут быть использованы и в PSQL в FOR циклах:

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

Примеры

Example 1. Запрос с использованием 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

Рекурсивное (ссылающееся само на себя) CTE это UNION, у которого должен быть, по крайней мере, один не рекурсивный элемент, к которому привязываются остальные элементы объединения.Не рекурсивный элемент помещается в CTE первым.Рекурсивные члены отделяются от не рекурсивных и друг от друга с помощью UNION ALL.Объединение не рекурсивных элементов может быть любого типа.

Рекурсивное CTE требует наличия ключевого слова RECURSIVE справа от WITH.Каждый рекурсивный член объединения может сослаться на себя только один раз и это должно быть сделано в предложении FROM.

Главным преимуществом рекурсивных CTE является то, что они используют гораздо меньше памяти и процессорного времени, чем эквивалентные рекурсивные хранимые процедуры.

Выполнение рекурсивного CTE

Выполнение рекурсивного CTE с точки зрения сервера Firebird можно описать следующим образом:

Сервер начинает выполнение с не рекурсивного члена;
Для каждой выбранной строки из нерекурсивного части выполняется каждый рекурсивный член один за другим, используя текущие значения из предыдущей итерации как параметры;
Если во время выполнения экземпляр рекурсивного элемента не выдаёт строк, цикл выполнения переходит на предыдущий уровень и получает следующую строку от внешнего для него набора данных.

Примеры

Example 1. Рекурсивное 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)

Следующий пример выводит родословную лошади.Основное отличие состоит в том, что рекурсия идёт сразу по двум веткам дерева родословной.

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
      –- ограничение глубины рекурсии
      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
      –- ограничение глубины рекурсии
      PEDIGREE.DEPTH < :MAX_DEPTH
  )
SELECT
  CODE_HORSE,
  NAME,
  MARK,
  DEPTH
FROM
  PEDIGREE

Примечания для рекурсивного CTE:

В рекурсивных членах объединения не разрешается использовать агрегаты (DISTINCT, GROUP BY, HAVING) и агрегатные функции (SUM, COUNT, MAX и т.п.);
Рекурсивная ссылка не может быть участником внешнего объединения OUTER JOIN;
Максимальная глубина рекурсии составляет 1024;
Рекурсивный член не может быть представлен в виде производной таблицы.