다음을 통해 공유


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

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

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

필수 조건

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

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

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

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

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

Employee 테이블 만들기

  1. 쿼리 편집기 창에서 다음 코드를 실행하여 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
    
  2. 효율적인 너비 우선 검색을 지원하기 위해 다음 코드를 실행하여 복합 인덱스를 OrgLevelOrgNode 열에 만듭니다.

    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에 보고합니다.

계층 트리의 루트 삽입

  1. 다음 예제에서는 마케팅 관리자인 David를 계층 구조의 루트에 있는 테이블에 삽입합니다. OrdLevel 열이 계산 열입니다. 따라서 INSERT 문의 일부가 아닙니다. 이 첫 번째 레코드는 GetRoot(데이터베이스 엔진) 메서드를 사용하여 이 첫 번째 레코드를 계층의 루트로 채웁니다.

    INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
    VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
    GO
    
  2. 다음 코드를 실행하여 테이블의 초기 행을 검사합니다.

    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 데이터 형식을 더 쉽게 이해할 수 있는 형식으로 변환합니다.

하위 직원 삽입

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

    • 부모가 NULL인 경우 GetDescendantNULL를 반환합니다.
    • 부모가 NULL가 아니고 child1child2가 모두 NULL인 경우 GetDescendant는 부모의 자식을 반환합니다.
    • 부모 및 child1NULL가 아니고 child2NULL인 경우 GetDescendantchild1보다 큰 부모의 자식을 반환합니다.
    • 부모 및 child2NULL가 아니고 child1NULL인 경우 GetDescendantchild2보다 작은 부모의 자식을 반환합니다.
    • 부모, child1child2가 모두 NULL이 아니면 GetDescendantchild1보다 크고 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');
    
  2. 첫 번째 프로시저에서 쿼리를 반복하여 테이블을 쿼리하고, 항목이 표시되는 방법을 확인합니다.

    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
    

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

  1. 데이터 입력을 간소화하려면 직원을 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
    
  2. 다음 예제에서는 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';
    
  3. 다시, 다음 쿼리를 실행하여 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 테이블이 완전히 채워졌으므로 이 작업에서 일부 계층적 메서드를 사용하여 계층 구조를 쿼리하는 방법을 보여줍니다.

하위 노드 찾기

  1. Sariya에게는 부하 직원이 한 명 있습니다. Sariya의 부하 직원에 대해 쿼리하려면 IsDescendantOf(데이터베이스 엔진) 메서드를 사용하는 다음 쿼리를 실행합니다.

    DECLARE @CurrentEmployee HIERARCHYID
    
    SELECT @CurrentEmployee = OrgNode
    FROM HumanResources.EmployeeOrg
    WHERE EmployeeID = 46;
    
    SELECT *
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
    

    결과에는 SariyaWanida가 모두 나열됩니다. Sariya는 해당 값이 0 수준의 하위 항목이기 때문에 나열됩니다. Wanida1 수준의 하위 항목입니다.

  2. 이 정보는 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만 나열됩니다.

  3. 이제 @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 사용

  1. 계층 구조가 커짐에 따라 멤버가 계층 구조에 있는 위치를 결정하는 것이 더 어렵습니다. GetLevel(데이터베이스 엔진) 메서드를 사용하여 계층에서의 각 행 수준을 알 수 있습니다. 다음 코드를 실행하여 모든 행의 수준을 확인합니다.

    SELECT OrgNode.ToString() AS Text_OrgNode,
    OrgNode.GetLevel() AS EmpLevel, *
    FROM HumanResources.EmployeeOrg;
    GO
    
  2. 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/ 계층 구조 수준을 제공합니다. 두 인수는 동일한 수의 수준을 포함할 필요가 없습니다.

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

  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
    
  2. 다음 코드를 실행하여 결과를 확인합니다.

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

    Wanida는 이제 /3/1/ 노드에 있습니다.

계층 구조의 섹션 재구성

  1. 더 많은 수의 사용자를 동시에 이동하는 방법을 보여주려면 먼저 다음 코드를 실행하여 Wanida에 보고하는 인턴을 추가합니다.

    EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
    GO
    
  2. 이제 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;
    
  3. 다음 코드를 실행하여 결과를 확인합니다.

    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)하위 트리 이동 섹션을 참조하세요.