表に階層データが含まれる場合、階層問合せ句を使用して階層順に行を選択することができます。
hierarchical_query_clause::=
condition
には、第6章「条件」で説明した任意の条件を使用できます
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
]条件の機能を拡張します。
Oracleは次のように階層問合せを処理します。
最初に、結合(指定されている場合)が、FROM
句で指定されているか、またはWHERE
句述語で指定されているかが評価されます。
CONNECT
BY
条件が評価されます。
残りのWHERE
句述語が評価されます。
次に、Oracleはこれらの評価からの情報を使用して、次の手順で階層を形成します。
Oracleは、階層のルート行を選択します。これらの行は、START
WITH
条件を満たすものです。
Oracleは、各ルート行の子である行を選択します。子である各行は、1つのルート行に関してCONNECT
BY
条件を満たす必要があります。
Oracleは、子である行の連続生成を選択します。まず、手順2で戻された子である行を選択し、その行にある子を選択します(以降同様に続きます)。現在の親である行に関するCONNECT
BY
条件を評価することによって、常に子を選択します。
問合せに結合を含まないWHERE
句が含まれる場合、Oracleは、階層からWHERE
句の条件を満たさないすべての行を排除します。条件を満たさない子である行をすべて排除するのではなく、各行に関してこの条件をそれぞれ評価します。
Oracleは、図9-1に示す順序で行を戻します。この図では、親である行の下に子である行が表示されます。階層ツリーの詳細は、図3-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
関連項目:
|