Занятие 2. Создание данных в иерархической таблице и управление ими

Статья
04/26/2023

Применяется к:SQL Server Azure SQL Database Azure, управляемому экземпляру SQL Azure

На занятии 1 существующая таблица была изменена так, чтобы в ней использовался тип данных hierarchyid . Кроме того, столбец hierarchyid был заполнен представлением существующих данных. На этом занятии будет создана новая таблица и вставлены данные с помощью иерархических методов. Затем с помощью иерархических методов будет выполнен запрос данных и показано управление данными.

Предварительные условия

Для работы с этим учебником требуется среда SQL Server Management Studio, доступ к серверу SQL Server и база данных AdventureWorks.

Установите SQL Server Management Studio.
Установите выпуск SQL Server 2017 Developer Edition.
Скачайте примеры баз данных AdventureWorks.

См. инструкции по восстановлению резервной копии базы данных с помощью SSMS.

Создание таблицы с помощью типа данных hierarchyid

В следующем примере создается таблица EmployeeOrg, включающая данные о сотрудниках и их иерархическом подчинении. В этом примере в базе данных AdventureWorks2022 создается таблица (что не обязательно). Для простоты эта таблица содержит только 5 столбцов.

OrgNode — это столбец типа hierarchyid , в котором хранятся иерархические связи.
OrgLevel — это вычисляемый столбец, основанный на столбце OrgNode, в котором хранятся данные об уровне каждого узла в иерархии. Эти данные будут использоваться для создания индекса по ширине.
Столбец EmployeeID содержит типичные идентификационные номера сотрудников, которые используются для таких задач, как расчет заработной платы. Новые приложения могут использовать столбец OrgNode, и этот отдельный столбец EmployeeID не требуется.
Столбец EmpName содержит имя сотрудника.
Столбец Title содержит должность сотрудника.

Создание таблицы EmployeeOrg

В окне редактора запросов выполните следующий программный код, чтобы создать таблицу EmployeeOrg . Если задать столбец OrgNode в качестве первичного ключа кластеризованного индекса, создается индекс по глубине:

USE AdventureWorks2022;  
GO  

if OBJECT_ID('HumanResources.EmployeeOrg') is not null
 drop table HumanResources.EmployeeOrg 

CREATE TABLE HumanResources.EmployeeOrg  
(  
   OrgNode hierarchyid PRIMARY KEY CLUSTERED,  
   OrgLevel AS OrgNode.GetLevel(),  
   EmployeeID int UNIQUE NOT NULL,  
   EmpName varchar(20) NOT NULL,  
   Title varchar(20) NULL  
) ;  
GO

Чтобы создать составной индекс по столбцам OrgLevel и OrgNode для эффективного поиска в ширину, выполните следующий код:
```
CREATE UNIQUE INDEX EmployeeOrgNc1   
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;  
GO  
```

Таблица готова для записи данных. В результате выполнения следующего задания таблица будет заполнена данными с применением иерархических методов.

Заполнение иерархической таблицы с помощью иерархических методов

AdventureWorks2022 имеет 8 сотрудников, работающих в отделе маркетинга. Иерархический список сотрудников выглядит следующим образом.

Дэвид, EmployeeID 6, начальник отдела маркетинга. В подчинении у Дэвиданаходятся три специалиста по маркетингу:

Сара, EmployeeID 46
Джон, EmployeeID 271
Джил, EmployeeID 119

Маркетолог Ванида (EmployeeID 269), подчиняется Саре, а маркетолог Мэри (EmployeeID 272) подчиняется Джону.

Вставка корневого элемента иерархического дерева

