Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 46: формирование и анализ иерархических структур

Для страницы с идентификатором 2, этот запрос вернёт следующие данные.

На этом решение данной задачи завершено.

Решение 7.1.1.c{475}.

В MySQL нет рекурсивных запросов, нет возможности вернуть из функции таблицу, нет общих табличных выражений — остаётся использовать алгоритм с циклом, в котором на каждом шаге мы поднимаемся на один уровень иерархии вверх, пока не достигнем корневой вершины (ссылка на родителя равна NULL).

Здесь мы последовательно подменяем (строки 12-15) значение идентификатора текущего узла и накапливаем (строки 16-19) все полученные значения в строковой переменной, которая и является результатом работы функции.

Обратите внимание, что MySQL допускает указание RETURN прямо в объявлении обработчика ситуации «запрос вернул пустой результат» (строка 7), и таким образом нет необходимости явно делать RETURN далее в коде функции.

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

9SET current_node = start_node;

10SET path_to_root = start_node;

11LOOP

12SELECT `sp_parent`

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(14)

На этом решение для MySQL завершено.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 485/545

Пример 46: формирование и анализ иерархических структур

Переходим к MS SQL Server. Поскольку в данной СУБД есть возможность возвратить из функции результат в виде таблицы, мы реализуем оба варианта поведения — и возврат таблицы, и возврат строки.

Встроках 9-17 мы реализуем такой же алгоритм, как и в решении для MySQL, но все рассмотренные значения идентификаторов вершин не накапливаем в строку, а помещаем в результирующую таблицу.

Встроках 19-28 мы проверяем необходимость вернуть результат в виде набора идентификаторов, разделённых запятыми и, если такая необходимость есть, собираем все данные из результирующей таблицы в строку (строки 21-24 кода), очищаем результирующую таблицу (строка 25 кода) и помещаем в неё полученную строку (строки 26-27 кода).

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);

19IF (@mode = 'STRING')

20BEGIN

21SET @all_as_string = (SELECT STUFF((SELECT ',' + CAST([id] AS VARCHAR)

25DELETE FROM @path;

26INSERT INTO @path

27SELECT @all_as_string;

28END;

29

30RETURN

31END;

Проверить работоспособность и корректность полученного решения можно следующими запросами, первый из которых вернёт колонку идентификаторов, а второй — таблицу из одной ячейки, в которой будет строка с идентификаторами, перечисленными через запятую.

MS SQL Решение 7.1.1.c (пример использования функции)

1SELECT * FROM GET_PATH_TO_ROOT(14, 'TABLE');

2SELECT * FROM GET_PATH_TO_ROOT(14, 'STRING');

Поскольку MS SQL Server поддерживает рекурсивные общие табличные выражения, эту задачу можно решить также с их помощью. Для краткости мы не будем создавать отдельную функцию, но приведём запрос, который даже сам по себе возвращает именно такой результат, какой требуется по условию задачи.

JOIN в строке 11 как раз и обеспечивает необходимое нам рекурсивное по-

ведение.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 486/545

Пример 46: формирование и анализ иерархических структур

MS SQL Решение 7.1.1.c (альтернативный вариант)

1WITH [path_to_root] AS

2(

3SELECT [sp_id],

4[sp_parent]

7UNION ALL

8SELECT [inner].[sp_id],

9[inner].[sp_parent]

11JOIN [path_to_root] ON [path_to_root].[sp_parent] = [inner].[sp_id]

12)

13SELECT [sp_id] FROM [path_to_root]

На этом решение для MS SQL Server завершено.

Переходим к Oracle и реализуем решение по аналогии с MS SQL Server.

1CREATE TYPE "ptr_tree_node" AS OBJECT("id" VARCHAR(32767));

2/

3

4CREATE TYPE "ptr_nodes_collection" AS TABLE OF "ptr_tree_node";

5/

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) :=

24ELSE

25all_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_string))

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 Стр: 487/545

Пример 46: формирование и анализ иерархических структур

Поскольку в Oracle нельзя присваивать новое значение входному параметру функции, мы используем временную переменную (строки 13 и 15) для хранения значений идентификаторов вершин и использования в цикле.

Вотличие от MS SQL Server, где внутри функции у нас есть полноценная таблица для хранения данных, в Oracle мы используем коллекцию, потому для упрощения кода мы сразу в теле цикла проверяем, придётся ли нам возвратить колонку идентификаторов (тогда мы добавляем их в коллекцию) или строку с их перечислением (тогда мы накапливаем их в строковой переменной).

Встроках 34-39 мы снова проверяем режим работы функции и помещаем строку с перечислением идентификаторов (из которой в строке 32 убираем первую лишнюю запятую) в коллекцию (которая сейчас пуста, если функция вызвана в режиме возврата перечисления идентификаторов).

Проверить работоспособность и корректность полученного решения можно следующими запросами, первый из которых вернёт колонку идентификаторов, а второй — таблицу из одной ячейки, в которой будет строка с идентификаторами, перечисленными через запятую.

Oracle Решение 7.1.1.c (пример использования функции)

1SELECT *

2FROM TABLE(CAST(GET_PATH_TO_ROOT(14, 'TABLE') AS "ptr_nodes_collection"));

3

4SELECT *

5FROM TABLE(CAST(GET_PATH_TO_ROOT(14, 'STRING') AS "ptr_nodes_collection"));

Поскольку Oracle (как и MS SQL Server) поддерживает рекурсивные общие табличные выражения, эту задачу можно решить также с их помощью. Для краткости мы не будем создавать отдельную функцию, но приведём запрос, который даже сам по себе возвращает именно такой результат, какой требуется по условию задачи.

Oracle Решение 7.1.1.c (альтернативный вариант)

1WITH "path_to_root" ("sp_id", "sp_parent") AS

2(

3SELECT "sp_id",

4"sp_parent"

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_CONNECT_BY_PATH очень легко получается решение с представлением результата в виде строки идентификаторов.

Oracle Решение 7.1.1.c (альтернативный вариант)

1 SELECT SUBSTR(SYS_CONNECT_BY_PATH("sp_id", ','), 2) "path_with_ids"

2 FROM "site_pages"

3WHERE "sp_id" = {исходная_вершина}

4START WITH "sp_id" = (SELECT "sp_id"

7CONNECT BY PRIOR "sp_id" = "sp_parent"

8ORDER SIBLINGS BY "sp_name"

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 488/545

Пример 46: формирование и анализ иерархических структур

Единственная особенность заключается в последовательности размещения идентификаторов: во всех ранее рассмотренных вариантах корневая вершина находилась справа (например, для вершины 14 последовательность была 14,6,2,1), а здесь корневая вершина будет находиться слева (т.е. получится 1,2,6,14).

На этом решение данной задачи завершено.

Задание 7.1.1.TSK.A: создать функцию, возвращающую список идентификаторов всех дочерних вершин заданной вершины (например, идентификаторов всех подстраниц страницы «Читателям») на глубину, не более заданной.

Задание 7.1.1.TSK.B: написать запрос для показа всего поддерева заданной вершины дерева, включая саму родительскую вершину (например, всех подстраниц страницы «Читателям», включая саму эту страницу), в котором с каждого уровня иерархии в выборку попадает не более одной вершины.

Задание 7.1.1.TSK.C: написать функцию, возвращающую список идентификаторов вершин на пути от корня дерева к заданной вершине (например, идентификаторов всех вершин на пути от страницы «Главная» к странице «Архивная»).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 489/545