WITH句を使って一時的なビューの作成と利用、再帰的処理の呼び出しなど

既存データに対して何らかの変換が必要になってデータコンバートを行おうとすると、時々、システムとしては不要だけど、データコンバートの時だけビューが欲しいといった場合があります。

この場合、データコンバートの時だけ必要なビューをわざわざCREATEを使って作成したくないのが普通で、作成してしまった場合、使用後に作成したビューを削除し忘れたりすることがあり、不要なビューがそのまま残ってしまうと、場合によっては管理が煩雑になりますし、プロジェクトを離れることになったら、引継ぎした担当者から文句を言われたりすることがあります。

そういった事態を避けるためにも、Oracleでは一時的にビューを利用したい場合、WITHという便利なものがあるので、これを使います。

以下はWITHを使ったSQLの例になります。

WITH 
ORDERS AS (
        SELECT 1 AS ID, '注文A' AS NAME FROM DUAL
  UNION SELECT 2 AS ID, '注文B' AS NAME FROM DUAL
),
ORDER_PRODUCTS AS (
        SELECT 1 AS ORDER_ID, 1 AS ID, '商品A' AS NAME, 100 AS PRICE FROM DUAL
  UNION SELECT 1 AS ORDER_ID, 2 AS ID, '商品A' AS NAME, 200 AS PRICE FROM DUAL
  UNION SELECT 1 AS ORDER_ID, 3 AS ID, '商品A' AS NAME, 300 AS PRICE FROM DUAL
)
SELECT 
  o.ID AS O1_ID,
  op1.ID AS P1_ID, op1.NAME AS P1_NAME, op1.PRICE AS P1_PRICE,
  op2.ID AS P2_ID, op2.NAME AS P2_NAME, op2.PRICE AS P2_PRICE,
  op3.ID AS P3_ID, op3.NAME AS P2_NAME, op3.PRICE AS P3_PRICE

FROM ORDERS o

LEFT JOIN ORDER_PRODUCTS op1 ON op1.ORDER_ID = o.ID AND op1.ID = 1
LEFT JOIN ORDER_PRODUCTS op2 ON op2.ORDER_ID = o.ID AND op2.ID = 2
LEFT JOIN ORDER_PRODUCTS op3 ON op3.ORDER_ID = o.ID AND op3.ID = 3

WHERE o.ID = 1;

「ORDERS」が注文データ、「ORDER_PRODUCTS」が注文された商品のデータという位置付けで、上記のSQLは、注文Aの商品データを商品ごとに列にして、1行で取得する内容となっています。

ちなみに、このSQLを実行すると以下のような結果になります。

     O1_ID      P1_ID P1_NAME   P1_PRICE      P2_ID P2_NAME   P2_PRICE      P3_ID P2_NAME   P3_PRICE
---------- ---------- ------- ---------- ---------- ------- ---------- ---------- ------- ----------
         1          1 商品A          100          2 商品A          200          3 商品A          300

仕事で利用した際は、データコンバート用に1000行近いPL/SQLを作成しなければならなくなり、テーブルから何度も同じサブクエリでデータを取得しないといけなかったので、このWITHは非常に便利でした。

上記のSQLではWITHを単純なビューとして利用しましたが、WITHを使う要件としては、SQLで再帰的な処理をしたいといった理由が多いと思います。

以下にWITHで再帰的な呼び出しを行う簡単な例を示します。

WITH 
CATEGORIES AS (
        SELECT   1 AS ID,  0 AS PARENT_ID, 'A'   AS NAME FROM DUAL
  UNION SELECT  11 AS ID,  1 AS PARENT_ID, 'A1'  AS NAME FROM DUAL
  UNION SELECT  12 AS ID,  1 AS PARENT_ID, 'A2'  AS NAME FROM DUAL
  UNION SELECT 121 AS ID, 12 AS PARENT_ID, 'A21' AS NAME FROM DUAL
  UNION SELECT  13 AS ID,  1 AS PARENT_ID, 'A3'  AS NAME FROM DUAL
  UNION SELECT 131 AS ID, 13 AS PARENT_ID, 'A31' AS NAME FROM DUAL
  UNION SELECT 132 AS ID, 13 AS PARENT_ID, 'A32' AS NAME FROM DUAL
  UNION SELECT   2 AS ID,  0 AS PARENT_ID, 'B'   AS NAME FROM DUAL
  UNION SELECT  21 AS ID,  2 AS PARENT_ID, 'B1'  AS NAME FROM DUAL
  UNION SELECT 211 AS ID, 21 AS PARENT_ID, 'B11' AS NAME FROM DUAL
  UNION SELECT 212 AS ID, 21 AS PARENT_ID, 'B12' AS NAME FROM DUAL
),
CATEGORY_LEVEL(id, parent_id, name, lvl) AS (
    SELECT c.ID, c.PARENT_ID, c.NAME, 1 AS LVL 
    FROM CATEGORIES c WHERE PARENT_ID = '0'
    UNION ALL 
    SELECT c.ID, c.PARENT_ID, c.NAME, lvl + 1 AS LVL 
    FROM CATEGORIES c, CATEGORY_LEVEL ct 
    WHERE c.PARENT_ID = ct.ID
)
SELECT *
FROM CATEGORY_LEVEL;

上記のSQLでは、WITHで定義したCATEGORY_LEVELを呼び出すと、CATEGORIESのデータを再帰的に取得して、取得したデータに階層レベル（LVL）の情報を付与して結果データを返してくれます。

実行結果は以下の通り。

        ID  PARENT_ID NAM        LVL
---------- ---------- --- ----------
         1          0 A            1
         2          0 B            1
        11          1 A1           2
        12          1 A2           2
        13          1 A3           2
        21          2 B1           2
       121         12 A21          3
       131         13 A31          3
       132         13 A32          3
       211         21 B11          3
       212         21 B12          3

パッと見は理解しにくいですが、使いどころが時々あるので、こういうことができるんだというのを知っておくといいと思います。