В следующем примере запись Дэвид (начальник отдела маркетинга) вставляется таблицу в качестве корневого элемента иерархии. OrdLevel — вычисляемый столбец. Следовательно, он не является частью инструкции INSERT. Для вставки первой записи в вершину иерархии используется метод GetRoot() .
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)  
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ;  
GO  
```

Для просмотра начальной строки таблицы используйте следующий код:

SELECT OrgNode.ToString() AS Text_OrgNode,   
OrgNode, OrgLevel, EmployeeID, EmpName, Title   
FROM HumanResources.EmployeeOrg ;

Результирующий набор:

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title  
------------ ------- -------- ---------- ------- -----------------  
/            Ox      0        6          David   Marketing Manager

Как и на предыдущем занятии, для преобразования данных типа ToString() hierarchyid в более наглядный формат используется метод .

Вставка записи подчиненного

Сара подчиняется Дэвиду. Чтобы вставить в иерархию узел Сара , необходимо создать соответствующее значение OrgNode с типом данных hierarchyid. Следующий код создает переменную типа hierarchyid и присваивает ей корневое значение OrgNode таблицы. Затем эта переменная используется методом GetDescendant() для вставки строки, представляющей подчиненный узел. GetDescendant принимает два аргумента. Вот список вариантов с разными значениями аргументов.
- Если родительская запись — NULL, GetDescendant возвращает значение NULL.
- Если родительская запись — не NULL, а потомки child1 и child2 — NULL, метод GetDescendant возвращает одного потомка данного родителя.
- Если родительская запись и потомок child1 — не NULL, а потомок child2 равен NULL, метод GetDescendant возвращает значение потомка родителя, который больше чем child1.
- Если родитель и потомок child2 — не NULL, а потомок child1 равен NULL, метод GetDescendant возвращает значение потомка родителя, который меньше child2.
- Если родитель, child1 и child2 не равны NULL, метод GetDescendant возвращает потомка родителя, который больше child1 и меньше child2.
В следующем примере в качестве аргументов корневого родительского элемента используются значения (NULL, NULL) , так как таблица пока не содержит никаких строк, кроме корневой. Чтобы вставить запись Сара, выполните следующий код:
```
DECLARE @Manager hierarchyid   
SELECT @Manager = hierarchyid::GetRoot()  
FROM HumanResources.EmployeeOrg ;  

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)  
VALUES  
(@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist') ;  
```

Чтобы обратиться к таблице и просмотреть вставленные записи, повторите запрос из первой процедуры:

SELECT OrgNode.ToString() AS Text_OrgNode,   
OrgNode, OrgLevel, EmployeeID, EmpName, Title   
FROM HumanResources.EmployeeOrg ;

Результирующий набор:

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title  
------------ ------- -------- ---------- ------- -----------------  
/            Ox      0        6          David   Marketing Manager  
/1/          0x58    1        46         Sariya  Marketing Specialist

Создание процедуры ввода новых узлов

Для упрощения ввода данных можно создать следующую хранимую процедуру, вставляющую данные о сотрудниках в таблицу EmployeeOrg . Входные данные процедуры — это данные о добавляемом сотруднике. Это EmployeeID руководителя нового сотрудника, EmployeeID самого сотрудника, а также их имена и должности. В процедуре используются методы GetDescendant() и GetAncestor() . Чтобы создать процедуру, выполните следующий код:
```
CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20))   
AS   
BEGIN  
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid  
   SELECT @mOrgNode = OrgNode   
   FROM HumanResources.EmployeeOrg   
   WHERE EmployeeID = @mgrid  
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
   BEGIN TRANSACTION  
      SELECT @lc = max(OrgNode)   
      FROM HumanResources.EmployeeOrg   
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;  

      INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)  
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)  
   COMMIT  
END ;  
GO  
```

В следующем примере вставляются записи об остальных 4 сотрудниках, прямо или косвенно подчиняющихся Дэвиду.

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist' ;  
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist' ;  
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant' ;  
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant' ;

Для просмотра строк таблицы EmployeeOrg выполните следующий запрос:

SELECT OrgNode.ToString() AS Text_OrgNode,   
OrgNode, OrgLevel, EmployeeID, EmpName, Title   
FROM HumanResources.EmployeeOrg ;  
GO

Результирующий набор:

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title  
------------ ------- -------- ---------- ------- -----------------  
/            Ox      0        6          David   Marketing Manager  
/1/          0x58    1        46         Sariya  Marketing Specialist  
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant  
/2/          0x68    1        271        John    Marketing Specialist  
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant  
/3/          0x78    1        119        Jill    Marketing Specialist

Теперь таблица полностью заполнена записями о сотрудниках отдела маркетинга.

Создание запросов к иерархической таблице с помощью иерархических методов

После того как таблица HumanResources.EmployeeOrg будет заполнена, эта задача продемонстрирует, как можно проводить запросы к иерархии с помощью некоторых иерархических методов.

Поиск подчиненных узлов

Sariya имеет одного подчиненного. Чтобы запросить подчиненных Sariya, выполните следующий запрос, в котором используется метод IsDescendantOf :
```
DECLARE @CurrentEmployee hierarchyid  

