再帰的照会の使用

一部のアプリケーションには、必然と再帰的なデータを取り扱うものがあります。このタイプのデータを照会するには、階層照会または再帰的共通表式を使用できます。

再帰的データの一例として、各種部品およびそれを構成する副部品にわたって処理する部品表 (BOM) アプリケーションがあります。例えば、椅子はシート・ユニットと足部の組み立て部品からできています。シート・ユニットは 1 つのシートと 2 つのアームで構成されている場合があります。これらの部品のいずれも、椅子を組み立てるのに必要なすべての部品がリストされるまで、さらに細かい副部品へと分解できます。

DB2® for i は、再帰的照会を定義するための 2 つの方法を提供します。最初の方法は階層照会と呼ばれ、CONNECT BY 文節を使用して、親の行を子の行に関連付ける方法を定義します。2 番目の方法では、再帰的共通表式を使用します。これは、共通表式を使用して最初の (シード) 行を定義してから、UNION を使用して子の行を決定する方法を定義します。

再帰的照会を定義するこれらの方法にはそれぞれ、利点と欠点があります。 CONNECT BY 構文の方が、はるかに理解しやすいですが、照会でデータを派生させる方法が少なくなります。CONNECT BY は、照会内の任意の場所の任意の副選択で指定できます。再帰的共通表式では、子の行を生成するために UNION を定義する方法のためのオプションが、より多く用意されています。

connect by 再帰的照会と再帰的共通表式照会とには、動作の違いがいくつかあります。まず、循環データの処理方法が違います。この違いについては、例で説明します。次に、connect by では、兄弟間でのソートが可能です。これについても例で示します。最後に、2 つの実装では、再帰の実装に使用されるキューにデータを配置する方法が違います。デフォルトでは、再帰的共通表式のデータは、先入れ先出しの幅優先順序になる傾向にあります。 connect by では、順序は深さ優先になるように設計されています。つまり、再帰的ステップの行は親の行の直後に配置されることになります。再帰的共通表式の構文では、SEARCH 文節を追加することで、階層順序を深さ優先にするか幅優先にするかを選択できます。connect by 構文では常に深さ優先です。

これらの再帰的方法のための旅行プランナーの例では、フライトと鉄道路線を使用して、各都市間の移動経路を検索します。以下の表定義とデータが例の中で使用されます。

CREATE TABLE FLIGHTS (DEPARTURE CHAR(20),
                      ARRIVAL CHAR(20), 
                      CARRIER CHAR(15),
                      FLIGHT_NUMBER CHAR(5), 
                      PRICE INT);


INSERT INTO FLIGHTS VALUES('New York', 'Paris', 'Atlantic', '234', 400);
INSERT INTO FLIGHTS VALUES('Chicago', 'Miami', 'NA Air', '2334', 300);
INSERT INTO FLIGHTS VALUES('New York', 'London', 'Atlantic', '5473', 350);
INSERT INTO FLIGHTS VALUES('London', 'Athens'  , 'Mediterranean', '247', 340);
INSERT INTO FLIGHTS VALUES('Athens', 'Nicosia' , 'Mediterranean', '2356', 280); 
INSERT INTO FLIGHTS VALUES('Paris', 'Madrid' , 'Euro Air',  '3256', 380);
INSERT INTO FLIGHTS VALUES('Paris', 'Cairo' , 'Euro Air', '63', 480);
INSERT INTO FLIGHTS VALUES('Chicago', 'Frankfurt', 'Atlantic', '37', 480);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Moscow', 'Asia Air', '2337', 580);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Beijing', 'Asia Air',  '77', 480); 
INSERT INTO FLIGHTS VALUES('Moscow', 'Tokyo', 'Asia Air', '437', 680);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Vienna', 'Euro Air', '59', 200);
INSERT INTO FLIGHTS VALUES('Paris', 'Rome', 'Euro Air', '534', 340);
INSERT INTO FLIGHTS VALUES('Miami', 'Lima', 'SA Air', '5234', 530);
INSERT INTO FLIGHTS VALUES('New York', 'Los Angeles', 'NA Air', '84', 330);
INSERT INTO FLIGHTS VALUES('Los Angeles', 'Tokyo', 'Pacific Air', '824', 530);
INSERT INTO FLIGHTS VALUES('Tokyo', 'Hawaii', 'Asia Air', '94', 330);
INSERT INTO FLIGHTS VALUES('Washington', 'Toronto', 'NA Air', '104', 250);

