SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

Oracleの階層問い合わせ

Oracleの階層問い合わせ(2)
(Level,sys_connect_by_path)

Level、sys_connect_by_pathを使ったOracleの階層問い合わせ

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (957.0 B)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 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擬似列のイメージ
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擬似列のイメージ
sys_connect_by_path関数のイメージ

 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;

会員登録無料すると、続きをお読みいただけます

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

次のページ
3. order siblings by

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
Oracleの階層問い合わせ連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/3249 2009/07/03 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング