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

Рекурсивные 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;

  • Рекурсивный член не может быть представлен в виде производной таблицы.

Список полей SELECT

Список полей содержит одно или более выражений, разделённых запятыми.Результатом каждого выражения является значение соответствующего поля в наборе данных команды SELECT.Исключением является выражение * (“звёздочка”), которое возвращает все поля отношения.

Синтаксис
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. Параметры списка полей оператора SELECT
Параметр Описание

qualifier

Имя таблицы (представления) или псевдоним таблицы (представления, хранимой процедуры, производной таблицы).

collation

Существующее имя сортировки (только для выражений символьных типов).

alias

Псевдоним поля.

col_name

Столбец таблицы или представления.

selectable-SP-outparm

Выходной параметр селективной хранимой процедуры.

literal

Литерал.

context-variable

Контекстная переменная.

function-call

Вызов скалярной, агрегатной или оконной функции.

single-value-subselect

Подзапрос, возвращающий единственное скалярное значение.

CASE-construct

Конструкция CASE.

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

Хорошим тоном является уточнять имя поля (или “*”) именем таблицы/представления/хранимой процедуры (или их псевдонимом), к которой это поле принадлежит. Например, relationname.columnname, relationname.*, alias.columnname, alias.*.Уточнение имени становится обязательным в случае, если поле с одним и тем же именем находится в более чем одном отношении, участвующей в объединении.Уточнение для “*” всегда обязательна, если это не единственный элемент в списке столбцов.

Important
Обратите внимание

Алиасы (псевдонимы) заменяют оригинальное имя таблицы, представления или хранимой процедуры: как только определён алиас для соответствующего отношения, использовать оригинальное имя нельзя.

В начало списка полей могут быть добавлены ключевые слова DISTINCT или ALL:

  • DISTINCT удаляет дубликаты строк: то есть, если две или более записей содержат одинаковые значения во всех соответствующих полях, только одна из этих строк будет включена в результирующий набор данных.

  • ALL включает все строки в результирующий набор данных. ALL включено по умолчанию и поэтому редко используется: явное указание поддерживается для совместимости со стандартом SQL.

Выражение COLLATE не изменяет содержимое поля, однако, если указать COLLATE для определённого поля, то это может изменить чувствительность к регистру символов или к акцентам (accent sensitivity), что, в свою очередь, может повлиять на:

  • Порядок сортировки, в случае если это поле указано в выражении ORDER BY;

  • Группировку, в случае если это поле указано в выражении GROUP BY;

  • Количество возвращаемых строк, если используется DISTINCT.