Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 46: формирование и анализ иерархических структур
GROUP_CONCAT MySQL.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 510/545
Пример 46: формирование и анализ иерархических структур
Переходим к Oracle. Здесь решение будет практически идентичным решению для MS SQL Server. Единственное отличие — в способе возврата таблицы из функции (мы рассматривали этот вопрос ранее, см. решение{355} задачи 5.1.1. b{352}). И здесь мы также вынуждены эмулировать поведение функции MySQL GROUP_CON- CAT через использование функции Oracle LISTAGG (см.
решение{72} задачи
2.2.2. a{7i}).
Oracle |
Решение 7.1.1.a (код функции) |
1CREATE TYPE "tree_node" AS OBJECT "id" VARCHAR132767));
2/ 3
4CREATE TYPE "nodes_collection" AS TABLE OF "tree_node"
5/ 6
|
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 Стр: 511/545
Пример 46: формирование и анализ иерархических структур
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 512/545
Пример 46: формирование и анализ иерархических структур
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода. Первый запрос возвратит результат в том виде, в котором это требуется по условию задачи (список идентификаторов, разделённых запятыми), второй запрос возвратит таблицу из одной колонки, где каждый идентификатор будет расположен в отдельной строке.
Oracle і Решение 7.1.1.a (пример использования функции) і
1SELECT . *
2FROM TABLE(CAST(GET_ALL_CHILDREN(2, 'STRING') AS "nodes_collection" );
4SELECT *
FROM TABLE(CAST(GET ALL CHILDRENS, 'TABLE') AS "nodes collection"));
На этом решение данной задачи завершено.
'IT Решение 7.1.1. b{475}.
Легко заметить, что решение этой задачи основано на рассуждениях, представленных в решении{476} задачи 7.1.1.a{475}. Если допустить, что соответствующие функции у нас уже есть, код для всех трёх СУБД будет выглядеть так.
MySQL і |
Решение 7.1.1.b (использованием функции GET_ALL_CHILDREN из решения 7.1.1.а) |
і |
1SELECT . 'sp_id',
2'sp_name'
3 |
FROM |
'site_pages' |
4 |
WHERE |
'sp_id' = {родительская_вершина} |
|
|
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] |
4 |
WHERE |
[sp_id] = {родительская_вершина} |
5 |
|
OR [sp id] IN |
6 |
|
(SELECT [id] |
7 |
|
FROM GET ALL CHILDREN({родительская _вершина}, 'TABLE')) |
|
|
|
Oracle |
|
Решение 7.1.1.b (использованием функции GET_ALL_CHILDREN из решения 7.1.1 |
1SELECT.a)"sp id",
2"sp name"
3 |
FROM |
"site_pages" |
4 |
WHERE |
"sp_id" = {родительская_вершина} |
5 |
|
OR "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 Стр: 513/545
Пример 46: формирование и анализ иерархических структур
В MySQL мы берём запрос, вокруг которого построена функция в решении{476} задачи 7.1.1.a{475}, и используем его напрямую как источник списка идентификаторов дочерних страниц.
MySQL і Решение 7.1.1.b
1SELECT 'sp id',
2'sp name'
3 |
FROM |
'site_pages' |
4 |
WHERE |
'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]
16)
17SELECT [sp_id],
18[sp_name]
19FROM [tree]
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 514/545