11gR2再帰SQL(Oracleアドベントカレンダー 2012/12/20)
想定読者：
SQLを扱い始めたばかりの駆け出しデータベース/アプリ開発者の方。
普段はPostgreSQLを専門で扱っていますが、ご縁で書くことになりました。
サポート契約を持ってない人に公開ができて、業務上知り得たノウハウでもなく、かつ面白いネタ、
なかなか見つけるのが難しいですね。
はじめに
機能面でPostgreSQLがOracleより優れているところを探す場合、"Oracleは標準SQLに対応していない"
という点が良く出てきました。
具体的には、一昔前(Oracle8i)までは特にJOINが槍玉に上がっていましたがOracle9iから標準SQLに
準拠するようになり、Oracleの隙はどんどん無くなってきています。(でもOracle9iも10年前か・・・)
そのような中、重箱の隅に残っていた、「再帰SQLの標準SQL非対応」ですが、Oracle11gR2にて対応
となりました。本日はこのあたりについて書いてみたいと思います。
はじめに2
もうひとつこの記事を書きたかった理由はこの再帰SQLという方法は「知らなければ発見が難しい
ノウハウ」に(私の分類によると)分類されるためです。
エラーが発生する場合はそのメッセージで検索すれば何かしら情報が出てきますが、このような
ノウハウはちょっと検索が難しいです。なので、このアドベントカレンダーという、少しでも目に
触れることの大きい今回の機会にblogに書いておこうと思い立ちました。
この記事をきっかけに
「今までがんばってPL/SQL書いてたのに！」
「できないと思っていた！」
という方の生産性が少しでも上がると幸いです。
再帰SQLと聞いただけで脳味噌が拒絶反応を示すひとがいるかもしれません。(私が実際にそうなので0ではない。)
しかし、使い方によってはSQLを非常にシンプルに表現できる便利なSQLなので、ちょっと
覚えておきましょう。
・再帰SQLとはSQLの結果を使ってSQLを実行するためのSQLです。
・WITH句を使って実装します。
・基本的には初期値とループを抜けるための終了条件が必要です。
うーん、言葉で説明することが難しいため、簡単な例にて。
検証環境：RHEL5.5 Oracle 11.2.0.1.0
まずは小さなSQLから作っていきましょう。
1.WITH句で仮想テーブルを指定し
2.開始条件となるSQLを1つ書き
3.UNION ALLで仮想テーブルで使った表と
列を使うことで再帰を表現
4.終了条件に向かうための記述
5.終了条件
WITH rectab(col1) --1.仮想テーブルの作成
AS
(SELECT 0 from dual --2.開始条件
UNION ALL
SELECT col1+1 FROM rectab --4.終了条件に向かうためにCOL1に1を加える
WHERE col1 < 7) --5.終了条件
SELECT col1 FROM rectab;
ここでうっかり終了条件のWHERE句を付け忘れたり、終了条件に向かう処理を入れ忘れると
無限ループに入る
はずですが、
そこはかゆいところに手が届くOracleです。
WITH rectab(col1)
AS
(SELECT 0 from dual
UNION ALL
SELECT col1+1 FROM rectab) --where句忘れ
SELECT col1 FROM rectab;ERROR:
ORA-32044: 再帰的WITH問合せの実行中にサイクルが検出されました
レコードが選択されませんでした。
SQL> WITH rectab(col1)
AS
(SELECT 0 from dual
UNION ALL
SELECT col1 FROM rectab --col1のカウントアップ忘れ
WHERE col1 < 7)
SELECT col1 FROM rectab;
ERROR:
ORA-32044: 再帰的WITH問合せの実行中にサイクルが検出されました
レコードが選択されませんでした。
ちゃんと検知してくれます。素晴らしいですね。
では、C言語のお勉強でよく出てくるツリーの作成をしてみましょう。
WITH rectab(col1)
AS
(SELECT 0 from dual
UNION ALL
SELECT col1+1 FROM rectab
WHERE col1 < 7)
SELECT
RPAD(' ',7-col1)||'*'||LPAD(' ',col1*2)||'*' ctree
FROM rectab;
CTREE
--------------------------------------------------------------------------------
**
* *
* *
* *
* *
* *
* *
* *8行が選択されました。
少しアドベントカレンダーっぽくなりました。
では、ちょっと発展して、EMP表の上司部下の関係を表示するSQLを考えます。
定番のemp表の中身は以下のとおりです。
EMPNO ENAME MGR
---------- -------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7839 KING
7844 TURNER 7698
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
7788 SCOTT 7566
7876 ADAMS 7788
では実際のSQLを見ていきましょう。
WITH emp_data(ename,empno,mgr,l)
AS
(SELECT
ename, empno, mgr, 1 lvl
FROM emp WHERE mgr IS null --開始条件。mgrがNULLの人を探す
UNION ALL
SELECT emp.ename, emp.empno, emp.mgr, ed.l+1
FROM emp, emp_data ed
WHERE emp.mgr = ed.empno --終了条件 テーブルという有限な値と
--結合しているので今回はこれでOK
)
SEARCH DEPTH FIRST BY ename SET order_by
SELECT l,
LPAD(' ',2*l,' ')||ename nm
FROM emp_data
ORDER BY order_byL NM
-- ----------------
1 KING
2 BLAKE
3 ALLEN
3 JAMES
3 MARTIN
3 TURNER
3 WARD
2 CLARK
3 MILLER
2 JONES
3 FORD
4 SMITH
3 SCOTT
4 ADAMS
SEARCH DEPTH FIRST BY ename SET order_by
の1行がここで出てきた新しい構文です。SEARCHの後はBREADTHとDEPTHを選択できて、幅と深さの
どちらで並べ替えを行うのか選択できます。
今回はenameをDEPTH順に並べますよ、という指定をしています。
うっかり、BREADTHを指定するとどのようになるのでしょうか。
これは実行してみるとよく分かります。
WITH emp_data(ename,empno,mgr,l)
AS
(SELECT
ename, empno, mgr, 1 lvl
FROM emp WHERE mgr IS null
UNION ALL
SELECT emp.ename, emp.empno, emp.mgr, ed.l+1
FROM emp, emp_data ed
WHERE emp.mgr = ed.empno
)
SEARCH BREADTH FIRST BY ename SET order_by --変更したのはここだけ
SELECT l,
LPAD(' ',2*l,' ')||ename nm
FROM emp_data
ORDER BY order_by
/
L NM
-- ----------------
1 KING
2 BLAKE
2 CLARK
2 JONES
3 ALLEN
3 FORD
3 JAMES
3 MARTIN
3 MILLER
3 SCOTT
3 TURNER
3 WARD
4 ADAMS
4 SMITH
これでは親子関係を表示するには不適切ですね。
再帰SQLは例のような組織の構成を表現する他、部品管理(これを作るためにはネジAと、
ネジBと、パーツAが必要)など、意外に使うシーンはあります。あとはDB屋さん的には
SQLの実行計画なども階層問い合わせのように表現されます。
Oracleでは再帰SQLを以下のように書く必要がありました。当時は
再帰SQLというより階層問い合わせという呼び名が一般的であったかも
しれません。
SELECT ename,job,empno,mgr,level
FROM emp
START WITH job = 'PRESIDENT'
CONNECT BY PRIOR empno = mgr
しかし、このSTART WITH CONNECT BYという構文、Oracleのオリジナル構文です。なので、
アプリケーションを他のデータベースに移植するとこのSQLは実行できません。
(PostgreSQLには一時期、互換のためのcontribがありました)
with句を使った再帰SQLの方がより柔軟な対応ができますので、これからSQLを覚える人は
こちらを、START WITHで慣れている人もWITH句を使ったSQLを覚えておいたほうが良いかも
しれません。
まとめ
調子に乗って書いていたら結構長くなってきましたので今回はこの辺で。
本記事を読んでアプリケーションを書いている時に
「あれ、これ、もしかしたら再帰SQLで書けるかも？」
「11gR2だから再帰SQLを使おう」
と気づいていただける方がいらしたら幸いです。
PS:記事を書いた後にもっと詳しい素晴らしい記事があることを知りました。。
本編は入門記事ということでご容赦を。
http://www.oracle.com/technetwork/jp/articles/point4-155261-ja.html
明日はkingyokkunさんです。よろしくお願いしまーす。
