「ORACLE SQL」カテゴリーアーカイブ

ORACLE SQL

ORACLE 11gR2 SQLの再帰(再帰With句)について 1

コメントする

いつも一瞬戸惑ってしまう再帰。皆さんはいかがでしょうか。

私の場合、あまり使うことはないので、よく忘れてしまうのですが、
使うことが発生し使いだすと、いつも「便利だな〜」と思います。
メモがてら、再帰についていくつかサンプルを記載したいと思います。

— 部品管理で再帰を利用する。
たとえば、車で考えてみます。
細かく言えば、複雑になるので次のように考えました。

車
    エンジン
      エンジン用ねじ
        アルミニウム
        鉄
      エンジン用鉄
    ハンドル
    　ハンドル用ねじ
    　ハンドル用鉄
    ブレーキペダル
    　ブレーキペダル用ねじ
    　ブレーキペダル用鉄
    アクセルペダル
    　アクセルペダル用ねじ
    　アクセルペダル用鉄
    車体組み立て用ねじ

正直こんな単純な構成はほぼないのですが、あくまで再帰の
抽出方法を確認するだけなので、ご了承いただければと思います・・。

上記は、次のようにイメージしてください。
車は、エンジン、ハンドル、ブレーキペダル、アクセルペダル
から成り立っています。
エンジンは、エンジン用ねじと、エンジン用鉄から作られます。

というような読み方で、それぞれの部材の親子関係を示しています。

その次に、上記の関係をDBに登録してみます。
日本語で分かりやすくテーブルを作るとこんな感じでしょうか。

CREATE TABLE 構成表 (
 親 VARCHAR2(100)
,子 VARCHAR2(100)
);

-- データも次のように登録します。

INSERT INTO 構成表 (親 , 子)
  VALUES('車','エンジン');

INSERT INTO 構成表 (親 , 子)
  VALUES('車','ハンドル');

INSERT INTO 構成表 (親 , 子)
  VALUES('車','ブレーキペダル');

INSERT INTO 構成表 (親 , 子)
  VALUES('車','アクセルペダル');

INSERT INTO 構成表 (親 , 子)
  VALUES('車','車体組み立て用ねじ');

INSERT INTO 構成表 (親 , 子)
  VALUES('エンジン','エンジン用ねじ');

INSERT INTO 構成表 (親 , 子)
  VALUES('エンジン','エンジン用鉄');

INSERT INTO 構成表 (親 , 子)
  VALUES('エンジン用ねじ','鉄');

INSERT INTO 構成表 (親 , 子)
  VALUES('エンジン用ねじ','アルミニウム');

INSERT INTO 構成表 (親 , 子)
  VALUES('ハンドル','ハンドル用ねじ');

INSERT INTO 構成表 (親 , 子)
  VALUES('ハンドル','ハンドル用鉄');

INSERT INTO 構成表 (親 , 子)
  VALUES('ブレーキべダル','ブレーキペダル用ねじ');

INSERT INTO 構成表 (親 , 子)
  VALUES('ブレーキペダル','ブレーキペダル用鉄');

INSERT INTO 構成表 (親 , 子)
  VALUES('アクセルペダル','アクセルペダル用ねじ');

INSERT INTO 構成表 (親 , 子)
  VALUES('アクセルペダル','アクセルペダル用鉄');

では、階層ごとに出してみましょう。
車がどのような構成で、作られているか
見ることにしましょう。
見やすいように、 親 -> 子 という表現にすることを考えたいと思います。
親が車の構成はどのような感じでしょうか。

まず考え方としては、
http://docs.oracle.com/cd/E16338_01/server.112/b56299/statements_10002.htm#BCEJGIBG
参照にしています。

今回は、再帰With句という ORACLE 11gR2から導入された機能を使います。

WITH VREC (構成 , 親  , 子) AS
(
   SELECT 親 || ' -> '|| 子 , 親, 子 FROM 構成表
   WHERE 親 = '車' AND 子 = 'エンジン'
UNION ALL
   SELECT V.構成 || ' -> '|| K.子 , K.親, K.子 FROM 構成表 K , VREC V
   WHERE V.子 = K.親
)
SELECT 構成 FROM VREC

 

構成
---------------------------------------------------
車 -> エンジン
車 -> エンジン -> エンジン用ねじ
車 -> エンジン -> エンジン用鉄
車 -> エンジン -> エンジン用ねじ -> 鉄
車 -> エンジン -> エンジン用ねじ -> アルミニウム

どうでしょうか、下位の構成まですべて出たと思います。
仕組みを簡単に書きたいと思います。
再帰With 句の中で UNION ALL で分かれます。
上の方を、非再帰項といい、下を再帰項といいます。

動きを見ていきましょう。
次の順番で動いていきます。

1.非再帰項を実行します。ここでは、車とエンジンのデータを取得します。
2.再帰項を1.のデータをもとに実行します。エンジンが親となり、そのデータを表示します。
3.再帰項を2.のデータをもとに実行します。エンジン用ねじが親となり、そのデータを表示します。

いかがでしょうか。
再帰は、まだまだ面白いことができます。
今日はこの辺にしてまた、次回追加で説明したいと思います。