階層的な手法を使用した階層テーブルの作成

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 は、2 つの引数を受け取ります。引数値の次のオプションを確認してください。

   • parent が NULL の場合、GetDescendant は NULL を返します。

   • parent が NULL でなく、child1 と child2 の両方が NULL の場合、GetDescendant は parent の子を返します。

   • parent と child1 が NULL でなく、child2 が NULL の場合、GetDescendant は child1 より大きい parent の子を返します。

   • parent と child2 が NULL でなく、child1 が NULL の場合、GetDescendant は child2 より小さい parent の子を返します。

   • parent、child1、child2 のすべてが NULL でない場合、GetDescendant は child1 より大きく child2 より小さい parent の子を返します。

   テーブルにはルート以外にまだ 1 行もないため、次のコードではルートの (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