階層問合せ

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

hierarchical_query_clause::=

図hierarchical_query_clause.epsの説明

conditionは、「条件」で説明されている任意の条件にすることができます。

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の例

次の階層問合せは、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