Gblog

おもにTips

階層問い合わせ(逆)

階層問い合わせがわかりにくくなるのは、どっちが、どっちかわからなくなるからだと思います。

少なくとも自分はそうです。。。

 

で、あえて逆をたどります。

 

サンプルデータは前回と同じ、ですがちょっと追加。

create table test (id number, next number);
insert into test values ( NULL , 1 );
insert into test values ( 1 , 2 );
insert into test values ( 1 , 3 );
insert into test values ( 1 , 4 );
insert into test values ( 4 , NULL );
insert into test values ( 2 , 5 );
insert into test values ( 5 , NULL );
insert into test values ( 2 , 6 );
insert into test values ( 6 , NULL );
insert into test values ( 3 , 5 );
insert into test values ( 3 , 7 );
insert into test values ( 7 , 8 );
insert into test values ( 8 , NULL );
commit;

 

id=NULL を足しています。

 

で逆バージョン基本形

select next,id,Level
from test
start with id = 8
connect by prior id = next;

単純に、next と id を入れ替えただけ。select句も入れ替えています。

prior id = next は、「親のid は子のnext」

結果をみると、子から親をたどっているように見えます。

      NEXT         ID      LEVEL
---------- ---------- ----------
                    8          1
         8          7          2
         7          3          3
         3          1          4
         1                     5

 

バージョンでパスを表示すると。。。

select next,id,Level,sys_connect_by_path(to_char(id),'/') as Path
from test
start with id = 8
connect by prior id = next;

      NEXT         ID      LEVEL PATH
---------- ---------- ---------- --------------------
                    8          1 /8
         8          7          2 /8/7
         7          3          3 /8/7/3
         3          1          4 /8/7/3/1
         1                     5 /8/7/3/1/

こんな感じ。

 

一応、DBA_DEPENCENCIES の階層問い合わせも(取り扱い注意!)

set verify off
define downer = &1
define dname  = &2
select
  upper('&downer') || '.' || upper('&dname') || sys_connect_by_path(owner||'.'||name,'<-')  DEPENDENCY
from
  DBA_dependencies
where connect_by_IsLeaf = 1
start with referenced_owner = upper('&downer') and referenced_name = upper('&dname')
connect by prior owner = referenced_owner and prior name = referenced_name
/