再帰の例: 部品表
部品表 (BOM) のアプリケーションは、多くの業務環境において一般的に必要になります。 BOM アプリケーションの再帰的共通表式の機能を示すため、部品とそれに関連する副部品、 そして各部品に必要な副部品の数量を示す表について考えてみます。
CREATE TABLE PARTLIST
(PART VARCHAR(8),
SUBPART VARCHAR(8),
QUANTITY INTEGER); PART SUBPART QUANTITY
-------- -------- -----------
00 01 5
00 05 3
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 14 8
07 12 8例 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 を使用した結果です)。
PARENT.SUBPART = CHILD.SUBPART無限ループが発生するこの例は、 意図したとおりにコーディングされていない場合であることは明らかです。 再帰サイクルが必ず終了するようにコーディングすることにも注意してください。
この例の照会によって得られる結果は、再帰的共通表式を使用しなくても、 アプリケーション・プログラム内で作成することができます。 しかし、そのような方法では、 すべての再帰レベルごとに新しい照会を開始する必要があります。 さらに、すべての結果をデータベースに入れ、 その結果を並べ替えることを、アプリケーションで行う必要があります。 そのような方法では、 アプリケーションのロジックが複雑になり、パフォーマンスはよくありません。 要約正展開やインデント正展開の照会など、その他の部品表の照会では、 アプリケーションのロジックがさらに複雑で効率の悪いものとなってしまいます。
例 2: 要約正展開
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 つの部品が異なる複数のロケーションで何回も使用される場合は、 もう 1 つ最終的な集計が必要になります。 これは、共通表式 RPL をグループ化し、 主要全選択の選択リストの中で SUM 集約関数を使用することによって行います。
PART SUBPART Total Qty Used
-------- -------- --------------
01 02 2
01 03 3
01 04 4
01 05 14
01 06 15
01 07 18
01 08 40
01 09 44
01 10 140
01 11 140
01 12 294
01 13 150
01 14 144この出力のうち、副部品が '06' の行に注目してください。 合計使用量の値 15 は、部品 '01' のための直接の数 3 と、 部品 '02' のための数 (6) に部品 '01' の数 (2) を掛けたものとを加えた数です。
例 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 を使っています。 初期化全選択では、LEVEL 列の値を 1 に初期化しています。 それ以降の全選択では、親のレベルに 1 ずつ加算します。 次に、結果のレベル数を制御するため、2 番目の全選択に、 親のレベルが 2 未満でなければならないという条件が組み入れられています。 これによって、2 番目の全選択では、 子の処理が 2 次レベルまでしか行われないことになります。
PART LEVEL SUBPART QUANTITY
-------- ----------- -------- -----------
01 1 02 2
01 1 03 3
01 1 04 4
01 1 06 3
02 2 05 7
02 2 06 6
03 2 07 6
04 2 08 10
04 2 09 11
06 2 12 10
06 2 13 10