SQL/PLUSでデータに親子関係を持つ場合などで再帰的に情報を拾ってきたい場合、SQLでは厳しいですが下記のようなPL/SQLをにて実現できます。
対象テーブル
住所マスタ (M_ADDRESS)
ADDRESS_CD | ADDRESS_NM | PARENT_CD |
ASIA | アジア | EARTH |
JP | 日本 | ASIA |
CN | 中国 | ASIA |
JP_TOKYO | 東京都 | JP |
JP_OSAKA | 大阪府 | JP |
JP_TOKYO_SHINJUKU | 新宿区 | JP_TOKYO |
JP_OSAKA_OSAKA | 大阪市 | JP_OSAKA |
JP_OSAKA_OSAKA_ASAHI | 大阪市旭区 | JP_OSAKA_OSAKA |
上記のようなテーブルの内容を階層別に表示する場合、下記のようなPL/SQLを作成することでよきに表示させることができます。
SET SERVEROUTPUT ON; / DECLARE first_cd VARCHAR2(100) := 'EARTH'; -- show children PROCEDURE showChildren( code IN VARCHAR2 , nestCnt IN NUMBER ) AS CURSOR curQuery1( pCode IN VARCHAR2 ) IS SELECT A.ADDRESS_CD , A.ADDRESS_NM , A.PARENT_CD FROM M_ADDRESS A WHERE 1 = 1 AND A.PARENT_CD = pCode ; vRec curQuery1%ROWTYPE; BEGIN -- FETCH QUERY OPEN curQuery1( code ); LOOP FETCH curQuery1 INTO vRec; EXIT WHEN curQuery1%NOTFOUND; DBMS_OUTPUT.PUT_LINE( LPAD( CHR(9) , nestCnt , CHR(9) ) || vRec.ADDRESS_NM); showChildren( vRec.ADDRESS_CD , nestCnt+1 ); END LOOP; CLOSE curQuery1; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('error.'); END; BEGIN showChildren( first_cd , 0 ); END; /
実行方法
SQLPLUSを立ち上げてSQLを実行するとき同様貼り付けて実行します。
概要
SET SERVEROUTPUT ON;
DBMS_OUTPUT.PUT_LINEはコマンドラインに文字列を出力する処理ですが、この記述がない場合出力されません。
フェッチした内容の子を表示する為に、showChildrenプロシージャ―を再帰的に呼び出します。このように無名ブロックの中にもプロシージャを記述できます。
LPAD( CHR(9) , nestCnt , CHR(9) )の箇所は階層分タブ文字を表示させています。
無名ブロックによる実行ですのでサーバーにリソースを残しません。よく利用するようでしたらストアドプロシージャ―として登録してもよいと思います。
実行結果
下記のように表示されると思います
アジア 日本 東京都 新宿区 大阪府 大阪市 大阪市旭区 中国