8-43 Oracle9iでnocycleを模倣

SQLパズル

Table
COLA  COLB
----  ----
   1     2
   2     3
   3     4
   4     5
   5     1
   6     7
   7     8

nocycleを使った下記のクエリを
Oracle9iで模倣する。

select ColA,ColB
  from (select 1 as ColA,2 as ColB from dual
  union select 2 as ColA,3 as ColB from dual
  union select 3 as ColA,4 as ColB from dual
  union select 4 as ColA,5 as ColB from dual
  union select 5 as ColA,1 as ColB from dual
  union select 6 as ColA,7 as ColB from dual
  union select 7 as ColA,8 as ColB from dual)
start with ColA = 1
connect by nocycle prior ColB = ColA;

出力結果
COLA  COLB
----  ----
   1     2
   2     3
   3     4
   4     5
   5     1

こちらを参考にさせていただきました(英語)

SQL

select distinct ColA,ColB
  from (select 1 as ColA,2 as ColB from dual
  union select 2 as ColA,3 as ColB from dual
  union select 3 as ColA,4 as ColB from dual
  union select 4 as ColA,5 as ColB from dual
  union select 5 as ColA,1 as ColB from dual
  union select 6 as ColA,7 as ColB from dual
  union select 7 as ColA,8 as ColB from dual)
start with ColA = 1
connect by prior ColB = ColA
    and Level != 50
    and prior dbms_random.value != 1;

解説

dbms_random.valueの値は、0以上1未満なので
!=1 を指定しているのです。

prior sys_guid() is not null で代用してもいいようですね

DBMS_Random.Valueファンクション

8-25 Oracle9iで、connect_by_rootを模倣
10-149 Oracle9iでconnect_by_isleafを模倣

表関数で階層問い合わせを実装して、nocycleを模倣してもいいです。
10-288 木のIDと節のIDのセットで識別

---