再帰的共通表式の例

CREATE TABLE PARTLIST
     (PART VARCHAR(8),
      SUBPART VARCHAR(8),
      QUANTITY INTEGER);

例 1: 単一レベルの展開:

単一レベルの爆発は、「01 '」に識別された部分を構築するために必要な部分は何ですか？ "。 このリストには、直接の副部品、副部品の副部品などが入ります。 しかし、ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません。

WITH RPL (PART, SUBPART, QUANTITY) AS
     (SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
        FROM PARTLIST ROOT
        WHERE ROOT.PART = '01'
     UNION ALL
        SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
           FROM RPL PARENT, PARTLIST CHILD
           WHERE PARENT.SUBPART = CHILD.PART)
SELECT DISTINCT PART, SUBPART, QUANTITY
    FROM RPL
    ORDER BY PART, SUBPART, QUANTITY;

前の照会には、この照会の再帰的パーツを表し、RPL という名前で識別される 共通表式が含まれています。 この照会には、再帰的共通表式の基本的なエレメントが示されています。

初期化全選択と呼ばれる、UNION の第 1 オペランド (全選択) によって、 パーツ「01」の直接の構成部品が得られます。 この全選択の FROM 文節は、ソース表を参照しますが、 それ自体 (この場合 RPL) を参照することはありません。 この最初の全選択の結果は、共通表式 RPL に入ります。 この例の場合、UNION は常に UNION ALL でなければなりません。

UNION の第 2 オペランド (全選択) は、FROM 文節を用いて構成部品を構成する構成部品を 計算する RPL を使用して、ソース表 (子) から RPL (親) に含まれる現行結果の 構成部品に部品を結合して、共通表式 RPL およびソース表 PARTLIST を参照します。 結果は、次に再度 RPL に戻されます。 UNION の第 2 オペランドは、 構成部品が存在しなくなるまで繰り返し使用されます。

この照会の主要な全選択の SELECT DISTINCT では、 同じ部品/副部品が 2 回以上リストに現れることがないようにしています。

照会の結果を、次の表に示します。

結果を見て、部品「01」に、 構成部品「06」などを含む構成部品「02」が含まれていることを調べます。 さらに、パーツ「06」は、1 回は直接に部品「01」経由、2 回目は 部品「02」経由で、2 回使われていることに注意してください。 しかし、この出力では、 部品「06」の構成部品がリストされるのは 1 回のみです (これは、SELECT DISTINCT を使用した結果です)。

再帰的共通表式では、 無限ループを導入できることに留意してください。 この例で、親と子の表を結合する第 2 オペランドの検索条件が、 以下のようにコーディングされた場合、 無限ループが作成されます。

WHERE PARENT.SUBPART = CHILD.SUBPART

この無限ループは、正しい意図でないコーディングによっても作成されます。 再帰サイクルの有限で終了するように、 コーディング内容を慎重に決める必要があります。

再帰的共通表式を使用せずに、この例で作成される結果を アプリケーション・プログラム内で作成しても構いません。 しかし、この種のアプリケーションでは、 再帰のレベルごとに異なる照会のコーディングを必要とします。 さらには、このアプリケーションは、すべての結果をデータベースに戻して、 最終結果を順序付けする必要があります。 そのような方法では、 アプリケーションのロジックが複雑になり、パフォーマンスはよくありません。 その他の部品表照会 (要約型部品展開および階層型部品展開) を行った場合、このお客様作成のアプリケーション・ ロジックは、さらに困難かつ非効率になります。

例 2: 爆発の要約 :

要約された爆発は、「パート '01'を構築するのに必要な各部分の総量は何ですか？」という質問に答えます。 単一レベルの部品展開との主な相違点は、 数量を集約する必要です。 単一レベルの部品展開では、 部品に必要な構成部品数量が必要に応じて示されます。 部品「01」の組み立てに必要な各構成部品数量は示しません。

WITH RPL (PART, SUBPART, QUANTITY) AS
           (
              SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
                 FROM PARTLIST ROOT
                 WHERE ROOT.PART = '01'
             UNION ALL
              SELECT PARENT.PART, CHILD.SUBPART, 
                     PARENT.QUANTITY*CHILD.QUANTITY
                 FROM RPL PARENT, PARTLIST CHILD
                 WHERE PARENT.SUBPART = CHILD.PART
           )
SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
   FROM RPL
   GROUP BY PART, SUBPART
   ORDER BY PART, SUBPART;

前の照会で、RPL という名前で識別される、 再帰的共通表式の UNION の第 2 オペランドの選択リストは、 数量の総計を示します。 使用される各構成部品数を判別するには、親の数量と、 親当たりの子の数量を乗算します。 1 つの部品が異なる複数のロケーションで何回も使用される場合は、 もう 1 つ最終的な集計が必要になります。 これは、共通表式 RPL 内の部品と構成部品をグループ分けし、 主全選択の選択リストの SUM 列関数を使用して行われます。

照会の結果を、次の表に示します。

構成部品「06」の合計数について考えます。 15 の値は、部品「01」に対して直接 3 の数量、 ならびに部品「01」を 2 回必要とする部品「02」に対して 6 の数量から導き出されます。

例 3: 深さの制御 :

再帰問い合わせの深さを制御して、「パーツ '01'を構築するために必要なパーツの最初の2つのレベルは何ですか？」という質問に答えることができます。 この例をやさしくするために、 各部品のレベルを結果表に含めました。

WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS
       (
         SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
           FROM PARTLIST ROOT
           WHERE ROOT.PART = '01'
     UNION ALL
         SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
           FROM RPL PARENT, PARTLIST CHILD
           WHERE PARENT.SUBPART = CHILD.PART
             AND PARENT.LEVEL < 2
        )
SELECT PART, LEVEL, SUBPART, QUANTITY
  FROM RPL;

このクエリは、例1のクエリと似ています。 各サブパートは元の部分からのレベルをカウントするために列レベルが導入されます。 初期化全選択では、LEVEL列の値は1に初期設定されます。 後続の全選択では、親表からのレベルは1で増加します。 結果のレベル数を制御するために、2番目の全選択には、親のレベルが2未満でなければならないという条件が含まれます。 これによって、2 番目の全選択では、 子の処理が 2 次レベルまでしか行われないことになります。

照会の結果を、次の表に示します。