20代SE 忘備録

普段自分が考えたことや学んだことを忘れないように書いていきます。

Oracle SQL 再帰問い合わせ-部品構成表の展開方法について

Oracle SQL 階層問い合わせ-部品構成表の展開方法について

 Oracle SQLを使用した階層問い合わせについてのメモ

 

下記のような部品構成表(Bill of Materials、以下BOM)が与えられているとします。

f:id:SY0807J:20160403091420j:plain

最終製品Aを1個作るのに中間品B、部品Cがそれぞれ2個、1個必要なことがわかります。また中間品Bを1個つくるのに部品Dが3/2個必要であります。

このとき完成品Aに対してすべての部品の必要数を求める方法として、再帰問い合わせによる方法があります。では下記表を使用して、抽出してみます。

f:id:SY0807J:20160403092816j:plain

 

再帰問い合わせ

WITH X(PARENT, CHILD, QTY)        ・・・①

AS (SELECT BT.PARENT, BT.CHILD, BT.QTY
    FROM BOM_TEST BT
    WHERE BT.PARENT = 'A'         ・・・②
    UNION ALL
    SELECT BT2.PARENT, BT2.CHILD, BT2.QTY * X.QTY
    FROM BOM_TEST BT2, X
    WHERE X.CHILD = BT2.PARENT)     ・・・③
SELECT * FROM X;

 【結果】

f:id:SY0807J:20160403101309j:plain

 

ここで、①はWITH句による仮想VIEWを定義しており、このVIEWに対して再帰的に問い合わせを実行します。

②では、まず初期条件を入力するのですが、ここでは最終製品がAとわかっているので、それを条件とし子供を探索していきます。

③では、仮想VIEWの子とBOM_TESTの親が等しいという条件で結合しています。

結果として中間品、部品の所要量がでます。つまりAを1個つくるのにB,C,Dがそれぞれ2個、1個、3個必要であるとわかります。