2단원: 계층적 테이블의 데이터 만들기 및 관리

아티클
07/31/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

1단원에서는 hierarchyid 데이터 형식을 사용하도록 기존 테이블을 수정하고, hierarchyid 열을 기존 데이터 표현으로 채웠습니다. 이 단원에서는 새 테이블로 시작하고 계층적 메서드를 사용하여 데이터를 삽입합니다. 그런 다음, 계층적 메서드를 사용하여 데이터를 쿼리하고 조작합니다.

필수 조건

이 자습서를 완료하려면 SQL Server Management Studio, SQL Server를 실행하는 서버에 대한 액세스 및 AdventureWorks2022 데이터베이스가 필요합니다.

SSMS(SQL Server Management Studio)를 설치합니다.
SQL Server 2022 Developer Edition을 설치합니다.
AdventureWorks 샘플 데이터베이스를 다운로드합니다.

SSMS에서 데이터베이스를 복원하기 위한 지침은 여기: SSMS를 사용하여 데이터베이스 백업 복원에 있습니다.

hierarchyid 데이터 형식을 사용하여 테이블 만들기

다음 예에서는 직원 데이터와 보고 계층을 포함하는 EmployeeOrg라는 테이블을 만듭니다. 이 예제에서는 테이블을 AdventureWorks2022 데이터베이스에 만들지만 이는 선택 사항입니다. 예제를 단순하게 유지하기 위해 이 표에는 5개의 열만 포함됩니다.

OrgNode는 계층적 관계를 저장하는 hierarchyid 열입니다.
OrgLevel은 계산 열이며 계층의 각 노드 수준을 저장하는 OrgNode 열을 기반으로 합니다. 폭 우선 인덱스에 사용됩니다.
EmployeeID에는 급여와 같은 애플리케이션에 사용되는 일반적인 직원 ID 번호가 포함됩니다. 새 애플리케이션 개발에서 애플리케이션은 OrgNode 열을 사용할 수 있으며 이 별도의 EmployeeID 열은 필요하지 않습니다.
EmpName에는 직원의 이름이 포함됩니다.
Title에는 직원의 직함이 포함됩니다.

Employee 테이블 만들기

쿼리 편집기 창에서 다음 코드를 실행하여 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명의 직원이 근무하고 있습니다. 직원 계층 구조는 다음과 같습니다.

David, EmployeeID 6은 마케팅 관리자입니다. 다음 3명의 마케팅 전문가가 David에게 보고합니다.

Sariya, EmployeeID 46
John, EmployeeID 271
Jill, EmployeeID 119

마케팅 도우미 Wanida(EmployeeID 269)는 Sariya에 보고하고 마케팅 도우미 Mary(EmployeeID 272)는 John에 보고합니다.

계층 트리의 루트 삽입

다음 예제에서는 마케팅 관리자인 David를 계층 구조의 루트에 있는 테이블에 삽입합니다. 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 데이터 형식을 더 쉽게 이해할 수 있는 형식으로 변환합니다.

하위 직원 삽입

Sariya은 David에 보고합니다. Sariya's 노드를 삽입하려면 hierarchyid 데이터 형식의 적절한 OrgNode 값을 만들어야 합니다. 다음 코드에서는 hierarchyid 데이터 형식의 변수를 만들고, 이를 테이블의 루트 OrgNode 값으로 채웁니다. 그런 다음, GetDescendant (데이터베이스 엔진) 메서드가 포함된 해당 변수를 사용하여 하위 노드인 행을 삽입합니다. GetDescendant는 2개의 인수를 사용합니다. 인수 값에 대해 다음 옵션을 검토합니다.
- 부모가 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) 인수를 사용합니다. 다음 코드를 실행하여 Sariya를 삽입합니다.
```
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 PROCEDURE 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

다음 예제에서는 David에게 직접 또는 간접적으로 보고하는 나머지 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는 해당 값이 0 수준의 하위 항목이기 때문에 나열됩니다. Wanida는 1 수준의 하위 항목입니다.
이 정보는 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

이번에는 David에게 보고하는 두 수준 아래에 있는 Mary도 반환됩니다.

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

계층을 다시 구성하는 것은 일반적인 유지 관리 태스크입니다. 이 작업에서는 GetReparentedValue(데이터베이스 엔진) 메서드가 포함된 UPDATE 문을 사용하여 먼저 단일 행을 계층 구조의 새 위치로 이동합니다. 그런 다음, 전체 하위 트리를 새 위치로 이동합니다.

GetReparentedValue 메서드는 두 개의 인수를 사용합니다. 첫 번째 인수는 수정할 계층 구조의 일부를 설명합니다. 예를 들어 계층이 /1/4/2/3/인 경우 /1/4/ 섹션을 변경하여 계층을 /2/1/2/3/으로 만들고 마지막 두 노드(2/3/)는 변경하지 않으려면 변경되는 노드(/1/4/)를 첫 번째 인수로 제공해야 합니다. 두 번째 인수는 예제에서 새 /2/1/ 계층 구조 수준을 제공합니다. 두 인수는 동일한 수의 수준을 포함할 필요가 없습니다.

단일 행을 계층 구조의 새 위치로 이동

현재 Wanida는 Sariya에게 보고합니다. 이 프로시저에서는 현재 노드 /1/1/에서 Wanida를 이동하여 이 사람이 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/ 노드에 있습니다.

계층 구조의 섹션 재구성

더 많은 수의 사용자를 동시에 이동하는 방법을 보여주려면 먼저 다음 코드를 실행하여 Wanida에 보고하는 인턴을 추가합니다.
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

이제 Kevin은 Wanida에게 보고하고, Wanida는 Jill에게 보고하고, 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에게 보고됩니다.

계층 구조의 섹션을 다시 구성하기 위한 저장 프로시저는 계층적 데이터(SQL Server)의 하위 트리 이동 섹션을 참조하세요.

다음을 통해 공유

2단원: 계층적 테이블의 데이터 만들기 및 관리

필수 조건

hierarchyid 데이터 형식을 사용하여 테이블 만들기

Employee 테이블 만들기

계층적 메서드를 사용하여 계층적 테이블 채우기

계층 트리의 루트 삽입

하위 직원 삽입

새 노드를 입력하는 프로시저 만들기

계층적 메서드를 사용하여 계층적 테이블 쿼리

하위 노드 찾기

GetRoot 및 GetLevel 사용

계층적 메서드를 사용하여 계층적 테이블의 데이터 순서 재정렬

단일 행을 계층 구조의 새 위치로 이동

계층 구조의 섹션 재구성

피드백

추가 리소스