階層問合せ

表に階層データが含まれる場合、階層問合せ句を使用して階層順に行を選択することができます。

hierarchical_query_clause::=

図「hierarchical_query_clause.gif」の説明

conditionには、第7章「条件」で説明した任意の条件を使用できます。

START WITH句では、階層のルート行を指定します。

CONNECT BY句では、階層の親/子の行の関連を指定します。

• NOCYCLEパラメータは、データ内にCONNECT BYループが存在する場合でも問合せで行を戻すようにOracle Databaseに指示します。このパラメータをCONNECT_BY_ISCYCLE疑似列とともに使用すると、ループが含まれている行を確認できます。詳細は、「CONNECT_BY_ISCYCLE疑似列」を参照してください。

• 階層問合せでは、condition内の1つの式を、親である行を参照するためにPRIOR演算子で修飾する必要があります。次に例を示します。

  ... PRIOR expr = expr
  or
  ... expr = PRIOR expr
  

  CONNECT BY conditionが複合条件の場合、1つの条件のみにPRIOR演算子が必要です(複数のPRIOR条件を使用することもできます)。次に例を示します。

  CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
  CONNECT BY PRIOR employee_id = manager_id and 
             PRIOR account_mgr_id = customer_id ...
  

  PRIORは単項演算子であり、単項算術演算子の+および-と同じ優先順位を持っています。この演算子は、階層問合せ内でカレント行の親である行の直後にある式を評価します。

  PRIORは、等価演算子を使用して列の値を比較する場合によく使用されます。(PRIORキーワードは演算子のどちら側でもかまいません。)PRIORを指定すると、列の親である行の値が使用されます。等号(=)以外の演算子は、理論上はCONNECT BY句に指定できます。ただし、これらの他の演算子の組合せによっては、作成される条件は無限ループを発生させる場合があります。この場合、実行時にループが検出され、エラーが戻されます。

CONNECT BY条件とPRIOR式は、いずれも相関関係のない副問合せの形式で指定できます。ただし、CURRVALおよびNEXTVALは、無効なPRIOR式であるため、PRIOR式は順序を参照できません。

CONNECT_BY_ROOT演算子を使用してSELECT構文のリスト内の列を問い合せることによって、階層問合せをさらに向上できます。この演算子は、親の行のみでなく、階層内のすべての祖先の行を戻すことによって、階層問合せのCONNECT BY [PRIOR]条件の機能を拡張します。

関連項目: この演算子の詳細は、「CONNECT_BY_ROOT」および「階層問合せの例」を参照してください。

Oracleは次のように階層問合せを処理します。

• 最初に、結合(指定されている場合)が、FROM句で指定されているか、またはWHERE句述語で指定されているかが評価されます。

• CONNECT BY条件が評価されます。

• 残りのWHERE句述語が評価されます。

次に、Oracleはこれらの評価からの情報を使用して、次の手順で階層を形成します。

1. Oracleは、階層のルート行を選択します。これらの行は、START WITH条件を満たすものです。

2. Oracleは、各ルート行の子である行を選択します。子である各行は、1つのルート行に関してCONNECT BY条件を満たす必要があります。

3. Oracleは、子である行の連続生成を選択します。まず、手順2で戻された子である行を選択し、その行にある子を選択します(以降同様に続きます)。現在の親である行に関するCONNECT BY条件を評価することによって、常に子を選択します。

4. 問合せに結合を含まないWHERE句が含まれる場合、Oracleは、階層からWHERE句の条件を満たさないすべての行を排除します。条件を満たさない子である行をすべて排除するのではなく、各行に関してこの条件をそれぞれ評価します。

5. Oracleは、図9-1に示す順序で行を戻します。この図では、親である行の下に子である行が表示されます。階層ツリーの詳細は、図2-1「階層ツリー」を参照してください。

図9-1 階層問合せ

「図9-1 階層問合せ」の説明

親である行に対する子を検索するために、Oracleは、親である行のCONNECT BY条件のPRIOR式、および各行の他の式を表の中で評価します。条件がTRUEとなる行が、その親である行の子です。CONNECT BY条件に、問合せによって選択された行をさらにフィルタ処理するための他の条件を含めることができます。

CONNECT BY条件が階層のループになった場合、Oracleはエラーを戻します。1つの行が別の行の親(または親の親または祖先)および子(または子の子または子孫)の場合、ループが発生します。

注意: 階層問合せでは、ORDER BYまたはGROUP BYを指定しないでください。指定すると、CONNECT BYの結果の階層順序が上書きされます。同じ親の兄弟である行を順序付ける場合は、ORDER SIBLINGS BY句を使用します。「order_by_clause」を参照してください。

階層問合せの例

CONNECT BY句の例 次の階層問合せは、CONNECT BYを使用して従業員とマネージャの関係を定義しています。

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
        203 Mavris                           101
        204 Baer                             101
