Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 46: формирование и анализ иерархических структур
Oracle I Решение 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" |
14 |
|
JOIN "tree" |
15 |
|
ON "inner" "sp_parent" = "tree" "sp_id" |
16)
17SELECT "sp id",
18"sp name"
19FROM "tree"
Если же отойти от традиции, в рамках которой мы рассматриваем во всех трёх СУБД максимально похоже решения, то в Oracle данную задачу можно решить с помощью конструкции CONNECT BY.
Для наглядности выберем всю информацию о страницах, и даже добавим указание уровня, на котором каждая из страниц находится относительно исходной родительской, список подстраниц которой мы ищем.
|
Oracl |
і |
Решение 7.1.1.b (альтернативный вариант) |
| |
e |
|
|||
|
|
|
|
|
1 |
|
SELECT "sp id", |
|
|
2 |
|
|
"sp_parent", |
|
3"sp_name",
4LEVEL
5 |
FROM |
"si te_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 позволяет для каждой
вершины формировать полный путь, связывающий её с родительской (для которой строится список дочерних элементов).
|
Oracl |
і |
Решение 7.1.1.b (альтернативный вариант) |
| |
e |
|
|||
|
|
|
|
|
1 |
|
SELECT 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 |
"si te_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 Стр: 515/545
Пример 46: формирование и анализ иерархических структур
Для страницы с идентификатором 2, этот запрос вернёт следующие данные.
debug |
sp_id |
sp_par- |
sp_name |
path_with_names |
path_with_ids |
|
|
ent |
|
|
|
Читателям |
2 |
1 |
Читателям |
/Читателям |
,2 |
Новости |
5 |
2 |
Новости |
/Читателям/Новости |
,2,5 |
|
6 |
2 |
Статистика |
/Читателям/Статистика |
,2,6 |
Статистика |
|
|
|
|
|
Архивная |
13 |
6 |
Архивная |
/Читателям/Статистика/Ар- |
,2,6,13 |
|
|
|
|
хивная |
|
Неофи- |
14 |
6 |
Неофици- |
/Читателям/Статистика/Не- |
,2,6,14 |
циальная |
|
|
альная |
официальная |
|
Текущая |
12 |
6 |
Текущая |
/Читателям/Статистика/Теку- |
,2,6,12 |
|
|
|
|
щая |
|
На этом решение данной задачи завершено.
ЧР’ Решение 7.1.1. С{475}.
В MySQL нет рекурсивных запросов, нет возможности вернуть из функции таблицу, нет общих табличных выражений — остаётся использовать алгоритм с циклом, в котором на каждом шаге мы поднимаемся на один уровень иерархии вверх, пока не достигнем корневой вершины (ссылка на родителя равна NULL).
Здесь мы последовательно подменяем (строки 12-15) значение идентификатора текущего узла и накапливаем (строки 16-19) все полученные значения в строковой переменной, которая и является результатом работы функции.
Обратите внимание, что MySQL допускает указание RETURN прямо в объявлении обработчика ситуации «запрос вернул пустой результат» (строка 7), и таким образом нет необходимости явно делать RETURN далее в коде функции.
MySQL |
Решение 7.1.1.С (код |
1DELIMITERфункции$$ )
2CREATE FUNCTION GET PATH TO ROOT start node INT) RETURNS TEXT
3NOT DETERMINISTIC
4BEGIN
5DECLARE path to root TEXT;
6DECLARE current node INT;
7DECLARE EXIT HANDLER FOR NOT FOUND RETURN path_to_root;
8 |
|
|
9 |
SET current node = start node |
|
10 |
SET path to root = start node |
|
11 |
LOOP |
|
12 |
SELECT |
'sp_parent' |
13 |
INTO |
current node |
14 |
FROM |
'site_pages' |
15 |
WHERE |
'sp id' = current node; |
16IF (current node IS NOT NULL)
17THEN
18SET path to root = CONCAT(path to root ',', current node ;
19END IF;
20END LOOP;
21END$$
22DELIMITER ;
Использовать полученную функцию можно так.
MySQL Решение 7.1.1.C (пример использования функции)
1 SELECT GET_PATH_TO_ROOT(141
На этом решение для MySQL завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 516/545
Пример 46: формирование и анализ иерархических структур
Переходим к MS SQL Server. Поскольку в данной СУБД есть возможность возвратить из функции результат в виде таблицы, мы реализуем оба варианта поведения — и возврат таблицы, и возврат строки.
Встроках 9-17 мы реализуем такой же алгоритм, как и в решении для MySQL, но все рассмотренные значения идентификаторов вершин не накапливаем в строку,
апомещаем в результирующую таблицу.
Встроках 19-28 мы проверяем необходимость вернуть результат в виде набора идентификаторов, разделённых запятыми и, если такая необходимость есть, собираем все данные из результирующей таблицы в строку (строки 21-24 кода), очищаем результирующую таблицу (строка 25 кода) и помещаем в неё полученную строку (строки 26-27 кода).
MS SQL і |
Решение 7.1.1.С (код функции) |
1CREATE FUNCTION GET PATH TO ROOT @current node INT, @mode VARCHAR(50 )
2RETURNS @path TABLE
3(
4id VARCHAR(max)
5)
6AS
7BEGIN
8DECLARE @all as string VARCHAR(max) = '';
9WHILE (@current node IS NOT NULL)
10BEGIN
11INSERT INTO @path
12SELECT CAST @current node AS VARCHAR);
13 |
|
|
14 |
SET @current_node = (SELECT [sp_parent] |
|
15 |
FROM |
[site_pages] |
16 |
WHERE |
[sp id] = @current node ; |
17 |
END; |
|
18 |
|
|
19IF @mode = 'STRING')
20BEGIN
21SET @all as string = (SELECT STUFF((SELECT ',' + CAST([id] AS VARCHAR)
22 |
FROM |
@path |
23 |
FOR XML PATH(''), TYPE) value ('.', 'nvarchar(max) '), |
|
24 |
1, 1, |
'')); |
25DELETE FROM @path;
26INSERT INTO @path
27SELECT @all as string
28END;
29
30RETURN
31END;
Проверить работоспособность и корректность полученного решения можно следующими запросами, первый из которых вернёт колонку идентификаторов, а второй — таблицу из одной ячейки, в которой будет строка с идентификаторами, перечисленными через запятую.
MS SQL Решение 7.1.1.С (пример использования функции)
1 SELECT * FROM GET_PATH_TO_ROOT 14 'TABLE')
2 SELECT * FROM GET PATH TO
ROOT 14 'STRING');
Поскольку MS SQL Server поддерживает рекурсивные общие табличные выражения, эту задачу можно решить также с их помощью. Для краткости мы не будем создавать отдельную функцию, но приведём запрос, который даже сам по себе возвращает именно такой результат, какой требуется по условию задачи.
JOIN в строке 11 как раз и обеспечивает необходимое нам рекурсивное поведение.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 517/545
Пример 46: формирование и анализ иерархических структур
MS SQL I |
Решение 7.1.1.c (альтернативный вариант) |
| |
||
1 |
WITH [path_to_root] AS |
|
||
2 |
( |
|
|
|
3 |
|
SELECT [sp id], |
|
|
4 |
|
|
[sp_parent] |
|
5 |
|
FROM |
[site_pages] |
|
6 |
|
WHERE |
[sp_id] = {исходная_вершина} |
|
7 |
|
UNION ALL |
|
|
8 |
|
SELECT [inner] [sp id], |
|
|
9 |
|
|
[inner] [sp_parent] |
|
10 |
|
FROM |
[site_pages] AS [inner] |
|
11 |
|
JOIN [path to root] ON [path to root] [sp_parent] = [inner] [sp_id] |
||
12 |
) |
|
|
|
13 |
SELECT [sp__id] FROM [path to root] |
|||
|
|
|
|
|
На этом решение для MS SQL Server завершено.
Переходим к Oracle и реализуем решение по аналогии с MS SQL Server.
Oracle і |
Решение 7.1.1.С (код функции) |
і |
1CREATE TYPE "рЪг_Ьгёё“поаё" AS OBJECT ("id" VARCHAR (32767) ) ;
2/ 3
4CREATE TYPE "ptr_nodes_collection" AS TABLE OF "ptr_tree_node";
5/ 6
CREATE OR REPLACE FUNCTION GET_PATH_TO_ROOT start_id NUMBER,
8 |
function_mode VARCHAR) |
9RETURN "ptr_nodes_collection"
10AS
11result_collection "ptr_nodes_collection" := "ptr_nodes_collection" );
12all_as_string VARCHAR 32767 ;
13temp_int_value NUMBER 10 ;
14BEGIN
15temp_int_value := start_id; 16
17WHILE temp_int_value IS NOT NULL)
18LOOP
19IF (function_mode = 'TABLE')
20THEN
21result_collection extend
22result_collection result_collection last :=
23 |
"ptr_tree_node" (TO_CHAR(temp_int_value ); |
24 |
ELSE |
25 |
all_as_string := all_as_string || ',' || temp_int_value |
26END IF;
27SELECT "sp_parent" INTO temp_int_value
28FROM "site_pages"
29WHERE "sp_id" = temp_int_value
30END LOOP; 31
32 all_as_string := SUBSTR(all_as_string, 2); 33
34IF (function_mode != 'TABLE')
35THEN
36SELECT "ptr_tree_node" TO_CHAR all_as_stringl)
37BULK COLLECT INTO result_collection
38FROM DUAL;
39END IF; 40
41RETURN result_collection
42END;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 518/545
Пример 46: формирование и анализ иерархических структур
Поскольку в Oracle нельзя присваивать новое значение входному параметру функции, мы используем временную переменную (строки 13 и 15) для хранения значений идентификаторов вершин и использования в цикле.
Вотличие от MS SQL Server, где внутри функции у нас есть полноценная таблица для хранения данных, в Oracle мы используем коллекцию, потому для упрощения кода мы сразу в теле цикла проверяем, придётся ли нам возвратить колонку идентификаторов (тогда мы добавляем их в коллекцию) или строку с их перечислением (тогда мы накапливаем их в строковой переменной).
Встроках 34-39 мы снова проверяем режим работы функции и помещаем строку с перечислением идентификаторов (из которой в строке 32 убираем первую лишнюю запятую) в коллекцию (которая сейчас пуста, если функция вызвана в режиме возврата перечисления идентификаторов).
Проверить работоспособность и корректность полученного решения можно следующими запросами, первый из которых вернёт колонку идентификаторов, а второй — таблицу из одной ячейки, в которой будет строка с идентификаторами, перечисленными через запятую.
Oracle |
і Решение 7.1.1.С (пример использования функции) SELECT |
* |
1 |
2 |
FROM |
|
TABLE(CAST(GET_PATH_TO_ROOT(14, 'TABLE') AS |
"ptr_nodes_collection" ); |
3 |
|
|
4 |
SELECT * |
|
|
FROM TABLE(CAST(GET PATH TO ROOT(14, 'STRING') AS "ptr nodes collection" ); |
|
Поскольку Oracle (как и MS SQL Server) поддерживает рекурсивные общие табличные выражения, эту задачу можно решить также с их помощью. Для краткости мы не будем создавать отдельную функцию, но приведём запрос, который даже сам по себе возвращает именно такой результат, какой требуется по условию задачи.
|
Oracl |
і |
Решение 7.1.1.c (альтернативный вариант) |
| |
|
|
e |
|
|
||||
|
|
|
|
|
|
|
1 |
|
WITH "path_to_root" |
"sp_id", "sp_parent" |
AS |
||
2 |
|
( |
|
|
|
|
3 |
|
SELECT "sp id" |
|
|
|
|
4 |
|
"sp_parent" |
5 |
FROM |
"site_pages" |
6 |
WHERE |
"sp_id" = {исходная_вершина} |
7UNION ALL
8SELECT "inner" "sp id"
9"inner" "sp_parent"
10FROM "site_pages" "inner"
11JOIN "path to root" ON "path to root" "sp_parent" = "inner" "sp id"
12)
13SELECT "sp_ _id" FROM "path _to_ root"
Как и в решении задачи 7.1.1. b, мы рассмотрим ещё один вариант, доступный только в Oracle. На основе выражения CONNECT BY и функции SYS_CON- NECT_BY_PATH очень легко получается решение с представлением результата в виде строки идентификаторов.
|
Oracl |
і |
Решение 7.1.1.c (альтернативный вариант) |
| |
|
||
e |
|
|
|||||
|
|
|
|
|
|
|
|
1 |
|
SELECT SUBSTR(SYS CONNECT BY PATH "sp id", ','), 2 |
"path with ids" |
||||
2 |
|
FROM |
"si te_pages" |
|
|
|
|
3 |
|
WHERE |
"sp id" = {исходная вершина} |
|
|
||
4 |
|
START WITH "sp id" = (SELECT "sp id" |
|
|
|||
5 |
|
|
FROM |
"site_pages" |
|
||
6 |
|
|
WHERE |
"sp_parent" IS NULL) |
|
||
7 |
|
CONNECT BY PRIOR "sp_id" = "sp_parent" |
|
||||
8 |
|
ORDER SIBLINGS BY "sp_name" |
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 519/545