今回は再帰コールの解説の最後として、再帰コールの注意点について説明します。 eラーニング、LMS（学習管理システム）、高度IT人材ならiStudy. What is going on with this article? 再帰クエリのイメージを図解してくれてる方 がいます。これでイメージできるかと。 Oracleの階層問い合わせで、親の数量と親に対する数量を元に子の数量を順次計算していくことは可能でしょうか？以下のようなテーブルで最上位の数量が変更された場合、子の数量を再計算したいです。 更新前 ID親ID親に対する必要数数量ID12ID2ID124ID3ID2312ID4ID214更新後（ 回答 0. 中国語 (繁体字) / 繁體中文 車がどのような構成で、作られているか PostgreSQL, Oracle, SQL Server, MySQL(8.0～), SQLite ※この記事で記載するSQLはすべてPostgreSQLで動くものです。 再帰クエリとは. クロアチア語 / Hrvatski スロベニア語 / Slovenščina 星和テクノロジー株式会社 まず1行目のとおり、SELECTの代わりにWITH句を使い、データセット「emp_data」を指定します。 チェコ語 / Čeština そして、UNION ALL文を実行し、最初の問合せでわかったマネジャーの下で働く全従業員を探します。 When you sign in to comment, IBM will provide your email, first name and last name to DISQUS. Help us understand the problem. 私の場合、あまり使うことはないので、よく忘れてしまうのですが、 ナイーブツリーのテーブル更新後、続けて経路列挙モデルのテーブルを全DELETE/INSERTします。, ※経路列挙モデルのテーブルはVIEWで作り、DELETE/INSERT自体不要にするのもよいと思います。そこは各自柔軟に解釈ください。, これで、「更新が楽なナイーブツリー」と「参照が楽な経路列挙」の各モデルの長所を生かせました。, ここからは、CRUDのケース別のクエリサンプルをご紹介します。 再帰は、まだまだ面白いことができます。 03-6427-7380 受付中. 細かく言えば、複雑になるので次のように考えました。, 正直こんな単純な構成はほぼないのですが、あくまで再帰の 見やすいように、 親 -> 子 という表現にすることを考えたいと思います。 ここでは、WITH句を使って従業員の上下関係を階層構造で表してみます。 仕組みを簡単に書きたいと思います。 この再帰的問合せは、常に2つの部分から成り立ち、それらの間にUNION ALLが入ります。 3.再帰項を2.のデータをもとに実行します。エンジン用ねじが親となり、そのデータを表示します。, いかがでしょうか。 投稿 2020/10/28 11:17 ・編集 2020/10/28 14:10. 日本語で分かりやすくテーブルを作るとこんな感じでしょうか。, では、階層ごとに出してみましょう。 IBM Knowledge Center で検索する, IBM Knowledge Center は JavaScript を使用します。 スクリプトが使用不可になっているか、ご使用のブラウザーではサポートされていません。 JavaScript を使用可能にし、再試行してください。. ギリシャ語 / Ελληνικά 再帰関数ってコーディングするとなんかかっこよくないですか？ PostgreSQL, Oracle, SQL Server, MySQL(8.0～), SQLite, 下のようにパスの構造がわからなかったデータ構造から、 追記 Orlofsky様 表に誤りがありました、申し訳ないです。 create tableとinsert tableも追記させて頂きます。 1階層目のOYAと3階層め … 中国語 (簡体字) / 简体中文 http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring, Point2： Analytics are the coolest thing to happen to SQL, since the keyword SELECT 【分析関数】, Point3： Execute on a directory 【圧縮ファイルでの外部表】, Point4： Recursive Subquery Factoring 【再帰的副問合せのファクタリング】, Point5： Improved Time Travel 【「タイムトラベル」の改良】, Point7： Deferred Segment Creation 【非同期セグメント作成】, Point8： Flash Cache 【Database Smart Flash Cache】, Point10： Edition-based redefinition 【エディション・ベースの再定義】, Oracle 11g R2の最新機能についてもっと知りたい方は、OTNセミナー オンデマンドへ！. Why not register and get more from Qiita? RECURSIVEのキーワードがいらないなど、RDBMSにより書き方の差は若干ありますが、ほぼ上記のように書けばよいです。, このときにlayer(階層)や、絶対パス(full_path)などのデータを作るとその後の参照に役立ちます。 By commenting, you are accepting the SQL初心者にとっては、CONNECT BY句よりも簡単だと思いますが、長年CONNECT BY句を使ってきた方は、慣れるまでやや戸惑うかもしれません。 スロバキア語 / Slovenčina この問合せを使って、「数独」のパズルを解くこともできます。アントン・シファー（Anton Scheffer）という開発者が、「『数独』の空欄に入る数値をSQLで解いてみた」のだそうです（ http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring参照）。たいへん興味深い使い方です。もちろん、皆さんが運用しているシステムで数独を解くような機会はまずないでしょうが、この問合せで何ができるかという観点ではおもしろいと思います。, この新しい機能についてもっと詳しく知りたい方は、「Oracle Database SQL言語リファレンス, 11gリリース2（11.2）」の19章「SQL文：SAVEPOINTからUPDATE」をお読みください。, Oracle Database 11g Release2の新機能は、200以上あります。トムがご紹介しきれなかった新機能を、日本オラクルの技術陣が詳しくセミナーでご紹介しています。詳しくは資料で。 インターワンプレイス烏丸Ⅱ 3F ルーマニア語 / Română 参照にしています。, 今回は、再帰With句という ORACLE 11gR2から導入された機能を使います。, どうでしょうか、下位の構成まですべて出たと思います。 SELECT文の重複する処理のWITH句による共通化; WITH句を複数宣言する方法; WITH句を再帰的に使った連番生成の方法; WITH句を再帰的に使った連続日付の生成方法; 連続日付に関連してDATE型について; おすすめ書籍. たとえば、車で考えてみます。 That information, along with your comments, will be governed by 見ることにしましょう。 ポルトガル語 / ブラジル/Brazil / Português/Brasil 更新はナイーブツリーモデル、参照は経路列挙モデルが担当します。 カザフ語 / Қазақша 最初の部分は、CONNECT BY句でのSTART WITHの部分にあたるものです。これは階層の最上位を指します。 この例の照会結果を示すために、partlist 表には次のようなデータが入っているものとします。 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 エンジンは、エンジン用ねじと、エンジン用鉄から作られます。, その次に、上記の関係をDBに登録してみます。 「ナイーブツリーでデータ更新し、参照は再帰クエリの経路列挙モデルでやる」というのが私のおすすめです。, なので！ イタリア語 / Italiano DISQUS’ privacy policy. 今日はこの辺にしてまた、次回追加で説明したいと思います。, 〒6008413 Oracle Database 11g R2では、従来のCONNECT BY句による再帰的問合せに加え、ANSI SQLに準拠したWITH句を使用した再帰的問合せが使えるようになりました。 SQL初心者にとっては、CONNECT BY句よりも簡単だと思いますが、長年CONNECT BY句を使ってきた方は、慣れるまでやや戸惑うかもしれませ … Oracle SQL SELECT7 : WITH句を使ったSELECT文 . タイ語 / ภาษาไทย ベトナム語 / Tiếng Việt. ブルガリア語 / Български レコード1がわかったら、そのレコードを次の問合せに入力します。 親子条件を、親のID = 子のOyaID として木構造なデータを探索します。
2000年にAsk Tomブログ( http://asktom.oracle.com ) を開設して以来10年にわたり、全世界のオラクル技術者のありとあらゆる質問に答え、データベース技術の活用を世に広めてきた世界的に有名なエバンジェリスト。. そうです。若干利用に制限があります。 フィンランド語 / Suomi 3行目では、mgrがNULLの従業員を探します。その結果、表empではKINGであることがわかります。これがレコード1です。 ドイツ語 / Deutsch 木構造なデータの探索 親子条件を満たす行を再帰的に出力. 百聞は一見に如かず。 まずはデータ準備. から成り立っています。 …ではなく、ここから、この再帰クエリの使いどころをご説明します。, 上記元データとして使用した object_tree のように、自分の親のレコードIDを持つタイプのデータ構造を「自己参照型テーブル構造」だとか「ナイーブツリー」と呼ばれます。この「ナイーブツリー」は SQLアンチパターン の一つとして有名です。, ナイーブツリーは更新は楽というメリットよりも、デメリットが大きいということでアンチパターンとされています。, この、ナイーブツリーというSQLアンチパターンの解決方法として、経路列挙モデル、入れ子集合モデル、閉包テーブルモデルなどあります。 マケドニア語 / македонски 今日は再帰クエリをご紹介します。, ※少なくとも以下のRDBMSの最新バージョンは再帰クエリに対応しています。 再帰クエリの結果はテーブル（もしくはマテリアライズドビュー）に格納して、簡単に参照することを推奨します。, つまり私は、「ナイーブツリーと経路列挙モデルの2テーブルを持つ」ことがいいと思います。 ノルウェー語 / Norsk Please note that DISQUS operates this forum. ※list_object_treeのDDLとDELETE/INSERTのクエリは末尾に記載します。, 今回推奨のナイーブと経路列挙の二重持ちですが、「1テーブルのときよりデータ量が2倍」、「DB更新で毎回DELETE/INSERTをする」というのが気になるかと思います。 ほとんどのArm IPが試し放題でスタートアップは年会費無料！？Arm Flexible Access, 再帰クエリというものがある。再帰クエリ使用時に階層番号や絶対パスをSELECT項目で作ると便利。, 再帰クエリは毎回書くのはややこしいからテーブルにデータを持っておくと便利（使いやすいデータ構造になる）。, ナイーブツリーの構造は再帰クエリがあるからそこまでアンチパターンとは（私は）思わない。, you can read useful information later efficiently. 実はSQLでも再帰があります。 ハンガリー語 / Magyar 受付時間 9:00〜18:00 (土日祝日除く), 前回まで、3回にわたり再帰コールについて解説してきました。今回は再帰コールの解説の最後として、再帰コールの注意点について説明します。, 再帰コールの注意点はリソースを多く消費しがちであることです。つまり、再帰コールは、いわば自分自身のコピーをコールすることなので、コールが連鎖することでコピーの数が増えて、リソースを大量に消費します。特に明示カーソル処理を行っている場合、カーソルをクローズする前に再帰コールを行う内容だと、コールが連鎖するにつれてオープンされているカーソルの数が増えていくので、いずれ上限を超えると「ORA-01000: 最大オープン・カーソル数を超えました。」のエラーとなります。したがって、明示カーソル処理と再帰コールの組み合わせは要注意です。, 以下、そのような再帰コールにおいてオープン・カーソル数が上限を超えるケースの再現、およびその回避策について実演しながら解説します。, まず再帰コール＋明示カーソル処理の例として、バックナンバー第79回「再帰コールで階層問い合わせをしてみる」の例を使います。この回で取り上げたPROC79プロシージャは、社員表で起点となる社員を指定すると、その部下、さらにその部下の部下、さらにその部下の部下の部下・・・・といったように、どんどんと部下の階層に下りて問い合わせを行います。明示カーソルをクローズせずに再帰コールをしているので、階層が深いと、「ORA-01000: 最大オープン・カーソル数を超えました。」のエラーとなります。, では、「ORA-01000: 最大オープン・カーソル数を超えました。」のエラーを実際に起こしてみます。, まず最大オープン・カーソル数の制限値を調べるために、データベースの初期化パラメータ「OPEN_CURSORS」を確認します。, 私の環境では、OPEN_CURSORS = 300 となっています。つまり１セッションでオープンできるカーソルの上限は300です。とういことは、300階層よりも深い階層を含む表をPROC79プロシージャのやり方で再帰コールすると、「ORA-01000: 最大オープン・カーソル数を超えました。」のエラーとなるはずです。, そのエラーを再現するためには、300階層以上の階層を含む表と、その表に対してPROC79プロシージャと同じロジックで再帰コールをするプロシージャが必要です。なるべく簡単にしたいので、その表をTEST81表という名前にして、そのプロシージャをPROC81という名前でそれぞれ作成します。, まず、TEST81表を作成します。簡単な社員表です。ここに、上司、部下の関連のある社員をロードします。必要最低限の列だけにしています。, このTEST81表のID列は社員番号で主キーです。MGR_ID列がID列を参照します。いわばMGR_ID列は社員表の上司番号に相当します。NAME列は社員名です。MGR_ID列はその社員の上司のID列の値を参照します。MGR_ID列によって、上司と部下の関係付けがなされます。, 続いて、TEST81表のID列（社員番号列）の値ですが、主キーですから一意な値が必要です。今回は順序オブジェクトからその値を取得したいと思います。名前をSEQ_TEST81にして、順序オブジェクトを作成します。, では、SEQ_TEST81順序オブジェクトを使って、TEST81表に300階層以上の上司・部下の関係をもつデータをロードします。1人の上司の部下は1人とします。そうでないと300階層では天文学的な行数となってしまいます。仮に1人の上司に2人の部下だとすると、300階層なら社員の総数は、(2の300乗 - 1)という、とんでもない数になります。1人の上司に部下が1人なら社員の総数は、300人ですみます。ここで問題なのは表の行数(社員の数)ではなく階層の深さです。階層の数だけのカーソルがオープンし、オープンカーソルの上限を超える場面を再現できます。, 以下はTEST81表に1人の上司に1人の部下として、301階層の社員をロードするPL/SQLブロックです。, これにより、301階層の上司・部下の連鎖がTEST81表にロードできました。 簡単に解説すると、5-6行目のINSERT文は一番上の階層の行（社長の行）をINSERTしています。ID列の値は、SEQ_TEST81.NEXTVALなので、順序オブジェクトSEQ_TEST81から取得した一意な番号です。また一番上の階層なので、上司はおらず、MGR_ID列の値はNULLとなっています。ここで注目すべきは、6行目の「RETURNING ID INTO V_ID」という記述です。これはINSERTに使われたID列の値を変数V_IDに代入するという意味です。これにより、INSERTのID列の値は、変数V_IDに格納されます。この変数の値は、次の行のMGR_ID列の値として使われます。, 8-12行目で300回のループ処理を行っていますが、1回目のループのとき、ここでのINSERT文のMGR_ID列の値は、上の6行目で取得されたV_ID変数です。そしてそのINSERT文にも、「RETURNING ID INTO V_ID」の記述があります(11行目)。このV_ID変数の値はループの2回目以降のINSERT文のMGR_ID列として使われるわけです。, このように次にINSERTされる行は常にその一つ前の行の部下という形でデータがロードされていきます。, このようにして、全体で、最上位の階層を含めて、301行の上司・部下の連鎖がロードできました。この表には301階層が含まれているわけです。, ご覧のように、社員番号(ID列)が1番から301番まで301行の社員が登録されていますね。MGR_ID列の値が、一つ前の行のID列となっています。, では次に、上司、部下の表示を再帰コールで行うプロシージャPROC81を作成します。このプロシージャの基本的なロジックは、バックナンバー第79回のPROC79と同じです。, 上のソースコードで注目していただきたいのは、22行目です。カーソルFORループ文(21～23行目)の中で、再帰コールを行っています。つまり、カーソルがオープンしてる最中に再帰コールをしています。, よってこのプロシージャを一番上の行の社員番号1を指定して実行すると、一番したの階層まで300階層あるので、300個のカーソルが同時にオープンしますが、途中でオープンカーソルの上限に達して、エラーとなります。, 実際に実行してみます。SQL> SET SERVEROUTPUT ON -- DBMS_OUTPUTの画面出力有効にする, ご覧のように、私の環境では297階層目でエラーとなっています。これは、297の社員の部下を問い合わすカーソルFORループ文を実行しようとしたときに、オープン・カーソルの最大数300を超えたために、エラーとなったものです。若干、数が合わないように思われますが、内部的にバックグラウンドでオープンされているカーソルもあるためです。ですから、おおむね予定通りの結果としてエラーになりました。, では、この処理をエラーなく最後まで完了させるには、どうすればよいかというと、カーソルをクローズしてから再帰コールするようなロジックに書き換えればいいわけです。そのためには、カーソルからの取得行をいったん配列（コレクション）変数に格納して、すみやかにカーソルをクローズします。次にその配列を使って、一件ずつ再帰コールを行えばいいわけです。, 上記のソースコードで注目していただきたいのは、24行目です。ここで、従来はカーソルFORループ文で処理していたSELECT文を、「SELECT BULK COLLECT INTO文」で先に配列変数(REC_TAB)に一括代入します。この「SELECT BULK COLLECT INTO文」では処理の間だけ内部的なカーソルを使用しますが、文の実行後、そのカーソルはすみやかにクローズしていますのでこれ以降の処理で明示的なカーソルはオープンしてません。そして、その配列変数(REC_TAB)の一件ずつに対してループ処理で、再帰コールを行っています。, 今度は、最後の301行目までエラーなく、表示できましたね。どこまで連鎖しても、オープンカーソルの数は増えないので、エラーとはならないわけです。, このように、再帰コールの連鎖の数が多い場合は、明示カーソルをクローズしてから、再帰コールするように修正することで、オープン・カーソル数を減らすことができます。, それでは再帰コールについてはここまでにしたいと思います。また次回、ご期待ください。, 記載された会社名・商品名・製品名は、各社の登録商標または商標です。eラーニング、LMS（学習管理システム）、高度IT人材ならiStudy.