. . .

LEVELの例 次の例は、前述の例と似ていますが、LEVEL疑似列を使用して、親および子である行を表示しています。

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        110 Chen                             108          3
        111 Sciarra                          108          3
        112 Urman                            108          3
        113 Popp                             108          3
        200 Whalen                           101          2
        203 Mavris                           101          2
        204 Baer                             101          2
        205 Higgins                          101          2
        206 Gietz                            205          3
        102 De Haan                          100          1
...

START WITH句の例 次の例は、START WITH句を追加して階層にルート行を指定し、SIBLINGSキーワードを使用したORDER BY句を追加して階層の順序を保持しています。

SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3
...

hr.employees表で、Steven Kingは会社の最高責任者であるため、マネージャはいません。彼の従業員には、部門80のマネージャであるJohn Russellがいます。employees表を更新してRussellをKingのマネージャとして設定する場合は、データ内にループを作成します。

UPDATE employees SET manager_id = 145
   WHERE employee_id = 100;

SELECT last_name "Employee", 
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;

ERROR:
ORA-01436: CONNECT BY loop in user data

CONNECT BY条件にNOCYCLEパラメータを指定すると、Oracleはループでも行を戻します。CONNECT_BY_ISCYCLE疑似列には、サイクルを含む行が表示されます。

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4
   ORDER BY "Employee", "Cycle", LEVEL, "Path";

Employee                       Cycle      LEVEL Path
------------------------- ---------- ---------- -------------------------
Abel                               0          3 /King/Zlotkey/Abel
Ande                               0          3 /King/Errazuriz/Ande
Banda                              0          3 /King/Errazuriz/Banda
Bates                              0          3 /King/Cambrault/Bates
Bernstein                          0          3 /King/Russell/Bernstein
Bloom                              0          3 /King/Cambrault/Bloom
Cambrault                          0          2 /King/Cambrault
Cambrault                          0          3 /King/Russell/Cambrault
Doran                              0          3 /King/Partners/Doran
Errazuriz                          0          2 /King/Errazuriz
Fox                                0          3 /King/Cambrault/Fox
...

CONNECT_BY_ISLEAFの例 次の文は、階層問合せを使用して、列の値をカンマで区切られたリストにする方法を示しています。

SELECT LTRIM(SYS_CONNECT_BY_PATH (warehouse_id,','),',') FROM
   (SELECT ROWNUM r, warehouse_id FROM warehouses)
   WHERE CONNECT_BY_ISLEAF = 1
   START WITH r = 1
   CONNECT BY r = PRIOR r + 1
   ORDER BY warehouse_id; 
 
LTRIM(SYS_CONNECT_BY_PATH(WAREHOUSE_ID,','),',')
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9

CONNECT_BY_ROOTの例 次の例では、部門110の各従業員の名字、階層内で各従業員の上に位置する最高レベルのマネージャ、マネージャと従業員との間のレベル数、および両者間のパスを戻します。

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id
   ORDER BY "Employee", "Manager", "Pathlen", "Path";

Employee        Manager            Pathlen Path
--------------- --------------- ---------- ------------------------------
Gietz           Higgins                  1 /Higgins/Gietz
Gietz           King                     3 /King/Kochhar/Higgins/Gietz
Gietz           Kochhar                  2 /Kochhar/Higgins/Gietz
Higgins         King                     2 /King/Kochhar/Higgins
Higgins         Kochhar                  1 /Kochhar/Higgins

次の例では、GROUP BY句を使用して、部門110の各従業員と階層内でその従業員の上位に位置するすべての従業員の合計の給与を戻します。

SELECT name, SUM(salary) "Total_Salary" FROM (
   SELECT CONNECT_BY_ROOT last_name as name, Salary
      FROM employees
      WHERE department_id = 110
      CONNECT BY PRIOR employee_id = manager_id)
      GROUP BY name
   ORDER BY name, "Total_Salary";

NAME                      Total_Salary
------------------------- ------------
Gietz                             8300
Higgins                          20300
King                             20300
Kochhar                          20300

関連項目: 階層問合せでのこれらの疑似列の処理方法については、「LEVEL疑似列」および「CONNECT_BY_ISCYCLE疑似列」を参照してください。 ルートからノードへの列の値のパスの検索については、「SYS_CONNECT_BY_PATH」を参照してください。 ORDER BY句のSIBLINGSキーワードについては、「order_by_clause」を参照してください。 subquery_factoring_clauseでは、再帰的副問合せのファクタリング(再帰的WITH)がサポートされ、これによって、階層データの問合せを行うことができます。この機能は、深さ優先検索および幅優先検索を提供し、複数の再帰的ブランチをサポートするという点において、CONNECT BYよりも強力です。