Shoeisha Technology Media

CodeZine(コードジン)

記事種別から探す

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

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2009/07/03 14:00

ダウンロード SourceCode (957.0 B)

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

バックナンバー

連載:Oracleの階層問い合わせ
All contents copyright © 2005-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.5