Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 46: формирование и анализ иерархических структур
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода. Первый запрос возвратит результат в том виде, в котором это требуется по условию задачи (список идентификаторов, разделённых запятыми), второй запрос возвратит таблицу из одной колонки, где каждый идентификатор будет расположен в отдельной строке.
MS SQL Решение 7.1.1.a (пример использования функции)
1SELECT * FROM GET_ALL_CHILDREN(2, 'STRING');
2SELECT * FROM GET_ALL_CHILDREN(2, 'TABLE');
Итак, рассмотрим решение. Мы создали именно табличную функцию затем, чтобы иметь возможность возвращать данные не только в виде списка идентификаторов, перечисленных через запятую, но и в виде таблицы из одной колонки, строками которой являются идентификаторы.
Вслучае, когда нам всё же нужен список идентификаторов, перечисленных через запятую, мы по-прежнему возвращаем таблицу из одной колонки, но в ней будет ровно одна строка, содержащая весь наш список.
Воснове решения лежит т.н. рекурсивное общее табличное выражение40. Рассмотрим его отдельно.
MS SQL Решение 7.1.1.a (рекурсивное общее табличное выражение)
1WITH [tree] ([sp_id], [sp_parent])
2AS
3(
4SELECT [sp_id],
5[sp_parent]
6 |
|
FROM |
[site_pages] |
7 |
|
WHERE |
[sp_id] = {родительская_вершина} |
8UNION ALL
9SELECT [inner].[sp_id],
10[inner].[sp_parent]
11 |
|
FROM [site_pages] AS [inner] |
12JOIN [tree]
13ON [inner].[sp_parent] = [tree].[sp_id]
14)
15SELECT [sp_id]
16 |
|
FROM |
[tree] |
17 |
|
WHERE |
[sp_id] != {родительская_вершина} |
|
|
|
|
Рекурсивное общее табличное выражение должно содержать две части:
•в первой части (строки 4-7) происходит выборка родительской записи (для которой мы будем искать дочерние);
•во второй части (строки 8-13) происходит рекурсивное обращение к результату выполнения общего табличного выражения, что и позволяет нам получить всё поддерево заданной вершины.
Поскольку первая часть (до оператора UNION) является обязательной, а по
условию задачи идентификатор самой родительской вершины не должен попадать в выборку, мы исключаем попадание соответствующей записи в итоговую выборку условием в строке 17.
Полученное рекурсивно общее табличное выражение мы разместили в строках 10-27 кода функции, обеспечив вставку его результатов (строка 24) в результирующую таблицу, которую возвращает функция.
В строках 31-50 кода функции находится то же самое рекурсивное общее табличное выражение, но при выборке результатов его выполнения мы применяем приём, подробно описанный в решении{72} задачи 2.2.2.a{71} для эмуляции функции
GROUP_CONCAT MySQL.
40 https://msdn.microsoft.com/en-us/library/ms175972%28v=sql.110%29.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 480/545
Пример 46: формирование и анализ иерархических структур
Переходим к Oracle. Здесь решение будет практически идентичным решению для MS SQL Server. Единственное отличие — в способе возврата таблицы из функции (мы рассматривали этот вопрос ранее, см. решение{355} задачи 5.1.1.b{352}). И здесь мы также вынуждены эмулировать поведение функции MySQL GROUP_CONCAT через использование функции Oracle LISTAGG (см. решение{72} задачи
2.2.2.a{71}).
Oracle |
Решение 7.1.1.a (код функции) |
1CREATE TYPE "tree_node" AS OBJECT("id" VARCHAR(32767));
2/
3
4CREATE TYPE "nodes_collection" AS TABLE OF "tree_node";
5/
6 |
|
|
7 |
|
CREATE OR REPLACE FUNCTION GET_ALL_CHILDREN(parent_id NUMBER, |
8 |
|
function_mode VARCHAR) |
9RETURN "nodes_collection"
10AS
11result_collection "nodes_collection";
12BEGIN
13IF (function_mode = 'TABLE')
14THEN
15WITH "tree" ("sp_id", "sp_parent")
16AS
17(
18SELECT "sp_id",
19 |
|
|
"sp_parent" |
20 |
|
FROM |
"site_pages" |
21WHERE "sp_id" = parent_id
22UNION ALL
23SELECT "inner"."sp_id",
24 |
|
|
"inner"."sp_parent" |
|
25 |
|
FROM |
"site_pages" "inner" |
|
26 |
|
|
JOIN |
"tree" |
27 |
|
|
ON |
"inner"."sp_parent" = "tree"."sp_id" |
|
|
|
|
|
28)
29SELECT "tree_node"(TO_CHAR("sp_id"))
30BULK COLLECT INTO result_collection
31 FROM "tree"
32WHERE "sp_id" != parent_id;
33ELSE
34WITH "tree" ("sp_id", "sp_parent")
35AS
36(
37SELECT "sp_id",
38 |
|
|
"sp_parent" |
39 |
|
FROM |
"site_pages" |
40WHERE "sp_id" = parent_id
41UNION ALL
42SELECT "inner"."sp_id",
43 |
|
|
"inner"."sp_parent" |
|
44 |
|
FROM |
"site_pages" "inner" |
|
45 |
|
|
JOIN |
"tree" |
46 |
|
|
ON |
"inner"."sp_parent" = "tree"."sp_id" |
47)
48SELECT "tree_node"(LISTAGG(TO_CHAR("sp_id"), ',' )
49 |
|
|
WITHIN GROUP (ORDER BY "sp_id")) |
50 |
|
BULK |
COLLECT INTO result_collection |
51 |
|
FROM |
"tree" |
52WHERE "sp_id" != parent_id;
53END IF;
54RETURN result_collection;
55END;
56/
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 481/545
Пример 46: формирование и анализ иерархических структур
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода. Первый запрос возвратит результат в том виде, в котором это требуется по условию задачи (список идентификаторов, разделённых запятыми), второй запрос возвратит таблицу из одной колонки, где каждый идентификатор будет расположен в отдельной строке.
Oracle Решение 7.1.1.a (пример использования функции)
1SELECT *
2FROM TABLE(CAST(GET_ALL_CHILDREN(2, 'STRING') AS "nodes_collection"));
3
4SELECT *
5FROM TABLE(CAST(GET_ALL_CHILDREN(2, 'TABLE') AS "nodes_collection"));
На этом решение данной задачи завершено.
Решение 7.1.1.b{475}.
Легко заметить, что решение этой задачи основано на рассуждениях, представленных в решении{476} задачи 7.1.1.a{475}. Если допустить, что соответствующие функции у нас уже есть, код для всех трёх СУБД будет выглядеть так.
MySQL |
Решение 7.1.1.b (использованием функции GET_ALL_CHILDREN из решения 7.1.1.a) |
1SELECT `sp_id`,
2`sp_name`
|
3 |
|
FROM |
`site_pages` |
|
|
4 |
|
WHERE |
`sp_id` = {родительская_вершина} |
|
|
5 |
|
|
|
OR FIND_IN_SET(`sp_id`, GET_ALL_CHILDREN({родительская_вершина})) |
|
|
|
|
||
|
MS SQL |
|
Решение 7.1.1.b (использованием функции GET_ALL_CHILDREN из решения 7.1.1.a) |
||
1SELECT [sp_id],
2[sp_name]
3 FROM [site_pages]
4WHERE [sp_id] = {родительская_вершина}
5OR [sp_id] IN
|
6 |
|
|
(SELECT [id] |
|
7 |
|
|
FROM GET_ALL_CHILDREN({родительская_вершина}, 'TABLE')) |
|
|
|
|
|
|
Oracle |
|
Решение 7.1.1.b (использованием функции GET_ALL_CHILDREN из решения 7.1.1.a) |
|
1SELECT "sp_id",
2"sp_name"
3 FROM "site_pages"
4WHERE "sp_id" = {родительская_вершина}
5OR "sp_id" IN
6 |
|
(SELECT "id" |
7 |
|
FROM TABLE(CAST( |
8 |
|
GET_ALL_CHILDREN({родительская_вершина}, |
9 |
|
'TABLE') |
10 |
|
AS "nodes_collection"))) |
|
|
|
Если же предположить, что функции GET_ALL_CHILDREN у нас нет, решение можно построить следующим образом.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 482/545
Пример 46: формирование и анализ иерархических структур
В MySQL мы берём запрос, вокруг которого построена функция в решении{476} задачи 7.1.1.a{475}, и используем его напрямую как источник списка идентификаторов дочерних страниц.
MySQL Решение 7.1.1.b
1SELECT `sp_id`,
2`sp_name`
3 FROM `site_pages`
4WHERE `sp_id` = {родительская_вершина}
5OR FIND_IN_SET
6(`sp_id`,
7(SELECT GROUP_CONCAT(`children_ids`)
8 |
|
FROM |
(SELECT |
`sp_id`, |
9 |
|
|
@parent_values := |
|
10 |
|
|
( |
|
11 |
|
|
SELECT GROUP_CONCAT(`sp_id` SEPARATOR ',') |
|
12 |
|
|
FROM |
`site_pages` |
13 |
|
|
WHERE |
FIND_IN_SET(`sp_parent`, @parent_values) > 0 |
14 |
|
|
) AS `children_ids` |
|
15 |
|
|
FROM |
`site_pages` |
16 |
|
|
JOIN |
(SELECT @parent_values := {родительская_вершина}) |
17 |
|
|
|
AS `initialisation` |
18 |
|
|
WHERE |
`sp_id` IN (@parent_values) |
|
|
|
|
|
19) AS `prepared_data`)
20)
ВMS SQL Server мы берём рекурсивные общие табличные выражения, вокруг которых построены функции в решении{476} задачи 7.1.1.a{475}, и, добавив в выборку имя страницы (поле sp_name), используем полученный результат как источ-
ник требуемых данных. Здесь даже не придётся исключать из выборки саму родительскую вершину, т.к. по условию данной задачи она должна попадать в конечный набор данных.
Обратите внимание на тот факт, что MS SQL Server допускает создание рекурсивных общих табличных выражений без явного указания списка колонок, в то время как в Oracle этот список обязателен.
MS SQL Решение 7.1.1.b
1WITH [tree]
2AS
3(
4SELECT [sp_id],
5[sp_parent],
6 |
|
|
[sp_name] |
7 |
|
FROM |
[site_pages] |
8 |
|
WHERE |
[sp_id] = {родительская_вершина} |
9UNION ALL
10SELECT [inner].[sp_id],
11[inner].[sp_parent],
12 |
|
|
[inner].[sp_name] |
13 |
|
FROM |
[site_pages] AS [inner] |
14JOIN [tree]
15ON [inner].[sp_parent] = [tree].[sp_id]
16)
17SELECT [sp_id],
18[sp_name]
19FROM [tree]
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 483/545
Пример 46: формирование и анализ иерархических структур
Oracle Решение 7.1.1.b
1WITH "tree" ("sp_id", "sp_parent", "sp_name")
2AS
3(
4SELECT "sp_id",
5"sp_parent",
6"sp_name"
7 |
|
FROM |
"site_pages" |
8 |
|
WHERE |
"sp_id" = {родительская_вершина} |
|
|
|
|
9UNION ALL
10SELECT "inner"."sp_id",
11"inner"."sp_parent",
12"inner"."sp_name"
13 |
|
FROM "site_pages" "inner" |
14JOIN "tree"
15ON "inner"."sp_parent" = "tree"."sp_id"
16)
17SELECT "sp_id",
18"sp_name"
19FROM "tree"
Если же отойти от традиции, в рамках которой мы рассматриваем во всех трёх СУБД максимально похоже решения, то в Oracle данную задачу можно решить с помощью конструкции CONNECT BY.
Для наглядности выберем всю информацию о страницах, и даже добавим указание уровня, на котором каждая из страниц находится относительно исходной родительской, список подстраниц которой мы ищем.
Oracle Решение 7.1.1.b (альтернативный вариант)
1SELECT "sp_id",
2"sp_parent",
3"sp_name",
4LEVEL
5 FROM "site_pages"
6START WITH "sp_id" = {родительская_вершина}
7CONNECT BY PRIOR "sp_id" = "sp_parent";
Для страницы с идентификатором 2, этот запрос вернёт следующие данные.
sp_id |
sp_parent |
sp_name |
LEVEL |
2 |
1 |
Читателям |
1 |
5 |
2 |
Новости |
2 |
6 |
2 |
Статистика |
2 |
12 |
6 |
Текущая |
3 |
13 |
6 |
Архивная |
3 |
14 |
6 |
Неофициальная |
3 |
Но и это ещё не всё: функция SYS_CONNECT_BY_PATH позволяет для каждой вершины формировать полный путь, связывающий её с родительской (для которой строится список дочерних элементов).
Oracle Решение 7.1.1.b (альтернативный вариант)
1SELECT LPAD(' ', 2 * LEVEL, ' ') || "sp_name" "debug",
2"sp_id",
3"sp_parent",
4"sp_name",
5SYS_CONNECT_BY_PATH("sp_name", '/') "path_with_names",
6 |
|
SYS_CONNECT_BY_PATH("sp_id", ',') "path_with_ids" |
7 |
|
FROM "site_pages" |
8START WITH "sp_id" = {родительская_вершина}
9CONNECT BY PRIOR "sp_id" = "sp_parent"
10ORDER SIBLINGS BY "sp_name"
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 484/545