【KPI】家系図テーブルの操作 1【oracle】

某炎の紋章の家系図見てて、TRPGなどに使えるかなと思って家系図テーブルの運用を考える。
階層問い合わせを利用し、人の一覧である「M_人」テーブルと、子とその親を載せた「T_家」テーブルがある前提で、大雑把にデータを取得する方法を考える。

※WITH区で疑似テーブルとして作成

oracle

WITH M_人 AS
(
    SELECT '01' AS NO, '人01' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '02' AS NO, '人02' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '03' AS NO, '人03' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '04' AS NO, '人04' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '05' AS NO, '人05' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '06' AS NO, '人06' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '07' AS NO, '人07' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '08' AS NO, '人08' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '09' AS NO, '人09' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '10' AS NO, '人10' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '11' AS NO, '人11' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '12' AS NO, '人12' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '13' AS NO, '人13' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '14' AS NO, '人14' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '15' AS NO, '人15' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '16' AS NO, '人16' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '17' AS NO, '人17' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '18' AS NO, '人18' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '19' AS NO, '人19' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '20' AS NO, '人20' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '21' AS NO, '人21' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '22' AS NO, '人22' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL
)
, T_家 AS
(
    SELECT '03' AS NO, '01' AS 父, '02' AS 母 FROM DUAL UNION ALL
    SELECT '04' AS NO, '01' AS 父, '02' AS 母 FROM DUAL UNION ALL
    SELECT '05' AS NO, '01' AS 父, '02' AS 母 FROM DUAL UNION ALL
    SELECT '08' AS NO, '06' AS 父, '07' AS 母 FROM DUAL UNION ALL
    SELECT '09' AS NO, '06' AS 父, '07' AS 母 FROM DUAL UNION ALL
    SELECT '12' AS NO, '11' AS 父, '10' AS 母 FROM DUAL UNION ALL
    SELECT '13' AS NO, '11' AS 父, '10' AS 母 FROM DUAL UNION ALL
    SELECT '14' AS NO, '03' AS 父, '12' AS 母 FROM DUAL UNION ALL
    SELECT '15' AS NO, '03' AS 父, '12' AS 母 FROM DUAL UNION ALL
    SELECT '16' AS NO, '09' AS 父, '05' AS 母 FROM DUAL UNION ALL
    SELECT '17' AS NO, '09' AS 父, '05' AS 母 FROM DUAL UNION ALL
    SELECT '18' AS NO, '14' AS 父, '16' AS 母 FROM DUAL
)
SELECT  LEVEL AS レベル
    , SYS_CONNECT_BY_PATH(MAIN.NO, ',') AS 根_子
    , SYS_CONNECT_BY_PATH(MAIN.父, ',') AS 根_父
    , SYS_CONNECT_BY_PATH(MAIN.母, ',') AS 根_母
    , MAIN.NO AS 子
    , MAIN.父
    , MAIN.母
FROM T_家 MAIN
--START WITH (MAIN.NO) IN (SELECT NO FROM T_家)  -- ツリーのルート用件番号
CONNECT BY PRIOR MAIN.父 = MAIN.NO OR PRIOR MAIN.母 = MAIN.NO
ORDER BY MAIN.NO, MAIN.父, LEVEL

実行結果は下記となる。

レベル	根_子	根_父	根_母	子	父	母
1	,03	,01	,02	03	01	02
2	,15,03	,03,01	,12,02	03	01	02
2	,14,03	,03,01	,12,02	03	01	02
3	,18,14,03	,14,03,01	,16,12,02	03	01	02
1	,04	,01	,02	04	01	02
1	,05	,01	,02	05	01	02
2	,17,05	,09,01	,05,02	05	01	02
2	,16,05	,09,01	,05,02	05	01	02
3	,18,16,05	,14,09,01	,16,05,02	05	01	02
1	,08	,06	,07	08	06	07
1	,09	,06	,07	09	06	07
2	,17,09	,09,06	,05,07	09	06	07
2	,16,09	,09,06	,05,07	09	06	07
3	,18,16,09	,14,09,06	,16,05,07	09	06	07
1	,12	,11	,10	12	11	10
2	,15,12	,03,11	,12,10	12	11	10
2	,14,12	,03,11	,12,10	12	11	10
3	,18,14,12	,14,03,11	,16,12,10	12	11	10
1	,13	,11	,10	13	11	10
1	,14	,03	,12	14	03	12
2	,18,14	,14,03	,16,12	14	03	12
1	,15	,03	,12	15	03	12
1	,16	,09	,05	16	09	05
2	,18,16	,14,09	,16,05	16	09	05
1	,17	,09	,05	17	09	05
1	,18	,14	,16	18	14	16

項目「レベル」は世代の数-1（連続するデータの数）となる。レベル2は親-子-孫の3世代いる。
「根_子」「根_父」「根_母」はそれぞれ、世代ごとの子、父、母となるコードが出力される。

これらを使用して、次はデータ操作を行う画面を作成したい。