ITコーディネータのシュウです。

ITコーディネータ関連の情報です。
ITCA（ITコーディネータ協会）のホームページには、ＩＴ活用を考えている中小企業の皆様へと題して（経済産業省からのご案内ということで）、IT導入時に利用可能な予算・融資制度の紹介やIT導入を検討するにあたって、ＩＴ活用に成功した企業の事例をいろいろと紹介しているサイトが載せてあります。
http://www.itc.or.jp/kakushinit/
その中の一つに、中小企業・小規模事業者の未来をサポートするサイト「ミラサポ」というものが昨年開設され、国や公的機関の支援情報・支援施策等いろいろな情報を提供していますね。
https://www.mirasapo.jp/index.html
また、ITコーディネータによる支援事例も下記のサイトにあります。
http://wwk.itc.or.jp/itkeiei/
ＩＴＣＡの宣伝のようになってしまいましたが、ちょっと紹介まで

＜本日の題材＞
階層構造のデータ表示（Oracle）

今回は、時折社内でも話題になる階層構造のデータ表示について取り上げてみます。
Oracleでは、階層構造のデータを表示するのに便利な START WITH句、CONNECT BY句というのが用意されています。よく、例として上げられるのが、SCOTTユーザのテーブル「emp」表で、このテーブルには各従業員の上司の従業員番号が項目としてあり、それを使って上司と部下の関係を階層構造で表示する例を見てみます。例えば下記のようなSQLを使います。

SELECT
E1.EMPNO AS 従業員番号
, E1.ENAME AS 従業員名
, LPAD(' ',(LEVEL-1)*2,' ')||E1.JOB AS 職務名
, E1.MGR AS 上司従業員番号
, E2.ENAME AS 上司従業員名
, LEVEL AS 階層LEVEL
FROM EMP E1
LEFT OUTER JOIN (SELECT EMPNO, ENAME FROM EMP) E2
ON E1.MGR = E2.EMPNO
START WITH E1.JOB = 'PRESIDENT'
CONNECT BY PRIOR E1.EMPNO = E1.MGR
ORDER SIBLINGS BY E1.ENAME;


START WITH句で、E1.JOB='PRESIDENT' (社長) を起点（ルート）として階層表示し、CONNECT BY句で親子関係を条件付けしています。PRIOR E1.EMPNO = E1.MGR で親のレコードのEMPNO = 子のレコードのMGR という条件になります。
また、LEVEL は階層レベルを意味していて、職務名のところの頭に階層LEVEL*2ずつの空白を付けて階層が分かりやすくしています。
ORDER SIBLINGS BY句は、階層問い合わせの場合に、CONNECT BYの結果の階層順序を壊さずソートして表示するためのものになります。

START WITH句で、ルートとなるのが JOB='PRESIDENT' という情報がわからない場合は、MGRがNULL（上司の従業員番号がない）のメンバーが階層のSTARTということから、下記のように設定することもできます。

SELECT
E1.EMPNO AS 従業員番号
, E1.ENAME AS 従業員名
, LPAD(' ',(LEVEL-1)*2,' ')||E1.JOB AS 職務名
, E1.MGR AS 上司従業員番号
, E2.ENAME AS 上司従業員名
, LEVEL AS 階層LEVEL
FROM EMP E1
LEFT OUTER JOIN (SELECT EMPNO, ENAME FROM EMP) E2
ON E1.MGR = E2.EMPNO
START WITH E1.EMPNO =
(SELECT EMPNO FROM EMP WHERE MGR IS NULL)
CONNECT BY PRIOR E1.EMPNO = E1.MGR
ORDER SIBLINGS BY E1.ENAME;

※結果は同じです。
また、SYS_CONNECT_BY_PATH(column, char) 句を使うと、ルートからノードへの列(column)の値の経路を、char で指定した文字で区切って返すかたちになります。

SELECT
EMPNO AS 従業員番号
, SYS_CONNECT_BY_PATH (JOB,'/') AS 職務パス
, ENAME AS 従業員名
, CONNECT_BY_ROOT ENAME AS 最終上司名
FROM EMP
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

職務パスということで、社長からの階層を区切り文字「/」を使って表示しています。
※CONNECT_BY_ROOT を使用すると、階層のルートの値（この場合は、一番の上司）を表示することができます。（階層のルートが複数あるような場合はそれぞれのルートの値が取れることになります）
ちなみに、上記の内容は、Oracleの場合に限定した内容になります。

次回からは、他のデータベースでも可能な方法として、再帰SQLについて、及びそれを使って階層構造を表示する方法について取り上げてみたいと思います。

今日は以上まで