SELECT @CurrentEmployee = OrgNode  
FROM HumanResources.EmployeeOrg  
WHERE EmployeeID = 46 ;  

SELECT *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1 ;  
```
Результатами будут Sariya и Wanida. Sariya перечисляется, поскольку она является потомком на нулевом уровне. Wanida является потомком на первом уровне.
Запросить эту информацию можно также с помощью метода GetAncestor . GetAncestor принимает аргумент уровня, попытка вернуть который выполняется. Поскольку Wanida находится одним уровнем ниже Sariya, следует использовать метод GetAncestor(1) так, как показано в следующем коде:
```
DECLARE @CurrentEmployee hierarchyid  

SELECT @CurrentEmployee = OrgNode  
FROM HumanResources.EmployeeOrg  
WHERE EmployeeID = 46 ;  

SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee  
```
В этот раз результатом будет только Wanida.
Теперь измените значение @CurrentEmployee на David (EmployeeID 6), а уровень на 2. Выполнение следующей инструкции также вернет значение Wanida:
```
DECLARE @CurrentEmployee hierarchyid  

SELECT @CurrentEmployee = OrgNode  
FROM HumanResources.EmployeeOrg  
WHERE EmployeeID = 6 ;  

SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee  
```
В этот раз в результате также будет возвращена Mary, являющаяся подчиненной David, и находящаяся на два уровня ниже.

Использование методов GetRoot и GetLevel

По мере роста иерархии становится труднее определять положение в ней ее членов. Можно использовать метод GetLevel для определения того, как глубоко по уровням находится каждая строка в иерархии. Выполните следующий код, чтобы увидеть уровни всех строк:
```
SELECT OrgNode.ToString() AS Text_OrgNode,   
OrgNode.GetLevel() AS EmpLevel, *  
FROM HumanResources.EmployeeOrg ;  
GO  
```
Используйте метод GetRoot для нахождения корневого узла в иерархии. Следующий код возвращает единственную строку, являющуюся корневым узлом:
```
SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode = hierarchyid::GetRoot() ;  
GO  
```

Переупорядочение данных в иерархической таблице с помощью иерархических методов

Применимо к:SQL Server

Реорганизация иерархии — это распространенная задача обслуживания. В этой задаче в первую очередь переместим одну строку в новую позицию в иерархии с помощью инструкции UPDATE с методом GetReparentedValue . Затем переместим все поддерево в новое место.

Метод GetReparentedValue имеет два аргумента. В первом аргументе описывается та часть иерархии, которая будет изменена. Например, если в иерархии /1/4/2/3/ необходимо изменить сегмент /1/4/ так, чтобы в результате получилась иерархия /2/1/2/3/, в которой последние два узла (2/3/) остались бы без изменений, необходимо указать в качестве первого аргумента изменяемые узлы (/1/4/). Второй аргумент предоставляет новый уровень иерархии; для этого примера это /2/1/. Эти два аргумента не обязаны содержать одинаковое число уровней.

Перемещение одной строки на новое место в иерархии

В настоящий момент Wanida является подчиненной Sariya. В этой процедуре переместим Wanida из ее текущего узла /1/1/ в другой так, чтобы она стала подчиненной Jill. Ее новым узлом будет узел /3/1/ , следовательно, первым аргументом будет /1/ , а вторым — /3/ . Эти значения соответствуют значениям Sariya и Jill в столбце OrgNode . Исполните следующий код, чтобы переместить Wanida из организации Sariya в организацию Jill:
```
DECLARE @CurrentEmployee hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid  
SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeOrg  
  WHERE EmployeeID = 269 ;   
SELECT @OldParent = OrgNode FROM HumanResources.EmployeeOrg  
  WHERE EmployeeID = 46 ;   
