Иерархические и рекурсивные запросы в SQLИерархический запрос - это тип запроса SQL, который обрабатывает данные иерархической модели. Они являются частными случаями более общих рекурсивных запросов с фиксированной точкой, которые вычисляют транзитивные замыкания. В стандартном SQL: 1999 иерархические запросы реализуются с помощью рекурсивных общих табличных выражений (CTE). В отличие от более раннего предложения Oracle о подключении, рекурсивные CTE были спроектированы с семантикой фиксированной точки с самого начала. Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2[1]. Рекурсивные CTE также поддерживаются Microsoft SQL Server (начиная с SQL Server 2008 R2)[2], Firebird 2.1[3], PostgreSQL 8.4+[4], SQLite 3.8.3+[5], IBM Informix версии 11.50+, CUBRID и MySQL 8.0.1+[6]. Tableau и TIBCO Spotfire не поддерживают CTE, в то время как в реализации Oracle 11g Release 2 отсутствует семантика точек фиксации. Без общих табличных выражений или предложений присоединения можно выполнять иерархические запросы с помощью пользовательских рекурсивных функций.[7] Общее табличное выражениеОбщее табличное выражение, или CTE, (в SQL) - это временный именованный набор результатов, полученный из простого запроса и определенный в пределах области выполнения оператора CTE можно рассматривать как альтернативу производным таблицам (подзапросам), представлениям и встроенным пользовательским функциям. Общие табличные выражения поддерживаются Teradata, DB2, Firebird[8], Microsoft SQL Server, Oracle (с рекурсией начиная с версии 11g 11g), PostgreSQL (начиная с 8.4), MariaDB (начиная с 10.2), MySQL (начиная с 8.0), SQLite (начиная с 3.8.3), HyperSQL и H2 (экспериментальные)[9]. Oracle называет CTE «факторингом подзапроса».[10] Синтаксис для рекурсивного CTE следующий: WITH [RECURSIVE] with_query [, ...] SELECT... где синтаксис query_name [ (column_name [,...]) ] AS (SELECT ...) Рекурсивные CTE (или «рекурсивный факторинг подзапросов»[11] в жаргоне Oracle) могут использоваться для обхода отношений (в виде графиков или деревьев), хотя синтаксис гораздо более сложен, поскольку не создаются автоматические псевдостолбцы (как LEVEL ниже); если они желательны, они должны быть созданы в коде. См. Документацию MSDN[2] или документацию IBM[12] для учебных примеров. Ключевое слово В SQL: 1999 рекурсивный (CTE) запрос может появляться везде, где разрешен запрос. Например, можно назвать результат, используя Некоторые базы данных, такие как PostgreSQL, поддерживают более короткий формат CREATE RECURSIVE VIEW, который внутренне преобразуется в кодирование WITH RECURSIVE. [15] Примером рекурсивного запроса, вычисляющего факториал чисел от 0 до 9, является следующий: WITH RECURSIVE temp (n, fact) AS
(SELECT 0, 1 -- Initial Subquery
UNION ALL
SELECT n+1, (n+1)*fact FROM temp -- Recursive Subquery
WHERE n < 9)
SELECT * FROM temp;
CONNECT BYАльтернативный синтаксис - нестандартная конструкция
SELECT select_list
FROM table_expression
[ WHERE ... ]
[ START WITH start_expression ]
CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ...
[ GROUP BY ... ]
[ HAVING ... ]
...
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
Вывод вышеприведенного запроса будет выглядеть следующим образом: level | employee | empno | manager
-------+-------------+-------+---------
1 | KING | 7839 |
2 | JONES | 7566 | 7839
3 | SCOTT | 7788 | 7566
4 | ADAMS | 7876 | 7788
3 | FORD | 7902 | 7566
4 | SMITH | 7369 | 7902
2 | BLAKE | 7698 | 7839
3 | ALLEN | 7499 | 7698
3 | WARD | 7521 | 7698
3 | MARTIN | 7654 | 7698
3 | TURNER | 7844 | 7698
3 | JAMES | 7900 | 7698
2 | CLARK | 7782 | 7839
3 | MILLER | 7934 | 7782
(14 rows)
Псевдо-столбцы
Унарные операторыВ следующем примере возвращается фамилия каждого сотрудника в отделе 10, каждого менеджера выше этого сотрудника в иерархии, количества уровней между менеджером и сотрудником и пути между ними: SELECT ename "Employee", CONNECT_BY_ROOT ename "Manager", LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Path" FROM emp WHERE LEVEL > 1 and deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Employee", "Manager", "Pathlen", "Path"; Функции
См. такжеИспользованная литература
Примечания |
Portal di Ensiklopedia Dunia
