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

1. 다음 예에서는 Marketing Manager 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. Sariya는 David에게 보고합니다. Sariya의 노드를 삽입하려면 hierarchyid 데이터 형식의 적절한 OrgNode 값을 만들어야 합니다. 다음 코드에서는 hierarchyid 데이터 형식의 변수를 만들고 이를 테이블의 루트 OrgNode 값으로 채웁니다. 그런 다음 해당 변수를 GetDescendant() 메서드와 함께 사용하여 하위 노드인 행을 삽입합니다. GetDescendant는 두 개의 인수를 사용합니다. 인수 값에 대해 다음 옵션을 검토합니다.

   • 부모가 NULL인 경우 GetDescendant는 NULL을 반환합니다.

   • 부모가 NULL이 아니고 자식1과 자식2가 모두 NULL인 경우 GetDescendant는 부모의 자식을 반환합니다.

   • 부모와 자식1이 NULL이 아니고 자식2가 NULL인 경우 GetDescendant는 자식1보다 큰 부모의 자식을 반환합니다.

   • 부모와 자식2가 NULL이 아니고 자식1이 NULL인 경우 GetDescendant는 자식2보다 작은 부모의 자식을 반환합니다.

   • 부모, 자식1 및 자식2가 모두 NULL이 아닌 경우 GetDescendant는 자식1보다 크고 자식2보다 작은 부모의 자식을 반환합니다.

   다음 코드에서는 테이블에 루트를 제외한 행이 아직 없기 때문에 루트 부모의 (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 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
   

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