SELECT @NewParent = OrgNode FROM HumanResources.EmployeeOrg  
  WHERE EmployeeID = 119 ;   

UPDATE HumanResources.EmployeeOrg  
SET OrgNode = @CurrentEmployee. GetReparentedValue(@OldParent, @NewParent)   
WHERE OrgNode = @CurrentEmployee ;  
GO  
```
Чтобы просмотреть результат, выполните следующий код:
```
SELECT OrgNode.ToString() AS Text_OrgNode,   
OrgNode, OrgLevel, EmployeeID, EmpName, Title   
FROM HumanResources.EmployeeOrg ;  
GO  
```
Теперь Wanida находится в узле /3/1/.

Реорганизация раздела иерархии

Чтобы продемонстрировать, как можно одновременно переместить большее количество людей, сначала необходимо выполнить следующий код, чтобы добавить Ваниде подчиненного.
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern'  ;  
GO  
```

Теперь Кевин — подчиненный Ваниды, которая является подчиненной Jill, которая является подчиненной David. Это означает, что Kevin находится на уровне /3/1/1/. Чтобы переместить всех подчиненных Jill к новому руководителю, мы присвоим новое значение всем узлам, имеющим значение /3/ в столбце OrgNode . Выполните следующий код, чтобы произвести изменение, в результате которого Wanida станет подчиненной Sariya, но Kevin останется подчиненным Wanida:

DECLARE @OldParent hierarchyid, @NewParent hierarchyid  
SELECT @OldParent = OrgNode FROM HumanResources.EmployeeOrg  
WHERE EmployeeID = 119 ; -- Jill  
SELECT @NewParent = OrgNode FROM HumanResources.EmployeeOrg  
WHERE EmployeeID = 46 ; -- Sariya  
DECLARE children_cursor CURSOR FOR  
SELECT OrgNode FROM HumanResources.EmployeeOrg  
WHERE OrgNode.GetAncestor(1) = @OldParent;  
DECLARE @ChildId hierarchyid;  
OPEN children_cursor  
FETCH NEXT FROM children_cursor INTO @ChildId;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
START:  
    DECLARE @NewId hierarchyid;  
    SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)  
    FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(1) = @NewParent;  

    UPDATE HumanResources.EmployeeOrg  
    SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)  
    WHERE OrgNode.IsDescendantOf(@ChildId) = 1;  
    IF @@error <> 0 GOTO START -- On error, retry  
        FETCH NEXT FROM children_cursor INTO @ChildId;  
END  
CLOSE children_cursor;  
DEALLOCATE children_cursor;

Чтобы просмотреть результат, выполните следующий код:

SELECT OrgNode.ToString() AS Text_OrgNode,   
OrgNode, OrgLevel, EmployeeID, EmpName, Title   
FROM HumanResources.EmployeeOrg ;  
GO

Результирующий набор:

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title  
------------ ------- -------- ---------- ------- -----------------  
/            Ox      0        6          David   Marketing Manager  
/1/          0x58    1        46         Sariya  Marketing Specialist  
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant  
/1/1/1/      0x5AD0  3        291        Kevin   Marketing Intern  
/2/          0x68    1        271        John    Marketing Specialist  
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant  
/3/          0x78    1        119        Jill    Marketing Specialist

Все организационное дерево, которое подчинялось Jill (Wanida и Kevin) теперь подчиняется Sariya.

Хранимую процедуру для реорганизации раздела иерархии можно найти в подразделе "Перемещение поддеревьев" раздела Реорганизация раздела иерархии.

Занятие 2. Создание данных в иерархической таблице и управление ими

Предварительные условия

Создание таблицы с помощью типа данных hierarchyid

Создание таблицы EmployeeOrg

Заполнение иерархической таблицы с помощью иерархических методов

Вставка корневого элемента иерархического дерева

Вставка записи подчиненного

Создание процедуры ввода новых узлов

Создание запросов к иерархической таблице с помощью иерархических методов

Поиск подчиненных узлов

Использование методов GetRoot и GetLevel

Переупорядочение данных в иерархической таблице с помощью иерархических методов

Перемещение одной строки на новое место в иерархии

Реорганизация раздела иерархии

Обратная связь

Обратная связь

Дополнительные ресурсы