SQL 解説書

例 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 (再帰的 PARTLIST) の中に入れられることになります。 この例の場合、UNION は常に UNION ALL でなければなりません。

UNION の第 2 オペランド (全選択) は、 RPL を使って、副部品の副部品を計算しています。 これは、FROM 文節で共通表式 RPL とソース表 (CHILD: 子) の部品を、 RPL (PARENT: 親) に含まれている現行の結果の副部品に結び付けることによります。 この結果は、再度 RPL に入れられます。 このようにして、UNION の第 2 オペランドは、子が存在しなくなるまで繰り返し使用されます。

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

照会結果は、次のようになります。

  PART     SUBPART  QUANTITY
  -------- -------- -----------
  01       02                 2
  01       03                 3
  01       04                 4
  01       06                 3
  02       05                 7
  02       06                 6
  03       07                 6
  04       08                10
  04       09                11
  05       10                10
  05       11                10
  06       12                10
  06       13                10
  07       12                 8
  07       14                 8

この結果では、部品 '01' から '02' へ、そこからさらに '06' へと進むようになっています。 さらに、部品 '06' へは、'01' から直接に 1 回、 '02' から 1 回の計 2 回達することに注意してください。 しかしこの出力では、 その副構成要素が 1 回しかリストに現れないようになっています (これは SELECT DISTINCT を使用した結果です)。

再帰的共通表式では、 無限ループ になる可能性を必ず考慮に入れてください。 この例で、親表と子表を結合する第 2 オペランドの探索条件を以下のようにコーディングしたとすると、 無限ループが作成されることになります。

   PARENT.SUBPART = CHILD.SUBPART

無限ループが発生するこの例は、 意図したとおりにコーディングされていない場合であることは明らかです。 しかし、再帰サイクルが必ず終了するようにコーディングすることにも注意を払ってください。

この例の照会によって得られる結果は、再帰的共通表式を使用しなくても、 アプリケーション・プログラム内で作成することができます。 しかし、そのような方法では、 すべての再帰レベルごとに新しい照会を開始する必要があります。 さらに、すべての結果をデータベースに入れ、 その結果を並べ替えることを、アプリケーションで行う必要があります。 そのような方法では、 アプリケーションのロジックが複雑になり、パフォーマンスはよくありません。 合計型展開やインデント型展開の照会など、その他の部品構成表の照会では、 アプリケーションのロジックがさらに複雑で効率の悪いものとなってしまいます。


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]