CREATE TABLE TRAINS(DEPARTURE CHAR(20), 
                    ARRIVAL CHAR(20),
                    RAILLINE CHAR(15), 
                    TRAIN CHAR(5), 
                    PRICE INT); 

INSERT INTO TRAINS VALUES('Chicago', 'Washington', 'UsTrack', '323', 90;
INSERT INTO TRAINS VALUES('Madrid', 'Barcelona', 'EuroTrack', '5234', 60);
INSERT INTO TRAINS VALUES('Washington' , 'Boston' , 'UsTrack', '232', 50);

CREATE TABLE FLIGHTSTATS(FLIGHT# CHAR(5), 
                         ON_TIME_PERCENT DECIMAL(5,2), 
                         CANCEL_PERCENT DECIMAL(5,2)); 

INSERT INTO FLIGHTSTATS VALUES('234', 85.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('2334',  92.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('5473', 86.2, 0.10);
INSERT INTO FLIGHTSTATS VALUES('247',  91.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('2356', 91.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('3256', 92.0 , 0.10);
INSERT INTO FLIGHTSTATS VALUES('63', 90.5 , 0.10);
INSERT INTO FLIGHTSTATS VALUES('37', 87.0 , 0.20);
INSERT INTO FLIGHTSTATS VALUES('2337', 80.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('77', 86.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('437', 81.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('59',  85.0, 01.0);
INSERT INTO FLIGHTSTATS VALUES('534', 87.0 , 01.0);
INSERT INTO FLIGHTSTATS VALUES('5234', 88.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('84', 88.0, 0.1);
INSERT INTO FLIGHTSTATS VALUES('824', 93.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('94', 92.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('104', 93.0, 0.10);

CONNECT BY 階層照会の使用

シカゴから始めた場合に、フライトのある都市を検索し、その都市にたどり着くまでに必要となるフライトの数を検索すると想定します。以下の照会で、それらの情報が得られます。

SELECT CONNECT_BY_ROOT departure AS origin, departure, arrival, LEVEL AS flight_count
    FROM  flights
    START WITH departure = 'Chicago'
    CONNECT BY PRIOR arrival = departure

この照会は次の情報を戻します。

表 1. 前述の照会の結果
ORIGIN	DEPARTURE	ARRIVAL	FLIGHT_COUNT
Chicago	Chicago	Miami	1
Chicago	Miami	Lima	2
Chicago	Chicago	Frankfurt	1
Chicago	Frankfurt	Vienna	2
Chicago	Frankfurt	Beijing	2
Chicago	Frankfurt	Moscow	2
Chicago	Moscow	Tokyo	3
Chicago	Tokyo	Hawaii	4

この階層照会には複数の部分があります。始めの選択では、再帰の初期シードを定義しています。この場合、START WITH departure を「Chicago」とした flights 表からの行です。CONNECT BY 文節を使用して、既に生成されている行を「接続」して、照会の後続の反復用にさらなる行を生成する方法を定義しています。PRIOR 単項演算子は、前の行の結果に基づいて新規行を選択する方法を DB2 に指示します。START WITH 文節の結果によって選択された再帰的結合列 (通常 1 列であるが、複数列も可能) が、PRIOR キーワードによって参照されています。これは、前の行の ARRIVAL 都市が、新しい行の DEPARTURE 都市の PRIOR 値になることを意味します。これは、節 CONNECT BY PRIOR arrival = departure にカプセル化されます。

この照会例では、他の 2 つの connect by フィーチャーが示されています。初期化ステップで決定されて、生成されるすべての再帰的結果行で同じになる固定式値を定義するために、単項演算子 CONNECT_BY_ROOT が使用されています。通常、複数の START WITH 値がある可能性があるため、その特定の反復における開始値です。この照会では、結果セットで、Chicago からの異なる目的地オプションの ORIGIN を定義します。START WITH 文節が複数の都市を選択した場合、ORIGIN は、行が開始値として使用した都市を示します。

LEVEL は、connect by 再帰の使用時に使用可能な 3 つの疑似列の 1 つです。LEVEL の値は、現在行の再帰レベルを反映します。この例では、LEVEL は、ORIGIN の都市 (Chicago) から別の ARRIVAL 都市に行くために必要なフライト数も反映しています。

階層照会は、それに相当する再帰的共通表式照会と同じように実行され、同じ結果セットを生成します。再帰的共通表式および再帰的ビューの使用を参照してください。唯一の違いは、返される行の順序です。connect by 照会は、行を深さ優先順序で返します。つまり、結果セットの各行が、その親の行の直後に配置されます。再帰的共通表式照会は、行を幅優先順序で返します。つまり、あるレベルのすべての行が返されてから、前のレベルで生成されたすべての行が返されます。

例: CONNECT BY を使用した再帰に使用される 2 つの表

ここでは、Chicago を起点として、フライトに加えて鉄道による移動オプションを追加し、行くことができる都市と必要な乗り継ぎ回数が知りたい場合を考えます。

以下の connect by 照会は、そのような情報を返します。なお、対応する再帰的共通表式の例 (例: 再帰的共通表式を使用した再帰に使用される 2 つの表) では、鉄道とフライトの乗り継ぎ回数を識別したり、当該目的地までのチケットのコストを合計したりすることもできます。その計算は、より複雑であるが、より柔軟な再帰的共通表式構文を使用した場合に可能になる導出例です。その機能は、connect by 構文を使用した場合には使用できません。

SELECT CONNECT_BY_ROOT departure AS departure, arrival, LEVEL - 1 connections
  FROM
      ( SELECT departure, arrival FROM flights
           UNION
        SELECT departure, arrival FROM trains) t
  START WITH departure = 'Chicago'
  CONNECT BY PRIOR arrival = departure;

この照会は次の情報を戻します。

表 2. 前述の照会の結果
DEPARTURE	ARRIVAL	CONNECTIONS
Chicago	Miami	0
Chicago	Lima	1
Chicago	Frankfurt	0
Chicago	Vienna	1
Chicago	Beijing	1
Chicago	Moscow	1
Chicago	Tokyo	2
Chicago	Hawaii	3
Chicago	Washington	0
Chicago	Boston	1
Chicago	Toronto	1

この例では、再帰をフィードするデータ・ソースが 2 つあります (フライトのリストと列車のリスト)。最終結果では、都市間の移動に必要な乗り継ぎ回数が分かります。

例: CONNECT BY を使用した兄弟の順序付け

再帰的共通表式の欠点の 1 つは、特定の列値に基づいて兄弟間で結果を順序付けることができないことです。これは、connect by を使用した場合には可能です。例えば、New York からの目的地を出力するときに、同時に当該目的地までのチケットのコストなどの特定の値によって兄弟間で階層データを順序付ける必要がある場合、ORDER SIBLINGS BY 文節を指定することでそれを実現できます。

SELECT CONNECT_BY_ROOT departure AS origin, departure, arrival, 
      LEVEL level, price ticket_price
  FROM  flights
  START WITH departure = 'New York'
  CONNECT BY PRIOR arrival = departure
  ORDER SIBLINGS BY price ASC

この照会は次の情報を戻します。

表 3. 前述の照会の結果
ORIGIN	DEPARTURE	ARRIVAL	LEVEL	TICKET_PRICE
New York	New York	LA	1	330
New York	LA	Tokyo	2	530
New York	Tokyo	Hawaii	3	330
New York	New York	London	1	350
New York	London	Athens	2	340
New York	Athens	Nicosia	3	280
New York	New York	Paris	1	400®
New York	Paris	Rome	2	340
New York	Paris	Madrid	2	380
New York	Paris	Cairo	2	480

結果表は、起点都市を New York として可能なすべての目的地を示しています。すべての兄弟目的地 (同じ出発都市を起点とする目的地) が、チケット価格でソートされて出力されます。例えば、Paris からの目的地は、Rome、Madrid、および Cairo です。これらは、チケット価格の昇順で順序付けられて出力されます。なお、出力では、New York からの直行便の最初の目的地として New York 発 LA 行きが示されます。これは、London または Paris への直行便のチケット価格 (それぞれ 350 と 400) よりもチケット価格が低い (330) ためです。

例: CONNECT BY を使用した循環データ・チェック

再帰的プロセスで重要なことは、再帰的プログラム、再帰的照会のいずれであっても、再帰は有限でなければならないということです。有限でない場合、無限ループに入ることになります。 CONNECT BY は、照会に対して制御が効かなくなることがないように、常に無限再帰がないかを検査し、該当する循環を自動的に終了するという点で、再帰的共通表式とは異なります。

デフォルトでは、connect by は循環データを検出すると、SQL エラーの「SQ20451: Cycle detected in hierarchical query」を発行します。このエラーにより、照会が終了するため、結果が返されません。

結果が必要で、無限循環が停止すればよいだけの場合、CONNECT BY 文節で NOCYCLE キーワードを指定できます。これは、循環データに対してエラーを発行しないことを意味します。

CONNECT_BY_ISCYCLE 疑似列とともに NOCYCLE オプションを使用すると、循環データを検出し、必要に応じてデータを修正することができます。

以下の行を FLIGHTS 表に挿入すると、Paris 発 Cairo 行き、Cairo 発 Paris 行きという経路ができてしまうため、無限再帰が生じる可能性があります。

INSERT INTO flights VALUES ('Cairo', 'Paris', 'Atlantic', '1134', 440);

以下の照会では、NOCYCLE を指定した循環データの許容を示します。また、CONNECT_BY_ISCYCLE 疑似列を使用して循環行を識別し、関数 SYS_CONNECT_BY_PATH を使用して、目的地に至るすべての就航都市の Itinerary (旅程) ストリングを作成します。SYS_CONNECT_BY_PATH は、CLOB データ・タイプとして実装されているため、深い再帰を反映する大きな結果列が得られます。

SELECT CONNECT_BY_ROOT departure AS origin, arrival,
     SYS_CONNECT_BY_PATH(TRIM(arrival), ' : ') itinerary, CONNECT_BY_ISCYCLE cyclic
  FROM flights	
  START WITH departure = 'New York'
  CONNECT BY NOCYCLE PRIOR arrival = departure;

この照会は次の情報を戻します。

表 4. 前述の照会の結果
ORIGIN	ARRIVAL	ITINERARY	CYCLIC
New York	Paris	: Paris	0
New York	Rome	: Paris : Rome	0
New York	Cairo	: Paris : Cairo	0
New York	Paris	: Paris : Cairo : Paris	1
New York	Madrid	: Paris : Madrid	0
New York	London	: London	0
New York	Athens	: London : Athens	0
New York	Nicosia	: London : Athens : Nicosia	0
New York	LA	: LA	0
New York	Tokyo	: LA : Tokyo	0
New York	Hawaii	: LA : Tokyo : Hawaii	0

なお、多くの場合、循環データを反映する結果セット行は、START WITH 文節で循環のどこで開始するかによって異なります。

例: CONNECT BY での疑似列 CONNECT_BY_ISLEAF

再帰データを処理しているとき、再帰がそれ以上なくなる行を知りたい場合があります。つまり、階層内でリーフ行である行や子がない行についてです。

以下の照会では、どの目的地が最終目的地なのか (つまり、どの目的地に出発便がないのか) が分かります。CONNECT_BY_ISLEAF 疑似列は、リーフではない場合に 0、リーフの場合に 1 になります。また、WHERE 述部で CONNECT_BY_ISLEAF を指定して、リーフ行のみを表示することもできます。

SELECT CONNECT_BY_ROOT departure AS origin, arrival,
     SYS_CONNECT_BY_PATH(TRIM(arrival), ' : ') itinerary, CONNECT_BY_ISLEAF leaf
  FROM flights	
  START WITH departure = 'New York'
  CONNECT BY PRIOR arrival = departure;

この照会は次の情報を戻します。

表 5. 前述の照会の結果
ORIGIN	ARRIVAL	ITINERARY	LEAF
New York	Paris	: Paris	0
New York	Rome	: Paris : Rome	1
New York	Cairo	: Paris : Cairo	1
New York	Madrid	: Paris : Madrid	1
New York	London	: London	0
New York	Athens	: London : Athens	0
New York	Nicosia	: London : Athens : Nicosia	1
New York	LA	: LA	0
New York	Tokyo	: LA : Tokyo	0
New York	Hawaii	: LA : Tokyo : Hawaii	1

例: CONNECT BY での join 述部および where 文節の選択

多くの場合、データの階層性は 1 つの表に反映されますが、行の出力を完全に決定するために、その結果を追加の表に結合する必要が生じます。

connect by 照会では、DB2 for i でサポートされる任意のタイプの結合 (INNER JOIN、LEFT OUTER JOIN、LEFT EXCEPTION JOIN など) を使用できます。JOIN 文節を明示的に使用した場合、ON 文節に指定した述部がまず適用されてから、connect by 操作が適用され、connect by 照会内のすべての WHERE 文節は再帰後に適用されます。再帰的プロセス結果があまりにも早期に終了してしまうことがないように、WHERE 選択は、connect by の後に適用されます。

以下の照会では、ON_TIME_PERCENT が 90% を超えている、New York 発のすべてのフライトを検索しています。

SELECT CONNECT_BY_ROOT departure AS origin, departure, arrival,
      flight_number, on_time_Percent AS onTime
  FROM flights INNER JOIN flightstats ON flight_number = flight#	
  WHERE on_time_percent > 90
  START WITH departure = 'New York'
  CONNECT BY PRIOR arrival = departure;

この照会は次の情報を戻します。

表 6. 前述の照会の結果
ORIGIN	DEPARTURE	ARRIVAL	FLIGHT#	ONTIME
New York	Paris	Cairo	63	90.50
New York	Paris	Madrid	3256	92.00
New York	London	Athens	247	91.00
New York	Athens	Nicosia	2356	91.00
New York	LA	Tokyo	824	93.00
New York	Tokyo	Hawaii	94	92.00

この照会は、JOIN 構文を使用しなくても表現できます。照会最適化プログラムは、最初に処理する必要がある join 述部である述部を WHERE 文節から取り除き、再帰後に評価する残りのすべての WHERE 述部を残します。

SELECT CONNECT_BY_ROOT departure AS origin, departure, arrival, flight_number, on_time_percent AS onTime
  FROM flights, flightstats  
  WHERE  flight_number = flight# AND on_time_percent > 90
  START WITH departure = 'New York'
  CONNECT BY PRIOR arrival = departure;

この 2 番目の例では、WHERE 述部がより複雑な場合に、flights 表と flightstats 表との間の JOIN 述部を明示的に取り除き、ON 文節と WHERE 文節の両方を使用することで、最適化プログラムを支援する必要が生じることがあります。

再帰プロセスの一部として追加検索条件を適用する場合 (例えば、定時パーセンテージが 90% 未満のフライトを除外する場合)、join 述部と WHERE 文節を使用して派生表に結合を入れることで、結合結果を制御することもできます。

SELECT CONNECT_BY_ROOT departure AS origin, departure, arrival, flight_number, on_time_percent AS onTime
  FROM (SELECT departure, arrival, flight_number, on_time_percent
             FROM flights, flightstats  
             WHERE flight_number = flight# AND on_time_percent > 90) t1
  START WITH departure='New York'
  CONNECT BY PRIOR arrival = departure;

別の選択肢として、START WITH 文節および CONNECT BY 文節に選択述部を入れることができます。

SELECT CONNECT_BY_ROOT departure AS origin, departure, arrival, flight_number, on_time_percent AS onTime
  FROM flights, flightstats  
  WHERE flight_number = flight# 
  START WITH departure = 'New York' AND on_time_percent > 90
  CONNECT BY PRIOR arrival = departure AND on_time_percent > 90

この場合、定時統計が 90% を超える、New York からの直行便がないため、うまくいきません。再帰にシードするものがないため、照会から返される行はありません。

再帰的共通表式および再帰的ビューの使用

WITH destinations (origin, departure, arrival, flight_count) AS    
    (SELECT a.departure, a.departure, a.arrival, 1 
            FROM flights a
            WHERE a.departure = 'Chicago'
     UNION ALL
     SELECT r.origin, b.departure, b.arrival, r.flight_count + 1 
            FROM destinations r, flights b
            WHERE r.arrival = b.departure)
SELECT origin, departure, arrival, flight_count 
    FROM destinations

この照会は次の情報を戻します。

表 7. 前述の照会の結果
ORIGIN	DEPARTURE	ARRIVAL	FLIGHT_COUNT
Chicago	Chicago	Miami	1
Chicago	Chicago	Frankfurt	1
Chicago	Miami	Lima	2
Chicago	Frankfurt	Moscow	2
Chicago	Frankfurt	Beijing	2
Chicago	Frankfurt	Vienna	2
Chicago	Moscow	Tokyo	3
Chicago	Tokyo	Hawaii	4

この再帰的照会は 2 つの部分で構成されています。共通表式の最初の部分は、初期化全選択と呼ばれます。これは共通表式の結果セットの最初の行を選択します。この例では、シカゴから他の場所に直接移動する flights 表で、2 行選択します。また、フライトの行程数を、選択する行ごとで 1 に初期化しています。

再帰的照会の 2 番目の部分では、共通表式の現在の結果セットからの行と、オリジナル表からの他の行を結合しています。これは反復全選択 と呼ばれます。ここが再帰が導入される箇所です。結果セット用にすでに選択されている行は、表名として共通表式の名前を使用し、また列名として共通表式の結果列名を使用して、参照される点に注意してください。

この照会の再帰的な部分は、あらかじめユーザーが選択した各到着都市から、フライトが可能な行程を示す行が元の表から選択されている点です。最初に選択した行の到着都市は新規の出発都市になります。この再帰的選択の行ごとに、目的地へのフライト・カウントが 1 フライト分増えます。これらの新しい行は、共通表式の結果セットに追加され、さらに結果セット行を生成するために反復全選択へと追加されます。最終結果用のデータでは、フライトの合計数が実際に、目的地に到達するまでに必要となった再帰結合 (プラス 1) の合計数であることが分かります。

再帰的ビューは再帰的共通表式に非常によく似ています。先に取り上げた再帰的共通表式を次のような再帰的ビューに書き変えることができます。

CREATE VIEW destinations (origin, departure, arrival, flight_count) AS    
     SELECT departure, departure, arrival, 1 
            FROM flights 
            WHERE departure = 'Chicago'
     UNION ALL
     SELECT r.origin, b.departure, b.arrival, r.flight_count + 1 
            FROM destinations r, flights b
            WHERE r.arrival = b.departure)

このビュー定義の反復全選択部はビュー自体を参照しています。このビューからの選択は、先に解説した再帰的共通表式から取得した行と同じ行を返します。比較として、connect by 再帰では、SELECT が許可される任意の場所で使用できるため、ビュー定義に簡単に含めることができます。

例: 再帰的共通表式を使用した 2 つの出発都市

Chicago または New York 発のフライトを利用する場合の可能な目的地とその費用を検索するとします。

WITH destinations (departure, arrival, connections, cost) AS
    (SELECT a.departure, a.arrival, 0, price
            FROM flights a 
            WHERE a.departure = 'Chicago' OR
                  a.departure = 'New York'
     UNION ALL 
     SELECT r.departure, b.arrival, r.connections + 1,
                  r.cost + b.price 
            FROM destinations r, flights b 
            WHERE r.arrival = b.departure) 
SELECT departure, arrival, connections, cost 
       FROM destinations

この照会は次の情報を戻します。

表 8. 前述の照会の結果
DEPARTURE	ARRIVAL	CONNECTIONS	COST
Chicago	Miami	0	300
Chicago	Frankfurt	0	480
New York	Paris	0	400
New York	London	0	350
New York	Los Angeles	0	330
Chicago	Lima	1	830
Chicago	Moscow	1	1,060
Chicago	Beijing	1	960
Chicago	Vienna	1	680
New York	Madrid	1	780
New York	Cairo	1	880
New York	Rome	1	740
New York	Athens	1	690
New York	Tokyo	1	860
Chicago	Tokyo	2	1,740
New York	Nicosia	2	970
New York	Hawaii	2	1,190
Chicago	Hawaii	3	2,070

戻された各行について、結果には出発都市と、最終の目的地都市が示されています。この照会ではフライトの合計数ではなく、必要な接続数をカウントし、すべてのフライトにかかるコストを合計します。

例: 再帰的共通表式を使用した再帰に使用される 2 つの表

次はシカゴを起点に飛行機に加え、鉄道という別の移動手段を使用して到達できる都市の名前を検索するとします。

以下の照会は次の情報を戻します。

WITH destinations (departure, arrival, connections, flights, trains, cost) AS
  (SELECT f.departure, f.arrival, 0, 1, 0, price 
          FROM  flights f 
          WHERE f.departure = 'Chicago' 
   UNION ALL
   SELECT t.departure, t.arrival, 0, 0, 1, price 
          FROM trains t 
          WHERE t.departure = 'Chicago'
   UNION ALL
   SELECT r.departure, b.arrival, r.connections + 1 , r.flights + 1, r.trains, 
             r.cost + b.price
          FROM  destinations r, flights b  
          WHERE r.arrival = b.departure
   UNION ALL
   SELECT r.departure, c.arrival, r.connections + 1 ,
             r.flights, r.trains + 1, r.cost + c.price  
          FROM destinations r, trains c 
          WHERE r.arrival = c.departure) 
SELECT departure, arrival, connections, flights, trains, cost 
       FROM destinations

この照会は次の情報を戻します。

表 9. 前述の照会の結果
DEPARTURE	ARRIVAL	CONNECTIONS	FLIGHTS	TRAINS	COST
Chicago	Miami	0	1	0	300
Chicago	Frankfurt	0	1	0	480
Chicago	Washington	0	0	1	90
Chicago	Lima	1	2	0	830
Chicago	Moscow	1	2	0	1,060
Chicago	Beijing	1	2	0	960
Chicago	Vienna	1	2	0	680
Chicago	Toronto	1	1	1	340
Chicago	Boston	1	0	2	140
Chicago	Tokyo	2	3	0	1,740
Chicago	Hawaii	3	4	0	2,070

この例では、照会に初期化値を提供する共通表式に、飛行機用と鉄道用の 2 つの部分があります。どの結果行についても、直前の到着位置から次の可能目的地に至るまで、2 つの再帰参照があります。 1 つは飛行機を使って続行した場合で、もう 1 つは鉄道で続行した場合です。最終結果では、必要な接続の数、利用可能な空路の数と、陸路の数が表示されます。

例: 再帰的共通表式の DEPTH FIRST および BREADTH FIRST オプション

ここで解説する 2 つの例では、再帰により深さが先に処理されたか、幅が先に処理されたかを基に結果セットの行の順序が異なる点を確認できます。

注: 検索文節は、再帰的ビューでは直接サポートされていません。再帰的共通表式を含むビューを定義すると、この機能を実行できます。

幅あるいは深さを先に使用して結果を判別するためのオプションは、SEARCH BY 文節に指定した再帰結合列を基にした再帰的関係ソートになります。再帰で幅が先に処理されると、すべての子が先に処理され、続いてその子、さらにその子へと処理が移ります。再帰で深さが先に処理されると、子の全再帰的祖先のチェーンが、次の子に移る前に処理されます。

これらいずれの場合でも、深さ先行、あるいは幅先行の順序を、再帰プロセスがトラックし続けるために使用する追加の列名を指定します。この列は、行の順番を指定したとおりに戻すため、外部照会の ORDER BY 文節で使用される必要があります。この列が ORDER BY で使用されない場合、DEPTH FIRST または BREADTH FIRST 処理オプションは無視されます。

SEARCH BY 列で使用する列を選択することは重要です。意味のある結果を出すには、初期化全選択から結合するために、反復全選択で使用される列でなければなりません。この例では、ARRIVAL が使用する列です。

以下の照会は次の情報を戻します。

WITH destinations (departure, arrival, connections, cost) AS
    (SELECT f.departure, f.arrival, 0, price       
            FROM flights f 
            WHERE f.departure = 'Chicago'
     UNION ALL 
     SELECT r.departure, b.arrival, r.connections + 1,
                r.cost + b.price 
            FROM destinations r, flights b 
            WHERE r.arrival = b.departure) 
    SEARCH DEPTH FIRST BY arrival SET ordcol 
SELECT * 
   FROM destinations 
   ORDER BY ordcol

この照会は次の情報を戻します。

表 10. 前述の照会の結果
DEPARTURE	ARRIVAL	CONNECTIONS	COST
Chicago	Miami	0	300
Chicago	Lima	1	830
Chicago	Frankfurt	0	480
Chicago	Moscow	1	1,060
Chicago	Tokyo	2	1,740
Chicago	Hawaii	3	2,070
Chicago	Beijing	1	960
Chicago	Vienna	1	680

この結果データでは、シカゴ-マイアミの行から生成されたすべての目的地が、シカゴ-フランクフルト行の目的地の前にリストされていることが分かります。

次に、同じ照会を実行できますが、幅先行で配列した結果を要求できます。

WITH destinations (departure, arrival, connections, cost) AS 
    (SELECT f.departure, f.arrival, 0, price
            FROM flights f 
            WHERE f.departure = 'Chicago' 
     UNION ALL 
     SELECT r.departure, b.arrival, r.connections + 1,
                r.cost + b.price 
            FROM destinations r, flights b 
            WHERE r.arrival = b.departure) 
    SEARCH BREADTH FIRST BY arrival SET ordcol 
SELECT * 
  FROM destinations 
  ORDER BY ordcol

この照会は次の情報を戻します。

表 11. 前述の照会の結果
DEPARTURE	ARRIVAL	CONNECTIONS	COST
Chicago	Miami	0	300
Chicago	Frankfurt	0	480
Chicago	Lima	1	830
Chicago	Moscow	1	1,060
Chicago	Beijing	1	960
Chicago	Vienna	1	680
Chicago	Tokyo	2	1,740
Chicago	Hawaii	3	2,070

この結果データでは、シカゴから直接到着できる目的地がすべて、接続フライトの前にリストされていることが分かります。このデータは先に実行した照会の結果と同一ですが、順序が幅先行になります。ご覧のとおり、深さ優先または幅優先処理で使用される列の値に基づいて順序付けが行われていません。順序付けを行うには、再帰の CONNECT BY 形式で使用可能な ORDER SIBLINGS BY 構造を使用できます。

例: 再帰的共通表式を使用した循環データ

再帰的プロセスで重要なことは、再帰的プログラミング・アルゴリズム、再帰的データの照会のいずれであっても、再帰は有限でなければならないということです。有限でない場合、無限ループに入ることになります。 CYCLE オプションは、循環データに対する保護機能を備えています。このオプションでは、繰り返しの循環を終了させるだけでなく、循環データを検出しやすくする循環マーク標識を出力することも選択できます。

注: 循環文節は、再帰的ビューでは直接サポートされていません。再帰的共通表式を含むビューを定義すると、この機能を実行できます。

最後の例では、データで循環が起きていると想定します。表にもう 1 行追加することで、カイロからパリへのフライトと、パリからカイロへのフライトができました。この例のように、故意に循環データを作り出そうとしなくても、データの処理時に無限ループに入る照会が生成されるのは、よくあることです。

以下の照会は次の情報を戻します。

INSERT INTO FLIGHTS VALUES('Cairo', 'Paris', 'Euro Air', '1134', 440)


WITH destinations (departure, arrival, connections, cost, itinerary) AS
    (SELECT f.departure, f.arrival, 1, price, 
                CAST(f.departure CONCAT f.arrival AS VARCHAR(2000))
            FROM flights f  
            WHERE f.departure = 'New York'
     UNION ALL 
     SELECT r.departure, b.arrival, r.connections + 1 ,
                r.cost + b.price, CAST(r.itinerary CONCAT b.arrival AS VARCHAR(2000))
            FROM destinations r, flights b 
            WHERE r.arrival = b.departure) 
    CYCLE arrival SET cyclic_data TO '1' DEFAULT '0' 
SELECT departure, arrival, itinerary, cyclic_data  
      FROM destinations

この照会は次の情報を戻します。

表 12. 前述の照会の結果
DEPARTURE	ARRIVAL	ITINERARY	CYCLIC_DATA
New York	Paris	New York Paris	0
New York	London	New York London	0
New York	Los Angeles	New York Los Angeles	0
New York	Madrid	New York Paris Madrid	0
New York	Cairo	New York Paris Cairo	0
New York	Rome	New York Paris Rome	0
New York	Athens	New York London Athens	0
New York	Tokyo	New York Los Angeles Tokyo	0
New York	Paris	New York Paris Cairo Paris	1
New York	Nicosia	New York London Athens Nicosia	0
New York	Hawaii	New York Los Angeles Tokyo Hawaii	0

この例では、データ内の循環を検出するために使用する列として、ARRIVAL 列が CYCLE 文節に定義されています。循環が見つかると、特殊な列 (このケースでは CYCLIC_DATA) で、結果セットの循環行に、文字値「1」が設定されます。その他の行にはデフォルト値である「0」が入っています。ARRIVAL 列に循環が見つかると、それ以上データの処理は行われず、無限ループは発生しません。ご使用のデータに実際に循環参照があるかを確認するには、外部照会で CYCLIC_DATA 列を参照してください。述部 WHERE CYCLIC_DATA = 0 を追加することで、循環行の除外を選択できます。