Oracleの階層問い合わせ（2） （Level,sys_connect_by_path）

https://codezine.jp/article/detail/3249

 

Oracleの階層問い合わせ（2）　（Level,sys_connect_by_path）

　Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、Level擬似列、sys_connect_by_path関数、order siblings byを扱います。

codezine.jp

​

​

　Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、Level擬似列、sys_connect_by_path関数、order siblings byを扱います。

---

目次

はじめに

　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;

​