はじめに
Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、Level擬似列、sys_connect_by_path関数、order siblings byを扱います。
対象読者
- Oracleの階層問い合わせを使いたい方
- OracleのSQLの理解を深めたい方
必要な環境
本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。
1. Level擬似列
Level擬似列は、ノードのレベルを表します。start with句の条件を満たしたノードのレベルが1となり、子供はレベル2、孫はレベル3といった感じでレベルが増えていきます。サンプルを見てみましょう。
LinkT
| ID | nextID |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 6 |
| 5 | 7 |
| 7 | 8 |
| 8 | 9 |
IDが1の行から、親のnextID = 子のIDであることを親子条件として、階層問い合わせを行います。
Level擬似列の使用例1
select ID,nextID,Level, sys_connect_by_path(to_char(ID),',') as Path from LinkT start with ID = 1 connect by prior nextID = ID;
出力結果
| ID | nextID | Level | Path |
| 1 | 2 | 1 | ,1 |
| 2 | 3 | 2 | ,1,2 |
| 3 | 4 | 3 | ,1,2,3 |
| 4 | 6 | 4 | ,1,2,3,4 |
| 3 | 5 | 3 | ,1,2,3 |
| 5 | 7 | 4 | ,1,2,3,5 |
| 7 | 8 | 5 | ,1,2,3,5,7 |
| 8 | 9 | 6 | ,1,2,3,5,7,8 |
Level擬似列のイメージは、下記となります。
Level擬似列のイメージ
もうひとつサンプルを見てみましょう。レベルの上限を3として、階層問い合わせを行った後、where句でレベルが1または3の行を抽出してます。
Level擬似列の使用例2
select ID,nextID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from LinkT
where Level in(1,3)
start with ID = 1
connect by prior nextID = ID
and Level <=3;
出力結果
| ID | nextID | Level | Path |
| 1 | 2 | 1 | ,1 |
| 3 | 4 | 3 | ,1,2,3 |
| 3 | 5 | 3 | ,1,2,3 |
2. sys_connect_by_path関数
sys_connect_by_path関数は、根からの経路を表します。前問のサンプルを再度見てみましょう。
sys_connect_by_path関数の使用例
select ID,nextID,Level, sys_connect_by_path(to_char(ID),',') as Path from LinkT start with ID = 1 connect by prior nextID = ID;
出力結果
| ID | nextID | Level | Path |
| 1 | 2 | 1 | ,1 |
| 2 | 3 | 2 | ,1,2 |
| 3 | 4 | 3 | ,1,2,3 |
| 4 | 6 | 4 | ,1,2,3,4 |
| 3 | 5 | 3 | ,1,2,3 |
| 5 | 7 | 4 | ,1,2,3,5 |
| 7 | 8 | 5 | ,1,2,3,5,7 |
| 8 | 9 | 6 | ,1,2,3,5,7,8 |
sys_connect_by_path関数のイメージは、下記となります。
Level擬似列のイメージ
connect by句では、sys_connect_by_path関数を使うことはできません。
ORA-30002: ここではSYS_CONNECT_BY_PATH関数を使用できません
select ID,nextID
from LinkT
start with ID = 1
connect by prior nextID = ID
and instr(sys_connect_by_path(to_char(ID),','),'7') = 0;
where句でも、sys_connect_by_path関数を使うことはできません。
ORA-30002: ここではSYS_CONNECT_BY_PATH関数を使用できません
select ID,nextID from LinkT where instr(sys_connect_by_path(to_char(ID),','),'7') = 0 start with ID = 1 connect by prior nextID = ID;
